First Attempt at Query Def Driving me loopy

[dynamictiger]

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.

 

[Fornatian]

I had a similar frustration for a while then came across the answer(courtesy of PH again)

QUOTE

You will receive this error message if you do not explicitly set the value of the parameter query when using VBA
an example would be
dim db as database
dim qryDef as querydef
dim rst as recordset
set db = currentdb
set qrydef = db.querydefs.("MyQry")
' sets parameter to current value on form
qrydef![forms!formname!controlname] = forms![formname]![controlname]
'open recordset
set rst = qrydef.openrecordset()
[END QUOTE]

Hope this helps


Ian