If you have used the contacts manager template you'll have a table Contacts
which includes the columns ContactID, FirstName and LastName. The easiest
way to find a contact is to ada combo box to the Contacts form which lsts all
the contacts by name alpahabetically. You then just need to select a name
from the list. So first add a combo box to the form in design view and name
it cboFindContact.
You next need to set properties of the combo box in its properties sheet.
First set its RowSource property to:
SELECT ContactID, (FirstName+" ") & LastName AS FullName FROM Contacts ORDER
BY LastName;
This will list the contacts in the format 'Ken Sheridan' sorted by the last
name. The set other properties of the combo box as follows:
BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches, but the first dimension
must be zero to hide the first column, ContactID, so you see just the names.
To get the form to move to the selected contact you need to put some VBA
code in the combo box's AfterUpdate event procedure. To do this select the
AfterUpdate event in the control's properties sheet and click on the ' build'
button (the one on the right with 3 dots). In the next dialogue select Code
Builder. When the VBA editor opens at the AfterUpdate event procedure there
will be two lines already in place. Enter the following codeas new lines
between these two:
Dim rst As Object
Set rst = Me.RecordsetClone
With rst
rst.FindFirst "ContactID = " & Me.cboFindContact
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With
Set rst = Nothing
What this does is first find the matching record in a clone of the form's
recordset. It then synchronises the bookmarks of the form and the recordset,
which causes the form to go to the record.
In addition to this main functionality put the following line of code in the
Form's AfterUpdate event procedure to update the combo box's list immediately
if you change or add a record in the form:
Me.cboFindContact.Requery
Also add this line to the Form's Current event procedure to keep the combo
box in sync with the form if you navigate through its records by means of
the navigation buttons or keyboard:
Me.cboFindContact = Me.ContactID
You should now find that all you have to do to go to a contact record is
select the name from the combo box's list.
If you want to print a report for the current contact in the form you can
design a report based on the Contacts table and add a button to the form to
open this report for just the current contact by putting the following in the
button's Click event procedure:
' first make sure the record is saved
RunCommand acCmdSaveRecord
' then print the report
DoCmd.OpenReport "YourReportName"
If you want to preview rather than print the report use:
' first make sure the record is saved
RunCommand acCmdSaveRecord
' then print the report
DoCmd.OpenReport "YourReportName",View:=acViewPreview
Better still have two buttons; one to print, one to preview.
