My code seems to hate me....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a dynamic query using a form with checkboxes to select
search field criteria and build a dynamic SQL statement (yeah, I know, real
exciting so far). I'm trying to cookbook it out of a book called "Beginning
Access 2000 VBA" by Smith and Sussman, if that helps to reference where I'm
screwing up.

My module codes are as follows:

On the form:
-------------------------------------------------------------------------
Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "tblMaim.* "

strFROM = "tblMain "

If chkToolNum Then
strWHERE = strWHERE & " AND tblMain.tool_number = " & txtToolNum
End If

If chkToolName Then
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)

BuildSQLString = True

End Function
Private Sub butCancel_Click()
On Error GoTo Err_butCancel_Click

Dim stDocName As String

stDocName = "macCancelSearch"
DoCmd.RunMacro stDocName

Exit_butCancel_Click:
Exit Sub

Err_butCancel_Click:
MsgBox Err.Description
Resume Exit_butCancel_Click

End Sub

Private Sub cmdSearch_Click()

Dim stDocName As String

If Not EntriesValid Then Exit Sub

If Not BuildSQLString(strSQL) Then
MsgBox "Something is all screwed up"
Exit Sub

End If

MsgBox strSQL

CurrentDb.QueryDefs("qryModDef").SQL = strSQL

End Sub
-------------------------------------------------------------------------


From the Module (called Search Module A):
-------------------------------------------------------------------------

Function MakeQueryDef(strSQL As String) As Boolean

Dim qdf As QueryDef

If strSQL = "" Then Exit Function

Set qdf = CurrentDb.CreateQueryDef("qryModDef")
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

MakeQueryDef = True

End Function

Function ChangeQueryDef(StrQuery As String, strSQL As String) As Boolean

If StrQuery = "" Or strSQL = "" Then Exit Function

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs(StrQuery)
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow

ChangeQueryDef = True

End Function
-------------------------------------------------------------------------

The error message I get when I press my search button on the form is:

Compile error: ByRef argument type mismatch

and it then highlights the strSQL variable under my cmdSearch_Click()
subroutine after the If Not BuildSQLString(strSQL) portion of the subroutine.

Yes, I know that a book does not a cook make. I would try to compare what
is on the accompanying CD-ROM in the book, except the book didn't have the CD
when I purchased it (yes, I know, "why did you buy the book then, stupid?").
In any case, if you could please help reduce my self-flaggelation and reduce
how much this is causing me to drink, the help would be appreciated.
 
The cmdSearch_Click procedure has no string variable named strSql.

Suggestions:
1. Make sure the very top of your module (above all the code) has the line:
Option Explicit
This will help you track down places where you failed to declare your
variables.

2. Add this line to the cmdSearch_Click() procedure:
Dim strSql As String
This fixes the immediate problem.

3. Before you try to run it, choose Compile on the Debug menu.
This identifies any problems.
Fix them, and keep compiling until no error messages are given.

If you want a downloadable example of a search form to play with, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows how ot work with number fields, text fields (exact matches
and partial matches), dates (including date ranges), combo boxes, and
combinations thereof.
 
You, sir, are the man! Funny how one little missed declaration can bite a
guy that hard...

I will name my next child for you (unless it's a girl, because that would be
silly).

Seriously, thank you again for the help on this.
 
Back
Top