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

    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 & "); ", _
    qdfTemp

    dbsMyTry.Close

    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

    rstAusPostCodesA.Close

    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
    Posts
    336
    Hi,

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

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

    HTH

  3. #3
    Join Date
    Feb 2002
    Posts
    403
    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
  •