Search Form won't return records

C

carriey

Hello There, I got this code off of some website many moons ago and have used
it dozens of times in building Search Forms. For some reason, this time I
can't seem to get it to return any results. At one point I did have trouble
before with the SQL statement but have gone through all my notes and believe
that this is the same as working copies of code that I have - I even played
around with the &'s and _'s but that didn't seem to do the trick.

I have double and triple checked this but must be missing a really silly
mistake. If anyone out there with a fresh pair of eyes can spot my error I
will forever indebted to you!!!!

Option Compare Database
Option Explicit

Private Sub List_Results_DblClick(Cancel As Integer)
'Open Main Form based on the ID from List_Results
DoCmd.OpenForm "C_Frm_Main_Form", , , "[Employee_ID] = " & Me.List_Results,
, acDialog
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Search_Button_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select Statement for the Row Source

strSQL = "SELECT C_Tbl_Employees.Employee_ID, C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName" _
& "FROM Tbl_Department_Name" _
& "RIGHT JOIN C_Tbl_Employees" _
& "ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department"

strWhere = "WHERE"

strOrder = "ORDER BY C_Tbl_Employees.[Employee_ID];"

'Set the WHERE Clause for the Results
If Not IsNull(Me.TxtEmployID) Then
strWhere = strWhere & " (C_Tbl_Employees.[Employee_ID]) Like '*" &
Me.TxtEmployID & "*' AND"
End If

If Not IsNull(Me.txtADPID) Then
strWhere = strWhere & " (C_Tbl_Employees.[ADPEmployeeID]) Like '*" &
Me.txtADPID & "*' AND"
End If

If Not IsNull(Me.cboLast) Then
strWhere = strWhere & " (C_Tbl_Employees.[LastName]) Like '*" & Me.cboLast &
"*' AND"
End If

If Not IsNull(Me.cboFirst) Then
strWhere = strWhere & " (C_Tbl_Employees.[FirstName]) Like '*" & Me.cboFirst
& "*' AND"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " (Tbl_Department_Name.[DepartmentName]) Like '*" &
Me.cboDept & "*' AND"
End If

'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Debug strSQL, strWhere and strOrder
Debug.Print strSQL
Debug.Print strWhere
Debug.Print strOrder

'Display strSQL Message Box --> remove single quote from line below if you
need to debug
'MsgBox strSQL

'Pass the SQL to the Rowsource of the listbox
Me.List_Results.RowSource = strSQL & " " & strWhere & " " & strOrder

End Sub
 
S

Stuart McCall

carriey said:
Hello There, I got this code off of some website many moons ago and have
used
it dozens of times in building Search Forms. For some reason, this time I
can't seem to get it to return any results. At one point I did have
trouble
before with the SQL statement but have gone through all my notes and
believe
that this is the same as working copies of code that I have - I even
played
around with the &'s and _'s but that didn't seem to do the trick.

I have double and triple checked this but must be missing a really silly
mistake. If anyone out there with a fresh pair of eyes can spot my error
I
will forever indebted to you!!!!

Option Compare Database
Option Explicit

Private Sub List_Results_DblClick(Cancel As Integer)
'Open Main Form based on the ID from List_Results
DoCmd.OpenForm "C_Frm_Main_Form", , , "[Employee_ID] = " &
Me.List_Results,
, acDialog
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Search_Button_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select Statement for the Row Source

strSQL = "SELECT C_Tbl_Employees.Employee_ID,
C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName" _
& "FROM Tbl_Department_Name" _
& "RIGHT JOIN C_Tbl_Employees" _
& "ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department"

strWhere = "WHERE"

strOrder = "ORDER BY C_Tbl_Employees.[Employee_ID];"

'Set the WHERE Clause for the Results
If Not IsNull(Me.TxtEmployID) Then
strWhere = strWhere & " (C_Tbl_Employees.[Employee_ID]) Like '*" &
Me.TxtEmployID & "*' AND"
End If

If Not IsNull(Me.txtADPID) Then
strWhere = strWhere & " (C_Tbl_Employees.[ADPEmployeeID]) Like '*" &
Me.txtADPID & "*' AND"
End If

If Not IsNull(Me.cboLast) Then
strWhere = strWhere & " (C_Tbl_Employees.[LastName]) Like '*" & Me.cboLast
&
"*' AND"
End If

If Not IsNull(Me.cboFirst) Then
strWhere = strWhere & " (C_Tbl_Employees.[FirstName]) Like '*" &
Me.cboFirst
& "*' AND"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " (Tbl_Department_Name.[DepartmentName]) Like '*" &
Me.cboDept & "*' AND"
End If

'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Debug strSQL, strWhere and strOrder
Debug.Print strSQL
Debug.Print strWhere
Debug.Print strOrder

'Display strSQL Message Box --> remove single quote from line below if you
need to debug
'MsgBox strSQL

'Pass the SQL to the Rowsource of the listbox
Me.List_Results.RowSource = strSQL & " " & strWhere & " " & strOrder

End Sub
Click to expand...

The following lines are all missing leading space characters:

& "FROM Tbl_Department_Name" _
& "RIGHT JOIN C_Tbl_Employees" _
& "ON Tbl_Department_Name.Tbl_Department_NameID =

strOrder = "ORDER BY C_Tbl_Employees.[Employee_ID];"

They should read:

& " FROM Tbl_Department_Name" _
& " RIGHT JOIN C_Tbl_Employees" _
& " ON Tbl_Department_Name.Tbl_Department_NameID =

strOrder = " ORDER BY C_Tbl_Employees.[Employee_ID];"
 
C

carriey

Thank you Stuart for such a quick response. I added in those leading space
characters (feeling very sheepish indeed) and still I am not getting any
results returned. I did save and close and go back in, just in case that was
a problem but no luck. Any other ideas?

Stuart McCall said:
carriey said:
Hello There, I got this code off of some website many moons ago and have
used
it dozens of times in building Search Forms. For some reason, this time I
can't seem to get it to return any results. At one point I did have
trouble
before with the SQL statement but have gone through all my notes and
believe
that this is the same as working copies of code that I have - I even
played
around with the &'s and _'s but that didn't seem to do the trick.

I have double and triple checked this but must be missing a really silly
mistake. If anyone out there with a fresh pair of eyes can spot my error
I
will forever indebted to you!!!!

Option Compare Database
Option Explicit

Private Sub List_Results_DblClick(Cancel As Integer)
'Open Main Form based on the ID from List_Results
DoCmd.OpenForm "C_Frm_Main_Form", , , "[Employee_ID] = " &
Me.List_Results,
, acDialog
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Search_Button_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select Statement for the Row Source

strSQL = "SELECT C_Tbl_Employees.Employee_ID,
C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName" _
& "FROM Tbl_Department_Name" _
& "RIGHT JOIN C_Tbl_Employees" _
& "ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department"

strWhere = "WHERE"

strOrder = "ORDER BY C_Tbl_Employees.[Employee_ID];"

'Set the WHERE Clause for the Results
If Not IsNull(Me.TxtEmployID) Then
strWhere = strWhere & " (C_Tbl_Employees.[Employee_ID]) Like '*" &
Me.TxtEmployID & "*' AND"
End If

If Not IsNull(Me.txtADPID) Then
strWhere = strWhere & " (C_Tbl_Employees.[ADPEmployeeID]) Like '*" &
Me.txtADPID & "*' AND"
End If

If Not IsNull(Me.cboLast) Then
strWhere = strWhere & " (C_Tbl_Employees.[LastName]) Like '*" & Me.cboLast
&
"*' AND"
End If

If Not IsNull(Me.cboFirst) Then
strWhere = strWhere & " (C_Tbl_Employees.[FirstName]) Like '*" &
Me.cboFirst
& "*' AND"
End If

If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " (Tbl_Department_Name.[DepartmentName]) Like '*" &
Me.cboDept & "*' AND"
End If

'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Debug strSQL, strWhere and strOrder
Debug.Print strSQL
Debug.Print strWhere
Debug.Print strOrder

'Display strSQL Message Box --> remove single quote from line below if you
need to debug
'MsgBox strSQL

'Pass the SQL to the Rowsource of the listbox
Me.List_Results.RowSource = strSQL & " " & strWhere & " " & strOrder

End Sub
Click to expand...

The following lines are all missing leading space characters:

& "FROM Tbl_Department_Name" _
& "RIGHT JOIN C_Tbl_Employees" _
& "ON Tbl_Department_Name.Tbl_Department_NameID =

strOrder = "ORDER BY C_Tbl_Employees.[Employee_ID];"

They should read:

& " FROM Tbl_Department_Name" _
& " RIGHT JOIN C_Tbl_Employees" _
& " ON Tbl_Department_Name.Tbl_Department_NameID =

strOrder = " ORDER BY C_Tbl_Employees.[Employee_ID];"
Click to expand...
 
S

Stuart McCall

carriey said:
Thank you Stuart for such a quick response. I added in those leading
space
characters (feeling very sheepish indeed) and still I am not getting any
results returned. I did save and close and go back in, just in case that
was
a problem but no luck. Any other ideas?
Click to expand...

Could you please copy and paste these results here?
 
C

carriey

I hope this is correct - this is what I get in the immediate window after I
try to run a search (thanks again):

SELECT C_Tbl_Employees.Employee_ID, C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName FROM Tbl_Department_Name RIGHT JOIN C_Tbl_Employees
ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department
WHERE (C_Tbl_Employees.[LastName]) Like '*White*
ORDER BY C_Tbl_Employees.[Employee_ID];
 
S

Stuart McCall

carriey said:
I hope this is correct - this is what I get in the immediate window after I
try to run a search (thanks again):

SELECT C_Tbl_Employees.Employee_ID, C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName FROM Tbl_Department_Name RIGHT JOIN
C_Tbl_Employees
ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department
WHERE (C_Tbl_Employees.[LastName]) Like '*White*
ORDER BY C_Tbl_Employees.[Employee_ID];
Click to expand...

The only mistake I can spot is a missing apostrophe from the end of this
line:

WHERE (C_Tbl_Employees.[LastName]) Like '*White*
 
C

carriey

So now my true non-coding colors are showing through! Where on earth would I
look for that missing apostrophe in the code?

Thanks again!!!!

Stuart McCall said:
carriey said:
I hope this is correct - this is what I get in the immediate window after I
try to run a search (thanks again):

SELECT C_Tbl_Employees.Employee_ID, C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName FROM Tbl_Department_Name RIGHT JOIN
C_Tbl_Employees
ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department
WHERE (C_Tbl_Employees.[LastName]) Like '*White*
ORDER BY C_Tbl_Employees.[Employee_ID];
Click to expand...

The only mistake I can spot is a missing apostrophe from the end of this
line:

WHERE (C_Tbl_Employees.[LastName]) Like '*White*
Click to expand...
 
C

carriey

My eyes are burning!!!!! After going through the code over and over
comparing it to another database, I found that I needed 2 spaces before the
AND:
"*' AND"

I only had one space. Just thought I'd post in case anyone else comes
across this. Sometimes I find this coding so tedious!

Stuart McCall said:
carriey said:
I hope this is correct - this is what I get in the immediate window after I
try to run a search (thanks again):

SELECT C_Tbl_Employees.Employee_ID, C_Tbl_Employees.ADPEmployeeID,
Tbl_Department_Name.DepartmentName, C_Tbl_Employees.LastName,
C_Tbl_Employees.FirstName FROM Tbl_Department_Name RIGHT JOIN
C_Tbl_Employees
ON Tbl_Department_Name.Tbl_Department_NameID =
C_Tbl_Employees.Lookup_Department
WHERE (C_Tbl_Employees.[LastName]) Like '*White*
ORDER BY C_Tbl_Employees.[Employee_ID];
Click to expand...

The only mistake I can spot is a missing apostrophe from the end of this
line:

WHERE (C_Tbl_Employees.[LastName]) Like '*White*
Click to expand...
 
S

Stuart McCall

carriey said:
My eyes are burning!!!!! After going through the code over and over
comparing it to another database, I found that I needed 2 spaces before
the
AND:
"*' AND"

I only had one space. Just thought I'd post in case anyone else comes
across this. Sometimes I find this coding so tedious!
Click to expand...

So I take it the code is now working, ie producing the results you expect?
That's great. Next time it won't be so hard, considering the experience you
got debugging this.

Sorry to have to tell you that your fix mentioned above is a red herring.
One or two spaces makes no difference in SQL. You must have (deliberately or
inadvertently) changed something else.

We all (at any level of experience) suffer from 'code eyes' at one time or
another. You can stare at code till your eyes hurt and still miss something
really obvious. One of the features of the 'human condition', I'm afraid.

One thing I've found that helps is to use the 'cardboard programmer'
technique to flush these bugs out. Here's what you do: collar a colleague,
database-literate or not (not preferably), and ask them to help you out by
sitting with you while you describe what each portion of code is doing (or
supposed to be doing :). As you describe it, more often than not, you'll
spot a bug. Fix it, test it. If it's still not right, carry on describing...

The reason it's called the cardboard programmer is that you could do just as
well describing code to a cardboard-cutout of your colleague. The idea is to
force yourself to look at the code in detail, which you may have thought you
were doing before, but in reality weren't.

Eventually you won't need an actual person and you'll be able to do it in
your head.

Hope that helps
 
S

Stuart McCall

'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
Click to expand...

I think your real problem lies here. That code, which shaves off the last 5
characters from your sql string, is taking the last apostrophe with it. The
line ought to read:

strWhere = Mid(strWhere, 1, Len(strWhere) - 4)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with ADP code 5
Search form problem 1
Listbox double click event 8
Display Yes/No instead of -1, 0 on Search Forms 1
Search Form with a SubForm 3
Require a field in search form 1
Modified Allenbrowne Search Form displays error 1
Question on Allen Browne's Search form (adding a sort) 1

Top