Parameter query and record selection question

[Trogg]

I have a "like" operator in a parameter query that allows the user to specify a partial name that then returns all like names. For example, when prompted for a name if the user types Smith* he gets all records of all Smiths. My question is what would be the easiest way at that point to allow the user to choose the Smith he is looking for from all the Smiths the query returned? Filters, boxes, Forms? not sure how to efficiently do this. Thanks in advance!

 

[dhookom]

Why not just use a combo box on a form? IMO, users should never be subjected to parameter prompts faq701-6763.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

I have no problem with that.. the problem is that there are a couple of hundreds of thousand names in the DB... the user shouldn't have to pick from a list that large should they?

 

[PHV]

Use a ComboBox with the AutoExpand property set to True ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You could also use a couple controls where the first one is used to filter the second. For instance a text box might allow the user to enter the first couple letters and then press enter. The second control (a combo box) would then be filtered based on the entered text.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Duane.. that sounds like What I need to do.. can you elaborate on that or give me an example how to tie them together... sorry to be unsure here. Thanks.

 

[dhookom]

Assuming you have a text box:
Name: txtFirstLetters
Control Source:

and a combo box:
Name: cboName
Row Source:
Row Source Type: Table/Query

Set the code (module window) of the After Update event of the text box to something like:

    Dim strSQL as String
    strSQL = "SELECT [EmpName] FROM tblEmployees " & _
       "WHERE [EmpName] Like " & Me.cboName & "*"" " & _
       "ORDER BY [EmpName];"
    Me.cboName.RowSource = strSQL

Since you didn't provide any control, table, or field names, you will have to make the appropriate changes.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Hi Duane...

I am playing around with this... I set up a text box and combo box as you described and entered the code for the text box. I get a "missing operator" error in the Rowsource for the combobox... here is the string in the rowsource generated from the code. SELECT [Name] FROM qSMH_PCI WHERE [Name]Like *" ORDER BY [Name];

Can you tell me what is missing? Thanks.

 

[dhookom]

Try add more quotes after Like:

    Dim strSQL as String
    strSQL = "SELECT [EmpName] FROM tblEmployees " & _
       "WHERE [EmpName] Like[b][COLOR=Red Yellow] ""[/color][/b]" & Me.cboName & "*"" " & _
       "ORDER BY [EmpName];"
    Me.cboName.RowSource = strSQL

Name is a horrible name for Access stuff that all has a name property.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You may also be interested in FAQ702-6304