| Property | Value |
| Caption | Contact |
| Default View | Single Form |
| Views Allowed | Form |
| Scroll Bars | Neither |
| Record Selectors | No |
| Dividing Lines | No |
| Auto Center | Yes |
| Key Preview | Yes |
| Cycle | Current Record |
| Shortcut Menu | No |
| Allow Design Changes | Design View Only |
Highlight the labels again, right-click, align, right to align the label fields to the right. Hold the shift key and press the right arrow until the right edges of the labels are a few pixels from the textboxes. With the labels still highlighted, right-click, size, fit to resize the label to fit the text it contains. Drag the textboxes to the desired positions on the form. Resize each textbox by dragging its right edge or holding shift and pressing the left or right arrow to shrink or expand it. Hold the Ctrl key and use the arrows to fine-tune the position of each textbox. You can also select objects, right-click and Align to align them.
| Control | Prefix |
| Combo Box | cbo |
| Command Button | cmd |
| Image | img |
| List Box | lst |
| Label | lbl |
| Line | lin |
| Option Group Frame | fra |
| Option Button | opt |
| Subform | sfrm |
| Tab Control | tab |
| Text Box | txt |
| Combo Box | cbo |
Contact: [Company] & ' - ' & [LastName] & ', ' & [FirstName] & ' (' & ContactID & ')'
This compound field shows the Company, name and ContactID. Select the Ascending sort in the Sort row under the second column. Click the red ! to run the query. Save it as qryCboContact.
| Property | Value |
| Column Count | 2 |
| Column Widths | 0;2.5" |
| List Width | 2.5" |
| RowSource | qryCboContact |
| Name | cboContact |
| Tab Stop | No |
|
For this, we'll need some VBA! Open frmContact in the design view, right-click cboContact and select Properties to see the properties window. Click the Event tab. Click the white line to the right of After Update and select [Event Procedure] in the combo box. Click the ... to the right of the combo to see the Visual Basic window. This is where the code behind the form is located. It has a General Declarations area and lines that say: Private Sub cboContact_AfterUpdate() End Sub The code we will add to this subroutine will run After we Update the combo box named cboContact. Private means the code can only used by this form. Any memory variables it changes will not affect other objects in the database. End Sub is the last line of the code for this event. Between the lines above, type: Me.RecordsetClone.FindFirst "ContactID=" & Me!cboContact Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark Save the form and open it to test your new combo box. If you typed the code correctly, the form data should now change to match the contact you select in the Contact combo box. Those two lines above are about as complicated as VBA gets. Let's look at some more simple VBA commands first. GENERAL VBA TIPS If you type DoCmd. in a code module, you'll see a menu of arguments for the DoCmd command. Here are some useful ones:
After you type a DoCmd command, press the F1 key to see Access help on that command. Another important part of VBA is knowing how to refer to objects. You are already familiar with quite a few objects. Controls such as textboxes, combo boxes, checkboxes, lines, images and option groups are objects. They can be contained in forms or reports which are also objects. Tables, queries and macros are also objects. Think of objects as nouns in the English language. They are things. Objects have properties such as Width, Height or BackColor that describe them. Properties are like adjectives that describe nouns. Running VBA code is like the verb of a sentence. It causes the objects to act (OpenForm, RunSQL, Close, etc). Access has several collections of objects. All forms are in the Forms collection. queries in the QueryDefs collection, tables in the TableDefs collection and reports in the Reports collection. Each form has a Controls collection that contains the textboxes, labels, combo boxes and other controls for that form. Each table has a Fields collection. Most objects have a Properties collection. You generally specify the name of the collection and the name of the object within that collection. Specify from the general collection to the more specific collection and finally to the property. To refer to the value in a textbox named txtContactID in the form frmContact use Forms("frmContact").Controls("txtContactID").Value. The word Forms tells us that frmContact is in the Forms collection. We are further referring to the txtContactID control in frmContact's Controls collection and we have specified the property .Value which appears in the textbox. To see if the field txtUsr is visible on the frmContact form you would refer to: Forms("frmContact").Controls("txtUsr").Visible To set it to True Forms("frmContact").Controls("txtUsr").Visible=True You can also shorten the syntax of forms to Forms!frmContact!txtUsr.visible=True Note that we use the ! when separating collections such as the Forms collection and its Controls collection. We use the . to separate Properties from collections. When code in frmContact is running, you can refer to the form as Me instead of having to use the longer Forms!frmContact or Forms("frmContact"). Me!txtZip is the same as Forms("frmContact").Controls("txtZip").Value. |
Me!cboContact = Me!txtContactID
On Error GoTo 0 The Current event fires when you move from one record to another. It fires a lot. Don't put a slow query here.
This event fires before all the objects have data, so we need to ignore errors with the On Error Resume Next line. We then set the combo box to the value of txtContactID and turn off the error handler. Now the combo stays in synch with the form when you navigate through the records.
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 The first line selects the current record and the second line deletes it. This code was created by the wizard that runs when you create a new command button, select Record Operations and Delete Record.
Me!txtUpdDate=Now() This code runs when the first letter of data is changed. The first line places your UserID in the Usr field of tblContact. That UserID is Admin for an unsecured Access database. The second line places the current date and time into the UpdDate field. We don't want the user to be able to edit these hidden fields but we want them to be able to read the data. We will create code so when the user presses Alt-Z, a messagebox appears. Put the following into the form's On Key Down event: If KeyCode = vbKeyZ And Shift = 4 Then
MsgBox "Updated by " & Me!txtUsr & " on " & Me!txtUpdDate
End If The constant vbKeyZ is the letter Z and Shift=4 is the Alt key. If that key combination is pressed at any time, the message box appears.
