Combo Box to Find Records…

You want your Users to be able to look up and/or add new activities for existing Companies but they may not know or remember the Company Name but they do remember the person they speak to on a daily basis.  Well, you don’t want to add a separate Combo Box for Company Name, First Name, etc.  Depending on how many fields you want to use putting that many Combo Boxes on a Form can get a bit crowded.   In this example, you will only need two Combo Boxes on your Main form…

Step 1

On your Main Form add two Combo Boxes.  In my example, I named them cboSearch (top one)  and cboResults (bottom one).   For your Subform use the table that holds the Activities for your Contacts.  (Note:  You can also use this set up to search for Vendor Products, Service Calls, Work Orders, and so on.)

cboSearch
In the Properties window…

On the Data tab…
Row Source = 1;Company Name;2;Last Name;3;First Name;4;eMail Address
Row Source Type = Value List
Bound Column = 1

On the Format tab…
Column Count = 2
Column Widths = 0″;1″

cboResults
In the Properties window…

On the Data tab…
Row Source = Leave Blank
Row Source Type = Table/Query
Bound Column = 1

On the Format tab…
Column Count = 2
Column Widths = 0″;1″

Step 2

Create a Query for each Value you want to search by.  (Be sure to to put Is Not Null on the Criteria line of the Company, First Name, etc. so you don’t get blanks in your Combo Box.)  For this example…

qrySearchCompany
qrySearchFirstName
qrySearchLastName
qrySearcheMailAddress

Note:  The only thing that is constant throughout the Queries is first field must be the key you are going to link the Main Form and Subform together on.  For example, we care using the cContactID, so the Query for qrySearchCompany would be…

SELECT tblContacts.cContactID, tblContacts.cCompany
FROM tblContacts
WHERE (((tblContacts.cCompany) Is Not Null));

Step 3

In the After_Update event of cboSearch copy/paste the below (providing you used the same names.  If not adjust accordingly)…

Select Case cboSearch
Case 1
Me.cboResults.RowSource = “qrySearchCompany”
Me.cboResults = “”
Me.sfrActivities.LinkMasterFields = “cboResults”
Me.sfrActivities.LinkChildFields = “aContactID”
Case 2
Me.cboResults.RowSource = “qrySearchLastName”
Me.cboResults = “”
Me.sfrActivities.LinkMasterFields = “cboResults”
Me.sfrActivities.LinkChildFields = “aContactID”
Case 3
Me.cboResults.RowSource = “qrySearchFirstName”
Me.cboResults = “”
Me.sfrActivities.LinkMasterFields = “cboResults”
Me.sfrActivities.LinkChildFields = “aContactID”
Case 4
Me.cboResults.RowSource = “qrySearcheMailAddress”
Me.cboResults = “”
Me.sfrActivities.LinkMasterFields = “cboResults”
Me.sfrActivities.LinkChildFields = “aContactID”
Case Else
Me.cboResults.RowSource = “”
Me.cboResults = “”
Me.sfrActivities.LinkMasterFields = “”
Me.sfrActivities.LinkChildFields = “”
End Select

All done… Enjoy!

You can download a sample here.

 786 total views,  1 views today