Form for Search Query?

[eyal8r]

I thought this was a simple task- but it's proving otherwise. I have a form with a number of combo boxes on it. I want to pass the values selected in the combo boxes to the query, and return the results. Basically, it's a 'search' form. However, it's not returning anything. I used the expression builder to select the right combo box expression in the query- so I know that's right. What am I missing here?

For example- I have a combo box for City. I want to select all the records in a specific City. The combo box has a 'Select Distinct' type statement in it- and it's displaying the City names properly. I have linked a button to Query I am using. But when I select a city from the list (yes, multi-select is turned on) and hit the button- it returns 0 records. What am I missing here?
Thanks!

 

[MajP]

Can you post your form code and your sql for the query? Can only make guesses without that information. I doubt you built a simple code for a multi select list box. Not something you can do with a wizard.

 

[eyal8r]

Here you go-

SQL to the Query-
SELECT tbListing.Status, tbListing.MLS, tbListing.Address
FROM tbListing
WHERE (((tbListing.Status)=[Forms]![Form1]![Status]));


Code to the Form- not sure which Sub it is- here's 2 that come up-

Option Compare Database

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String

stDocName = "qry1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub
Private Sub RunQry_Click()
On Error GoTo Err_RunQry_Click

Dim stDocName As String

stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_RunQry_Click:
Exit Sub

Err_RunQry_Click:
MsgBox Err.Description
Resume Exit_RunQry_Click

End Sub

I don't know how to do any other code other than the Wizard- what more do I need?

 

[dhookom]

Combo boxes don't have a multi-select property. If you actually have a multi-select list box, you will need to use code to use it in a query.

Can you provide more information on your query such as its SQL view?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eyal8r]

See above- and yes, I mean a listbox- sorry.

 

[dhookom]

Is [Forms]![Form1]![Status] the list box? If so, you need to use some additional code. There is a generic function that might work at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You really should find and use a naming convention.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eyal8r]

huh. How do I put code into this form? I assume I need code for every listbox I have on here? This form was just a quick sample one I was using for this instance. I have a better naming convention for my REAL forms/Queries...

 

[dhookom]

The generic function I suggested can be used for multiple list boxes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MajP]

Does a single list box define the search or do multiple list boxes define the search?
Lets say you have a color list box and a shape list box.
Can you search for both color and shape? ex "where color = 'blue' or color = 'red' and shape = 'circle' or shape = 'triangle'"
Or can you search for either specific colors or specific shapes, but not at the same time? The first is more complicated.

 

[dhookom]

You can create a query that uses multiple listboxes. For instance in the sample download, you could create a query with SQL like:

SELECT Employees.*
FROM Employees
WHERE IsSelectedVar("frmMultiselectListDemo","lboJobTitle",[Title])=-1 AND 
IsSelectedVar("frmMultiselectListDemo","lboEmployeeID",[EmployeeID])=-1;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]