Access Tutorial: Create a Simple Yet Powerful UI Tool
Introduction
Creating a user interface that makes data entry easier and more accurate for users is essential to good application design. Those are a couple of reasons why databases have a dropdown select box, and in the case of Access, it’s called a combo box. They work fine and are very useful, but I find that if I have a large continuous form, the combobox dropdown arrows become ugly and take up too much screen space. To solve that problem, here’s a simple tool that helps with data entry and also has the added benefit of minimizing the size of the dropdown arrow you would have from a combobox.
Do the following to set it up on a new database or your own. Note that any name you create can be changed in this example, just be sure to be consistent when you try to use the element name later. You need to create a table for data, two small forms, and a small module. Setup time should be five to ten minutes depending on your familiarity with Access.
Create table Name it new1
- automatic identification number
- name text
- address text
- city text
- State Text
- compressed text
Fill in some test data in 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 the newl table
- Enable the form header/footer option
- Put all fields in a single row
- Place all 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 popup form. Name it stpop. Align it to the end of the status field in the row.
- Set the foreground and background colors the same way in the properties/format tab of the label you just created.
- In the onclick event of the text box you just created, put the following code
Private Sub Ctl_stpop_Click()
Me.State.SetFocus
MRecOpen = Me.ID.Value
DoCmd.OpenForm “StatePop”
finish sub
Close and save the form as BtnForm.
Create form named StatePop
Follow these steps for Form 2:
Make record source = SELECT new1.State FROM new1 GROUP BY new1.State HAVING (((new1.State) Is Not Null));
Add the only field to the form that will be the status field.
Activate the header/footer of the form. Add a label at the top of the form header with the States text. Place another tag next to the form with the X-only text. You’ll use it to close the form because we’re going to turn off the borders of the form.
Adjust these settings for the form
In the format tab:
- subtitle states
- Default View Continuous Forms
- Vertical-only scrollbars
- Record Selectors No
- Navigation Buttons Yes
- dividing lines not
- Auto resize no
- border style none
- control box not
- Buttons min max none
- close button not
- Width .8
In the Other tab set:
- popup yes
- modal yes
In design view, click the label you created with X as text. Show the properties, and in the onclick event for this tag, put the following code. In my example, Access called it Label7.
private sublabel 7_Click()
DoCmd.Close
finish sub
Next, in the onclick event for the status text box you created, put the following code. Here you are telling Access to put the value of the status field into a variable called CRP. It then puts the CRP value into the status field in the btnform and then closes the popup.
Sub state private_Click()
PCR = Me.Status.Value
Shapes![btnform]![State].Value = PCR
DoCmd.Close
finish sub
Close and save the form layout as a StatePop.
Now create a module with the following lines of code to define the variables you will use.
Compare Database Option
public MRecOpen as integer
Public CRP as String
Save and close with any name.
Test it. You should see it work. Remember that the popup window is populated with the 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 status value in the form, then you will see that data in the popup. As you enter new states in the form, those values will be available for selection the next time you click the popup button. Also remember that anytime 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 prevents a popup form from being sent back behind another form you might have open. If that happened, you could lose control of your application because the popup form might have been sent backwards behind other windows or forms.