You are here:

Using MS Access/Access 2007 Form Combo Box Lookup

Advertisement


Question
QUESTION: I have a form on which I have created a Combo Box Lookup using the Wizard.  I selected "Find a record on my form based on the value I selected in my combo box"

The combo box works fine until I try to add a parameter by going into the Row Source Query Builder and adding the following to the Company field

Like "*" & [Enter Company Name or first few letters or press Enter for all] & "*"

After I add that parameter the lookup combo box will work once but will not clear out the records in order to do another search.

The reason I need the parameter is that not all Company names may have been entered starting with the word I am searching for:  e.g.,  Rams, St. Louis Rams, Football Rams, etc.

The Combo Box Lookup without the parameter only returns records that BEGIN with "Rams".  With the parameter it returns any Company name that CONTAINS "Rams".  However, the results will not clear from the Combo Box Lookup field after I search using the parameter.

ANSWER: What I would do is add a text box next to your search combo. Call it txtFilter, then change the WHERE clause in the RowSource of the combo to:

WHERE [CompanyName] Like "*" & Forms!formname!txtFilter & "*;"

Then add this code to the After Update event of txtFilter:

Me.cboSearch.Requery

where cboSearch is the name of your Search combo.

If you want to do a new search clear out the text box.

Hope this helps,
Scott<>


---------- FOLLOW-UP ----------

QUESTION: Thanks Scott, however nothing happens when I type a word in the text box.  Here is ALL the code in my form

Private Sub form_load()
DoCmd.GoToRecord acDataForm, "Development Data Entry", acNewRec
End Sub

Private Sub txtFilter_AfterUpdate()
Me.CLUSearch.Requery
End Sub

Here is the Combo box row source

WHERE [Company/Foundation] Like "*" & Forms!Development_Data_Entry!txtFilter & "*;"

I'm perplexed...

ANSWER: First, what version of Access? Second, there should be code, probably an embedded macro, behind the search combo. third, that should NOT be the Entire Rowsource of the Combo. It should be a full SQL statement. Fourth Why are you going to a new record when you load the form?

Hope this helps,
Scott<>

---------- FOLLOW-UP ----------

QUESTION: First, sorry I thought my subject line indicated Access 2007.

Second, macro behind combo is

Search for record
, , First, ="[ID] = " & Str(Nz(Screen.ActiveControl,0))

Third, I misunderstood your instructions.  I now have changed the combo box rowsource to:

SELECT [CONTACT DEMOGRAPHICS].[Company/Foundation], [CONTACT DEMOGRAPHICS].[Company Address], [CONTACT DEMOGRAPHICS].LastName, [CONTACT DEMOGRAPHICS].FirstName, [CONTACT DEMOGRAPHICS].SALUTATION FROM [CONTACT DEMOGRAPHICS] WHERE (("WHERE [Company/Foundation]" Like "*" & [Forms]![Development_Data_Entry]![txtFilter] & "*;"));

Fourth, when the data entry people open the form I do not want someone's record to pop up because they may inadvertently overwrite it, so I like for them to start on a blank record.

Now when I type in the text box I get a Forms!Development_Data_Entry!txtFilter parameter box and when I type in there again I get nothing.

Still perplexed, what have I done wrong?

Answer
Sorry, I missed the reference to Access 2007.

Based on the macro, the RowSource should be:

SELECT ID, [Company/Foundation]
FROM [Contact Demographics]
WHERE [Company/Foundation] Like "*" & Forms![Data_Development_Entry]!txtFilter & "*;"

The prompt indicates Access can't find a control named txtFilter. So are you sure you named the textbox control?

Hope This helps,
Scott<>

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.