mohamedmatter
Registered User.
- Local time
- Yesterday, 22:51
- Joined
- Oct 25, 2015
- Messages
- 112
this is codeDo 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.
Hi. Did you see my post? Try changing this line to use a single quote instead of a double quote.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
& " WHERE (((TblFail.StateRepair)= 'fail')) " _
Hi. Did you see my post? Try changing this line to use a single quote instead of a double quote.Hope it helps...Code:& " WHERE (((TblFail.StateRepair)= 'fail')) " _
Did it work?I had some problems and so I attached the file. Thank you for your help
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.any help for this problem
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
Oh, I guess I missed the attachment. I was only looking at the image and code posted earlier. Thanks for the clarification.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.
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
Code: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
why you ' ? before Me.FrmHlpSearch.Form.Requery and this
Me.FrmHlpSearch.Form.Requery is problem
thank you
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.
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.