![\"frmComboBox\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2013\/08\/frmComboBox.png\")
<\/p>\nYou want\u00a0your Users to be able to\u00a0look up and\/or add new\u00a0activities 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.\u00a0 Well,\u00a0you don’t want to add a separate Combo Box for Company Name, First Name, etc.\u00a0\u00a0Depending on how many fields you want to use putting that many Combo Boxes on a\u00a0Form can get a bit crowded.\u00a0\u00a0 In this example, you will only need two Combo Boxes on your Main form…<\/p>\n
<\/p>\n
On your Main Form add two Combo Boxes.\u00a0 In my example, I named them cboSearch<\/span> (top one) \u00a0and cboResults<\/span> (bottom one).\u00a0\u00a0 For your Subform use the table that holds the Activities for your Contacts.\u00a0 (Note:\u00a0 You can also use this set up to search for Vendor Products, Service Calls, Work Orders, and so on.)<\/p>\n cboSearch<\/strong><\/span> On the Data tab…<\/strong><\/span> On the Format tab…<\/span><\/strong> cboResults<\/span><\/strong> On the Data tab…<\/strong><\/span> On the Format tab…<\/strong><\/span> Create a Query for each Value you want to search by.\u00a0 (Be sure to to put Is Not Null<\/strong> on the Criteria line of the Company, First Name, etc. so you don’t get blanks in your Combo Box.)\u00a0 For this example…<\/p>\n qrySearchCompany<\/span> Note:<\/strong>\u00a0 The only thing that is constant throughout the Queries is first field must<\/span><\/strong> be the key you are going to link the Main Form and Subform together on.\u00a0\u00a0For example, we care using the cContactID<\/span><\/strong>, so the Query for qrySearchCompany<\/span> would be…<\/p>\n SELECT tblContacts.cContactID<\/span><\/strong>, tblContacts.cCompany In the After_Update<\/strong> event of\u00a0cboSearch copy\/paste the below (providing you used the same names.\u00a0 If not adjust accordingly)<\/span><\/span>…<\/span><\/p>\n Select Case cboSearch You can download a sample here<\/a>.<\/p>\n
\nIn the Properties window…<\/p>\n
\nRow Source = 1;Company Name;2;Last Name;3;First Name;4;eMail Address
\nRow Source Type = Value List
\nBound Column = 1<\/p>\n
\nColumn Count = 2
\nColumn Widths = 0″;1″<\/p>\n![\"ComboBox\"](\"http:\/\/regina-whipp.com\/blog\/wp-content\/uploads\/2013\/08\/ComboBox.png\")
<\/p>\n
\nIn the Properties window…<\/span><\/span><\/p>\n
\nRow Source = Leave Blank
\nRow Source Type = Table\/Query
\nBound Column = 1<\/p>\n
\nColumn Count = 2
\nColumn Widths = 0″;1″<\/p>\nStep 2<\/h5>\n
\nqrySearchFirstName
\nqrySearchLastName
\nqrySearcheMailAddress<\/p>\n
\nFROM tblContacts
\nWHERE (((tblContacts.cCompany) Is Not Null));<\/p>\nStep 3<\/h5>\n
\nCase 1
\nMe.cboResults.RowSource = “qrySearchCompany”
\nMe.cboResults = “”
\nMe.sfrActivities.LinkMasterFields = “cboResults”
\nMe.sfrActivities.LinkChildFields = “aContactID”
\nCase 2
\nMe.cboResults.RowSource = “qrySearchLastName”
\nMe.cboResults = “”
\nMe.sfrActivities.LinkMasterFields = “cboResults”
\nMe.sfrActivities.LinkChildFields = “aContactID”
\nCase 3
\nMe.cboResults.RowSource = “qrySearchFirstName”
\nMe.cboResults = “”
\nMe.sfrActivities.LinkMasterFields = “cboResults”
\nMe.sfrActivities.LinkChildFields = “aContactID”
\nCase 4
\nMe.cboResults.RowSource = “qrySearcheMailAddress”
\nMe.cboResults = “”
\nMe.sfrActivities.LinkMasterFields = “cboResults”
\nMe.sfrActivities.LinkChildFields = “aContactID”
\nCase Else
\nMe.cboResults.RowSource = “”
\nMe.cboResults = “”
\nMe.sfrActivities.LinkMasterFields = “”
\nMe.sfrActivities.LinkChildFields = “”
\nEnd Select<\/p>\nAll done… Enjoy!<\/h5>\n
<\/div>\n