Debug Help

[modest]

I am getting an error with the following code:

Public Function QueryExists(queryName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    QueryExists = False
    
    For Each qdf In db.QueryDefs
        If qdf.Name = queryName Then
            QueryExists = True
            Exit Function
        End If
    Next qdf
End Function

Public Function FindCityState(table As String, cityField As String, stateField As String, _
                              cityOutputField As Variant, stateOutputField As Variant)
    Dim db As DAO.Database                      'declare variable to hold db/workspace name
    Dim qdf As DAO.QueryDef                     'declare variable to hold query info
    Dim rs As DAO.Recordset                     'declare variable to reference query result table
    
    Dim strSQL As String                        'declare sql string variable
    Dim queryName As String                     'declare query name variable
    
    Set db = CurrentDb                          'Set name of database
    queryName = "tempQuery"                     'Set name of temporary query
    
    strSQL = "SELECT DISTINCT [3 CITY],[3 ST] " & _
             "FROM " & table & " " & _
             "WHERE " & cityField & "=" & table & ".[5 CITY] " & _
             "AND " & stateField & "=" & table & ".[5 ST];"
    
    If Not QueryExists(queryName) Then
        Set qdf = db.CreateQueryDef(queryName)
    Else
        Set qdf = db.QueryDefs(queryName)
    End If
    
    qdf.SQL = strSQL                            'Match the SQL to the query name
    
    Set rs = db.OpenRecordset(queryName)
    cityOutputField = rs![3 city]
    stateOutputField = rs![3 st]
     
    rs.Close
    Set rs = Nothing
    db.QueryDefs.Delete queryName              'working to put this when query window closes
    
End Function

When I run I get "Run-time error '3061' Too few paramaters expected 2" In reference to the db.OpenRecordset() line at the end. The function is called from a button event with:

FindCityState table, "forms!Main!oCity", "forms!Main!oState", Me.OriginCity, Me.OriginState

and table is also a string variable

 

[Mile-O]

Do a search on your error message expected parameters - the solution to this has been answered several times.

 

[modest]

SJ,
I actually had already completed a search on this problem and could only find the "expected 1" or in one case "expected 20"... not "expected 2".

I should have included that in my post because I saw where you had posted that same reply in other people's posts. If someone would be so kind to point me in the right direction to a specific link (thread), I would be much obliged.

-modest

 

[Mile-O]

Have a look at this.

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=176367

 

[modest]

Thank you SJ, that semi-helped! I didn't read it all, but something there sparked my memory.

If you choose not to delete this thread, here's the posted reason for my problem and solution to it:

The problem existed in the SQL statement that dealt with how it was receiving the variable from the textbox. Although the code would work when clicking the actual query, someone said it has to do with how JET interprets the data.

The solution was to change the function header to:

Public Function FindCityState(table As String, cityField As [B]Variant[/B], stateField As [B]Variant[/B], formObject As Variant)

and to change the way the variables were passed in to:

    FindCityState table, _
                  [Forms]![Main]![oCity].[Value], _
                  [Forms]![Main]![oState].[Value], _
                  Me

 

[jgc31]

I suspect that has to do with your SQL syntax. If the variables are strings ( which they were originally) then where statements should be WHERE somefield = """ & strString & """" or equivalent "'" & strString & "'" . I believe that for variants ( always try to avoid them) it is the same as numbers ie =" & intNumber

 

[modest]

What I was originally doing was passing the parameter as a string. This would actually work in the query builder, so I wasn't far off. What I came to learn is that I needed to pass the parameter by value so that the JET reader could interpret it correctly at run-time.

This changed how I needed to define the variable and how I would ultimately pass it in.


-modest