Hi J,
-- PROBLEMS I see--
There is a typo in the function "Function BuildSQLString(strSQL As String)
As Boolean", in this line:
strSELECT = "tblMaim.* "
It should be tblmaiN.* with an "N" not "M".
And in the Sub "", you should have already changed
Dim stDocName As String
to
Dim strSQL As String
In the function "BuildSQLString", it looks like there might be some values
that are text. If so, they need to be delimited with (single) quotes.....
If chkToolName Then
strWHERE = strWHERE & " AND tblMain.tool_nomen = " & txtToolName
End If
if "txtToolName" is text then is should be
strWHERE = strWHERE & " AND tblMain.tool_nomen = '" & txtToolName & "'"
(after the equal sign it is single quote/ double quote
and at the end of the line it is double quote/single quote/ double quote)
----------------------------------------------------
Have you tried to step thru the code to see what it does?? In the IDE, click
on the line
Private Sub cmdSearch_Click()
then press the F9 key. The line should now have a brownish background and a
brown dot to the left. This is a breakpoint; the code will stop executing at
this point to allow you to step thru the code one line at a time (using the
F8 key) and see what values are in the variables.
If you click on the dot, the breakpoint will be removed. Click in the gray
area where the dot was to set the breakpoint again.
BTW, you could do all of this a lot easier in one subroutine....but for
learning sake, I'll use your code...
-----------------------------------------------
So, looking at the subroutine "Sub cmdSearch_Click()", what is
"EntriesValid"???? I'm guessing this is where your code takes the off ramp...
Then we come to this line:
If Not BuildSQLString(strSQL) Then
This says you are passing a variable to a function which returns a boolean.
But right now the function
"Function BuildSQLString(strSQL As String) As Boolean" ALWAYS returns
TRUE because of this line:
BuildSQLString = True
I modified your code a little...... see if it makes sense to you
Private Sub cmdSearch_Click()
' Dim stDocName As String
Dim strSQL As String
' commented out
' If Not EntriesValid Then Exit Sub
' If Not BuildSQLString(strSQL) Then
' MsgBox "Something is all screwed up"
' Exit Sub
' End If
strSQL = BuildSQLString
MsgBox strSQL
CurrentDb.QueryDefs("qryModDef").SQL = strSQL
End Sub
'-------
' Function BuildSQLString(strSQL As String) As Boolean
Function BuildSQLString() As string
' Dim strSELECT As String
' Dim strFROM As String
Dim strSQL As String
Dim strWHERE As String
' next line has a typo
' strSELECT = "tblMaim.* "
' strFROM = "tblMain "
strSQL = "SELECT tblMain.* FROM tblMain"
If chkToolNum Then
strWHERE = strWHERE & " AND tblMain.tool_number = " & txtToolNum
End If
If chkToolName Then
' needs to be delimited if txtToolName is text
strWHERE = strWHERE & " AND tblMain.tool_nomen = " & txtToolName
End If
If chkCategory Then
strWHERE = strWHERE & " AND tblMain.tool_category = " & ddmCategory
End If
If chkControlCode Then
strWHERE = strWHERE & " AND tblMain.tool_control_code = " & ddmCategory
End If
If chkPriority Then
strWHERE = strWHERE & " AND tblMain.tool_release_priority = " &
ddmPriority
End If
If chkStatus Then
strWHERE = strWHERE & " AND tblMain.tool_status = " & ddmStatus
End If
If chkToolEng Then
strWHERE = strWHERE & " AND tblMain.tool_eng = " & ddmToolEng
End If
If chkMfgEng Then
strWHERE = strWHERE & " AND tblMain.manuf_eng = " & ddmMfgEng
End If
If chkTDRNum Then
strWHERE = strWHERE & " AND tblMain.tdr_number = " & txtTDRNum
End If
If chkTDRType Then
strWHERE = strWHERE & " AND tblMain.tdr_type = " & ddmTDRType
End If
If chkSupplier Then
strWHERE = strWHERE & " AND tblMain.tool_supplier = " & ddmSupplier
End If
If chkDueDate Then
If Not IsNull(txtDateFrom) Then
strWHERE = strWHERE & " AND tblMain.tool_reqd_date >= #" &
Format$(txtDateFrom, "mm/dd/yyyy") & "#"
End If
If Not IsNull(txtDateTo) Then
strWHERE = strWHERE & " AND tblMain.tool_reqd_date <= #" &
Format$(txtDateTo, "mm/dd/yyyy") & "#"
End If
End If
If chkOldTool Then
strWHERE = strWHERE & " AND tblMain.old_tool_number = " & txtOldTool
End If
' strSQL = "SELECT " & strSELECT
' strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
'return the SQL string
BuildSQLString = strSQL
End Function
HTH
