Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Setting the field value for a query at r

Locked

I am currently trying to set up a query where the field that is searched is user defined at runtime via a combo box. Any suggestions? I already tried using the Expression Builder to point to the combo box but no luck. Please help me if you can!!All my points t the answer that helps the most.

Topic

Setting the field value for a query at r

In reply to Setting the field value for a query at r

Two ways: Set up a query for every possibility and choose the correct query based on the combo box contents OR (Better way) use visual basic to modify the query before it is run. see querydef object in the object browser for examples. Basically what you do is build an sql string out of variables that are present at runtime and use that string as the sql statement in the query.

Setting the field value for a query at r

In reply to Setting the field value for a query at r

I was given the following code yesterday for my problem, but it keeps giving me an error:

Private Sub btnSearch_Click()

Dim strSQL As String

strSQL = “SELECT * FROM tblBugs WHERE ” & “[” & Me!FieldList & “] = ” & Chr(34) & Me!TextSearch & Chr(34)

Me!frmBugEntrySubform.Form.RecordSource = strSQL

End Sub

the error is:
runtime error ‘2001’:
you canceled the previous operation.

I have a main form called frmBugEntry,
a sub form called frmBugEntrySubform, a button called btnSearch, a combobox called FieldList, a text box called TextSearch.

the combobox is a list of all the fields on my table (tblBugs), the text box is for users to type in a searchable string that may be found in the field selected through the combobox. then click the button (btnSearch) the occlick event for the button is the code above.

any ideas????

[Author]

Replies