Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002

    Angry Unanswered: First attempt at queryDef driving me nuts

    I am using Access XP, and have set the reference to DAO 3.6.
    Using the help files I have modified an example of a querydef to look up post codes from a table from Aussie Post. My code is as follows.

    Private Sub state_AfterUpdate()
    Dim dbsMyTry As DAO.Database
    Dim qdfTemp As DAO.QueryDef
    Dim rstAusPostCodes As DAO.Recordset

    Set dbsMyTry = OpenDatabase("C:/dynamic/workSolution/db1.mdb")

    Set qdfTemp = dbsMyTry.CreateQueryDef("")

    Dim txtSuburb As String
    Dim txtState As String

    txtSuburb = Me.suburb

    txtState = Me.state

    SQLOutput "SELECT AusPostCodes.Locality, AusPostCodes.State, AusPostCodes.Pcode " & _
    "FROM AusPostCodes " & _
    "WHERE (((AusPostCodes.Locality)= " & txtSuburb & " ) and (AusPostCodes.State)= " & txtState & "); ", _


    End Sub
    Function SQLOutput(strSQL As String, qdfTemp As DAO.QueryDef)

    Dim rstAusPostCodesA As DAO.Recordset

    Debug.Print strSQL

    qdfTemp.SQL = strSQL

    Debug.Print qdfTemp.SQL

    Set rstAusPostCodesA = qdfTemp.OpenRecordset

    Debug.Print strSQL

    Me.postcode = rstAusPostCodesA


    The debug statements Debug.Print strSQL and Debug.Print qdfTemp.SQL return the query in the manner I would expect. However on the line Set rstAusPostCodesA = qdfTemp.OpenRecordset I get an error message 3061, too few parameters expected two.

    I suspected rstAusPostCodesA was causing a problem, however, a debug of this does not throw an error. So, I assume it is something to do with qdfTemp.OpenRecordset, but I can't for the life of me see it.

    Checking back against the help example has provided no clues at all.

  2. #2
    Join Date
    Nov 2001

    You may not need to use DAO.QueryDef to accomplish your task.

    Try DLookup() function instead. MS Access help has excellent description of it.


  3. #3
    Join Date
    Feb 2002
    Dlookup is common knowledge to me. I am making a concentrated effort to learn how to use recordsets.

    I have now solved my problem.

    It was to do with formatting in the SQL statement

    "WHERE (((AusPostCodes.Locality)= '" & txtSuburb & "' ) and (AusPostCodes.State)= '" & txtState & "'); ",

    This caught me off guard as I was expecting to not need ' in format as it was required in 97 dlookup and not in 2000.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts