Search database with form combobox

[cuddles]

I have a form and a subform. The form contains a combobox I want the end-user to use to search with. The subform contains what will be displayed when the end-user searches. The combobox is bound to the table as are the subform items. I have it displayed as a formview and not datasheet because the items being returned are too much for the screen so I want it to look nice and neat. It does not return anything when I search and I think it has something to do with the txtsearchstring portion. Also, how does the end-user without doing this in a datasheet click something to get to the next record until they get to the record they are looking for?
I am not sure how to modify my code to make it work. Here is my code:

Private Sub Search_Click()
Dim LSQL As String
Dim LSearchString As String
If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must select a Measure or go back to the Main Menu."
Else
LSearchString = txtSearchString
LSQL = "select * from Measure"
lqsl = LSQL & " where Measure like '*" & LSearchString & "*'"
txtSearchString = ""
MsgBox "Results are displayed."
End If
End Sub

[ranman256]

There is no need for code.
1. NEXT RECORD..the subform should have the property NAVIGAION BUTTONS = yes. The record controls will show at the bottom for the user to click. (front , back, end, 1st rec, etc)
2. The sub form is connected to a query. The query references the cboBox as criteria (where clause). Then a SearchClick event will be subForm.requery.

No code needed.

[cuddles]

I am not sure how to query refence the cbobox in the subform.

[ranman256]

The query would reference the combo box:
SELECT * FROM tZipCodes WHERE (tZipCodes.state)='" & forms!frmMain!cboState & "'"

Use the EXPRESSION BUILDER if needed.

[cuddles]

So you are saying my subforms control source should be the select statement?

[cuddles]

Nope. It does not like that in the control source.

[ranman256]

correct. If the user changes the combo box, you just refresh it... forms!frmMain!form!subForm.requery

[ranman256]

The subform.rowsource = "qsMyQuery"
The sql would be in the query.
The combo box would NOT be in the subform.
Make sure the sql ref. to the combo box is not inside the quotes.
This works...I do it all the time.

[cuddles]

I don't know what I am doing wrong. I have a mainform and a maintable. So in order to perform a search, I take this mainform and put my combobox which is a field from this maintable, but I guess I cannot really do it that way because I need a query. So, I create a query that includes all the fields and the 1 field that is my combobox I put a where on it. The control source then for the mainform and subform becomes this query? If so, then do I need a rowsource for the mainform combobox? I am trying all variations of what you posted and I get the following error:

The expression on open you entered as the event property setting produced the following error: Object or class does not support the set of events.

[cuddles]

Ok. What I have right now is in the mainform the form itself the controlsource is my maintable. Then when I click the combobox button and go to properties, I select the name of the field in the maintable as the controlsource. Then the rowsource is a query where I select the field. Then I go to my subform and the controlsource for that would be the query I create to point to the cbobox?

[ranman256]

I dont think you need the sub form.
The form would be a tabular list
in the form header, is a combo
the combo has a query like qsTopics...(this only shows 1 column of possible choices)

the form is a query like: qsMyQuery
the sql in qsMyQuery referenced the combo

the event of the combo box would be me.rowsource.requery (so the data changes when the combo box does)

[cuddles]

Ok. So I remove the subform and put the textboxes that will be filled in by the search in my details. the combobox is now in the header of the mainform. So it is setup like this, mainform controlsource is the query that includes the referencing of the cbobox with the =forms!etc....Then I bind the combo to the items that are in that field of the query. So that becomes my control source of the combo and then the row source is the select statement? I am just confused as to where all this stuff goes because the way I have it now changes it is adding whatever I display in that combobox to my main table. Like it is updating it. If I make the combo unbound it does not change anything but then I get an error of :

method or data not found and my onclick event of the combobox has:

me.rowsource.requery

But then what do I need a search button for? This is so confusing. Is there any sort of video I can watch out there that shows how to set this up? I have been looking but they all refer to using a textbox with like

[cuddles]

I got this working now.