Search Form error (1 Viewer)

[mohamedmatter]

i create search form by useing vba but error display . i selected the error but i can't solve it . i attach jpg to error

 

[theDBguy]

Hi. Try using single quotes. ‘fail’

 

[essaytee]

Do a Debug.print of your SQL variable and post here.

Your screenshot does not show the entire creation of your SQL query.

I haven't analysed it for syntax errors at this point in time.

 

[mohamedmatter]

View attachment Employees.accdb

Do a Debug.print of your SQL variable and post here.

Your screenshot does not show the entire creation of your SQL query.

I haven't analysed it for syntax errors at this point in time.

this is code
Private Sub Text1_Change()
Dim SQL As String
SQL = " SELECT TblSchool.SchoolName, TblSchool.Stage, TblFail.Lab, TblFail.Device, TblFail.DeviceKind, TblFail.DescriFail, TblFail.DoFail, TblFail.DeviceDrag, TblFail.StateRepair, TblFail.Note " _
& " FROM TblSchool INNER JOIN TblFail ON TblSchool.SchoolID = TblFail.SchoolID" _
& " WHERE (((TblFail.StateRepair)= " fail ")) " _
& " ORDER BY TblSchool.SchoolName"
Me.FrmHlpSearch.Form.RecordSource = SQL
Me.FrmHlpSearch.Form.Requery
End Sub

 

[theDBguy]

View attachment 74724
this is code
Private Sub Text1_Change()
Dim SQL As String
SQL = " SELECT TblSchool.SchoolName, TblSchool.Stage, TblFail.Lab, TblFail.Device, TblFail.DeviceKind, TblFail.DescriFail, TblFail.DoFail, TblFail.DeviceDrag, TblFail.StateRepair, TblFail.Note " _
& " FROM TblSchool INNER JOIN TblFail ON TblSchool.SchoolID = TblFail.SchoolID" _
& " WHERE (((TblFail.StateRepair)= " fail ")) " _
& " ORDER BY TblSchool.SchoolName"
Me.FrmHlpSearch.Form.RecordSource = SQL
Me.FrmHlpSearch.Form.Requery
End Sub

Hi. Did you see my post? Try changing this line to use a single quote instead of a double quote.

& " WHERE (((TblFail.StateRepair)= 'fail')) " _

Hope it helps...

 

[mohamedmatter]

Hi. Did you see my post? Try changing this line to use a single quote instead of a double quote.

& " WHERE (((TblFail.StateRepair)= 'fail')) " _

Hope it helps...

I had some problems and so I attached the file. Thank you for your help

 

[theDBguy]

I had some problems and so I attached the file. Thank you for your help

Did it work?

 

[mohamedmatter]

any help for this problem

 

[theDBguy]

any help for this problem

Did you see my last post? What exactly happened when you tried my suggestion? I didn't see any file attachment in your earlier post.

 

[Gasman]

What you attached is not what you are posting.?

That did not work anyway, you had two WHEREs and no spaces between words?

This works.
As requested after you create the SQL string
Debug.Print SQL

and you should see all your errors.

HTH

Private Sub Searchtxt_Change()
Dim SQL As String
SQL = "SELECT Company.[Emp-No2], Company.Company, Company.Job, Company.Department, Employeetbl.FullName, Employeetbl.Dob, Employeetbl.[Work status] FROM Employeetbl INNER JOIN Company ON Employeetbl.[Emp-No] = Company.[Emp-No2] WHERE (((Employeetbl.[Work status])='works'))" _
& " AND ([Emp-No2] LIKE '*" & Me.SearchTxt.Text & "*' " _
& " OR [FullName] LIKE '*" & Me.SearchTxt.Text & "*' )" _
& " ORDER BY Company.[Emp-No2] DESC"
Me.FrmHlpSearch.Form.RecordSource = SQL
'Me.FrmHlpSearch.Form.Requery
End Sub

 

[theDBguy]

What you attached is not what you are posting.?

That did not work anyway, you had two WHEREs and no spaces between words?

Oh, I guess I missed the attachment. I was only looking at the image and code posted earlier. Thanks for the clarification.

 

[Gasman]

Oh, I guess I missed the attachment. I was only looking at the image and code posted earlier. Thanks for the clarification.

theDBguy,

The O/P might have posted the wrong DB.
The one posted was about Company, the code posted is about School, so I am none the wiser.

 

[mohamedmatter]

What you attached is not what you are posting.?

That did not work anyway, you had two WHEREs and no spaces between words?

This works.
As requested after you create the SQL string
Debug.Print SQL

and you should see all your errors.

HTH

Private Sub Searchtxt_Change()
Dim SQL As String
SQL = "SELECT Company.[Emp-No2], Company.Company, Company.Job, Company.Department, Employeetbl.FullName, Employeetbl.Dob, Employeetbl.[Work status] FROM Employeetbl INNER JOIN Company ON Employeetbl.[Emp-No] = Company.[Emp-No2] WHERE (((Employeetbl.[Work status])='works'))" _
& " AND ([Emp-No2] LIKE '*" & Me.SearchTxt.Text & "*' " _
& " OR [FullName] LIKE '*" & Me.SearchTxt.Text & "*' )" _
& " ORDER BY Company.[Emp-No2] DESC"
Me.FrmHlpSearch.Form.RecordSource = SQL
'Me.FrmHlpSearch.Form.Requery
End Sub

that work thanks
why you ' ? before Me.FrmHlpSearch.Form.Requery and this
Me.FrmHlpSearch.Form.Requery is problem
thank you

 

[pbaldy]

why you ' ? before Me.FrmHlpSearch.Form.Requery and this
Me.FrmHlpSearch.Form.Requery is problem
thank you

Gasman is probably asleep. In my experience the requery line isn't necessary; setting the record source will requery the form. Gasman may have had the same experience thus commented it out.

 

[mohamedmatter]

theDBguy,

The O/P might have posted the wrong DB.
The one posted was about Company, the code posted is about School, so I am none the wiser.

Thank you for your interest

 

[Gasman]

Thank you Paul,

Yes exactly that, though I seem to recall I was told that here at AWF.

Gasman is probably asleep. In my experience the requery line isn't necessary; setting the record source will requery the form. Gasman may have had the same experience thus commented it out.