Syntax Error Help needed with Search Box

[jonmitch]

I have looked through other threads here and have come further along in my search function dilemma, but need someone to look at this code:

I have created an unbound textbox on my switchboard called txtFind and created a command button called cmdFind. In the On Click Event, here is my code:

Me.RecordSource = "SELECT * FROM Master Table " & "WHERE CompanyName LIKE '*" & txtFind & "*'"

When I type in a company name and click the command button, I get a Run Time Error '3131' saying there is Syntax Error in the FROM clause.

Does anyone know how I can get this to work?

For those who did not read my previous posts, I am trying to create a search function on my switchboard so that people can type in a company name and it will bring them to the corresponding record in the Master Form.

 

[essnrv]

Me.RecordSource = "SELECT * FROM [Master Table] " & "WHERE CompanyName LIKE '*" & txtFind & "*'"
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[jonmitch]

That fix did get rid of the error message so thank you, but now it has caused a new error message:
RunTime Error '2465'
Microsoft Access cannot find the field 'ItemText' referred to in your expression.
When I hit debug, I am brought to this section of code:

Private Sub Form_Current()
' Update the caption and fill in the list of options.

Me.Caption = Nz(Me![ItemText], "&quot
FillOptions
End Sub

I have no clue what this is, do you? When I attempt to remove, and then type in text and hit the commmand button, nothing happens. Therefore, I am assuming it serves a purpose.

Any ideas?

 

[essnrv]

please check Control Name, if every thing is ok then move the code to form_open event Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[jonmitch]

Ok, we are making progress...I moved the code to form_open event and do not get the error message anymore, however now when I type in text and hit the command button it does nothing. Do I need to add in some code to go to the master form from the where I am (which is the switchboard)? It seems it is looking for text on the switchboard and not looking for text in CompanyName on the Master Form?

I am confused. Any ideas?

Thank you for you time on this.

 

[essnrv]

In Command Click Event
Dim strSQL as string

'If you want to show result on same form then below code works
strsql = "SELECT * FROM [Master Table] " & "WHERE CompanyName LIKE '*" & Me![txtFind] & "*'"
Me.RecordSource= strsql
Me. Requery
If you want to show on another form Say frmMain then
dim Form1 as Form
set Form1= Forms![frmMain].Form
strsql = "SELECT * FROM [Master Table] " & "WHERE CompanyName LIKE '*" & Me![txtFind] & "*'"
Form1.RecordSource= strsql
Form1.Requery Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[jonmitch]

I would like so that when you type in a company name in the textbox on the "Switchboard" and then Click the Command Button...it will bring up the record in "Master Form" (a form that I already created).

Here is the code that I have in the On Click event of the command button on the "Switchboard" form.

Private Sub cmdFind_Click()
Dim strSQL As String
Dim Form1 As Form
Set Form1 = Forms![Master Form].Form
strSQL = "SELECT * FROM [Master Table] " & "WHERE CompanyName LIKE '*" & Me![txtFind] & "*'"
Form1.RecordSource = strSQL
Form1.Requery

End Sub

I now get an error message telling me that it cannot find the form "Master Form" as referred to in the code. That is however the correct name of the form.

I think I am at a loss, unless you have any further suggestions.

Thanks

 

[essnrv]

I think your Master Form is not opened i.e. Not loaded.
1.Is you Master Form is bound to table or a query or a unbound form ?
2.where is your Search button ?
3.Is it on Master Form ?

Can you mail me your DB with Master Form,Search Form and Master Table ?
email address: needjob9@yahoo.com Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[essnrv]

I mailed you back. Let me know how it works Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!