By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Query criteria from Main Form

P: n/a
I have a main form with a text box on it "txtSearch" onlost focus it
updates another text box "txtSearchString" to include a quote and
asterik before and after the value of txtSeach.

I then have a query set to use forms!frmMain!txtSearchString as the
search criteria for a field. But it is not working. If I go and copy
the value of txtSearchString and paste it as cretieria in the query it
works but not live and dynamicly pulling it from the main form. My
criteria is set as

forms!frmMain!txtSearchString

and I even tried

= forms!frmMain!txtSearchString

Like forms!frmMain!txtSearchString

Any help is apreciated...

Dave

Oct 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I don't think you have provided enough detail to get a good answer.
Are you using a stored query, a temporary query or a query set in code?

You might try this. Set the module in the onclick event of a test button on
your form.
The button should do nothing else.

See if the output is what you are looking for.
Sub MyQueryTest()

Dim dbs As Object, rst As Object

Dim strSQL As String

Set dbs = CurrentDb()

strSQL = "Select Field1, Field2 " & _

"From tblOfInterest " & _

"Where Field1 Like *" & forms!frmMain!txtSearchString & "* ; "

Set rst = dbs.OpenRecordset(strSQL)

With rst

Do While Not .EOF()

Debug.print Field1

.MoveNext

Loop

End With

Set rst = nothing

Set dbs = nothing

End Sub

This way you know that the form is open when the query is invoked and thus
the txtBox's value is available.

Also the Like function needs to have a "quote" , "asterik" before and
"asterik" "quote" after.

Good luck or post better question.

<st********@hotmail.comwrote in message
news:11*********************@k70g2000cwa.googlegro ups.com...
>I have a main form with a text box on it "txtSearch" onlost focus it
updates another text box "txtSearchString" to include a quote and
asterik before and after the value of txtSeach.

I then have a query set to use forms!frmMain!txtSearchString as the
search criteria for a field. But it is not working. If I go and copy
the value of txtSearchString and paste it as cretieria in the query it
works but not live and dynamicly pulling it from the main form. My
criteria is set as

forms!frmMain!txtSearchString

and I even tried

= forms!frmMain!txtSearchString

Like forms!frmMain!txtSearchString

Any help is apreciated...

Dave

Oct 16 '06 #2

P: n/a
st********@hotmail.com wrote:
I have a main form with a text box on it "txtSearch" onlost focus it
updates another text box "txtSearchString" to include a quote and
asterik before and after the value of txtSeach.
No, don't do that. It's not how to use a wildcard.

I do something very similar to what you describe, EXCEPT I use the on
change event. In that event, I have:

Private Sub txtSearch_Change()
Me.txtSearchString = Me.txtSearch.Text
me.requery
End Sub

In the query that is the recordsource for your form, the criteria should be:

"*" & forms!frmMain!txtSearchString & "*"

for the query field CONTAINS the search string or

"*" & forms!frmMain!txtSearchString

for the query field ENDS WITH the search string or

forms!frmMain!txtSearchString & "*"

for the query field STARTS WITH the search string.

The requery is necessary to tell the form to check its recordsource query.

By doing the above in the on change event, you'll also see your results
narrow down with every letter entered into the txtSearch box.

BTW, I always have txtSearchString a hidden control, so the user only
sees what s/he is entering into the txtSearch control.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Oct 17 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.