Strings, Query Search

[Jbajin]

I have two strings that I would like to incorporate into a search function.

Dim StringText As String
Dim StringCombo As String

StringText = Form1.SearchString
StringCombo = Form1.ComboSelect

*******Gold.RecordSource = "select * from Gold " & _
"where " & StringCombo & " = '" & StringText & "'"***********


Gold.Refresh
Form1.Hide


That is what I have as my search Button Function. Now where the astrerisk are is where i am having a problem. I want to be able to search in my database called Gold in the Table and in the Category that is listed in my ComboSelect Box. I am looking for what is in the StringText Variable. If that makes sense at all. I hope it does. I want to make this search feature available for all my categories not just one section of it.

[Jimmer]

I think your only problem is the syntax you are using in the Sql statement. Give this a try:

Dim fieldStr As String
Dim searchStr as string

set searchStr = Form1.SearchString
set fieldStr = Form1.ComboSelect.text

Gold.recordsource = "SELECT * FROM [GOLD] WHERE " & _
"[" & fieldStr & "] = '" & searchStr & _
"'"
gold.refresh

This is assuming that you are using a field that contains text, and not numeric or date fields. If you are using the other types of fields you would have to change the quotes to the correct syntax. Nothing for numeric fields and # for date fields.

Hope This Helps.

[Jbajin]

Thanks Jimmer for your help. I think I got the SQL statement now fixed. When I run the program and try to use the search feature, it gives me a "Compile Error Object Required". And It points to

Set searchStr = Form1.SearchString

I don't know what exactly is wrong with it. Can anyone help?

Thanks a Bunch.

[joedoer]

Do you have database set?
Something like Set db = CurrentDB

[Clunietp]

do not use Set when working with non-object variables. SET should only be used when assigning objects. remove the SET from your statements and you should be fine:

searchStr = Form1.SearchString
fieldStr = Form1.ComboSelect.text

[Jbajin]

Well I followed the suggestions and now I still got the problem back in the SQL statement. I am getting a Run-time error with Object Required.
This is what I have for the code as we speak.

Private Sub SearchButton_Click()

Dim fieldStr As String
Dim searchStr As String

searchStr = Form1.SearchString
fieldStr = Form1.ComboSelect.Text

Gold.RecordSource = "SELECT * FROM [GOLD] WHERE " & _
"[" & fieldStr & "] = '" & searchStr & _
"'"



Gold.Refresh


Form1.Hide

End Sub





Thanks,

[JHausmann]

It sounds like there's an object missing on your form

I'd replace the code you have:

Dim fieldStr As String
Dim searchStr As String

searchStr = Form1.SearchString
fieldStr = Form1.ComboSelect.Text

Gold.RecordSource = "SELECT * FROM [GOLD] WHERE " & _
"[" & fieldStr & "] = '" & searchStr & _
"'"


with

Gold.RecordSource = "SELECT * FROM [GOLD] WHERE " & _
"[" & trim(Form1.ComboSelect.Text) & "] = '" & trim(Form1.SearchString) & _
"'"

[Jbajin]

Man this is starting to suck. I am still getting the Run Time Error "424" Object required.

Any more ideas?

Joseph

[JHausmann]

It sounds like the form objects don't exist (or maybe you have a typo)...

[Jbajin]

The only thing I can think of is that I did the combo box wrong. Can someone tell me how to make entries into the combo box and I can see if I did it right?

Joe

[Jbajin]

ttt