Building a user interface that makes data entry easier and more accurate for your users is essential to good application design. Those are a couple reasons that databases have a drop down selection box and in the case of Access it is called a combo box. They work well and are very handy but I find that if I have a large continuous form those drop down arrows from combo boxes become ugly and take up too much screen real estate. To solve that problem here is a simple tool that helps with data entry and also has the added benefit of minimizing the size of the drop down arrow that you would have from a combo box.
Do the following to set it up in a new database or one of your own. Note that in this example any name you create can be changed, just make sure you are consistent when you try to use the item name later. You need to create a table for data, two small forms and a small module. Set up time should be about five to ten minutes depending on your Access familiarity.
Create Table Name it new1
- ID autonumber
- Name text
- Address text
- City text
- State Text
- Zip text
Fill in some test data on the table.
Create two forms
Follow these steps for form 1:
- First create the form to display the data and name it BtnForm
- Create a form and put all the fields in it from table newl
- Turn on the form header/footer option
- Put all the fields in single row
- Put all the field labels in a single row above their respective fields but in the form header.
- Set the form type to continuous form
- Create a text box and make it small to be the button to open the pop up form. Name it stpop. Align it at the end of the state field on the row.
- Set the foreground and background colors the same on the properties/format tab of the label you just created.
- On the onclick event on the text box you just created place the following code
Private Sub Ctl_stpop_Click()
MRecOpen = Me.ID.Value
Close and save the form as BtnForm.
Create form named StatePop
Follow these steps for form 2:
Make the recordsource = SELECT new1.State FROM new1 GROUP BY new1.State HAVING (((new1.State) Is Not Null));
Add the one field to the form which will be the state field.
Turn the form header/footer on for the form. Add a label at the top in the form header with the text of States. Place another label next to the form with the text of just X. You will use this to close the form because we are going to turn off the form borders.
Adjust these setting for the form
On the format tab:
- Caption States
- Default view Continuous forms
- Scroll Bars vertical only
- Record selectors No
- Navigation buttons No
- Dividing lines no
- Auto resize no
- Border style none
- Control box no
- Min max buttons none
- Close button no
- Width .8
On the Other Tab set:
- Popup yes
- Modal yes
In design view, click on the label you created with X as the text. Show properties and on the onclick event for this label place the following code. In my example Access named it Label7.
Private Sub Label7_Click()
Next on the onclick event for the state text box you created, place the following code. Here you are telling Access to put the value of the state field into a variable named CRP. Next it places the value of CRP into the state field on the btnform then closes the popup.
Private Sub state_Click()
CRP = Me.State.Value
Forms![btnform]![State].Value = CRP
Close and save the form design as StatePop.
Now create a module with the following lines of code to define the variables you will use.
Option Compare Database
Public MRecOpen As Integer
Public CRP As String
Save and close with any name.
Try it out. You should see it work. Remember the popup is populated by data previously entered in the BtnForm. So the first time you open it, if there are no states in the data there will be no values in the popup. Enter at least one state value on the form, then you will see that data in the popup. As you enter new states in the form those values subsequently become available to select the next time you click the popup button. Also remember, that any time you have the Modal setting for a form set to yes, that form takes control of Access and you can’t do anything else until you close that form. The benefit of having modal set to yes, is that it keeps a pop up form from getting sent backwards behind another form you may have open. If that were to happen you could loose control of your application because the popup form could have been sent backward behind other windows or forms.