QuerdyDef driving me nuts

[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&quot



Set qdfTemp = dbsMyTry.CreateQueryDef("&quot

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.

 

[FancyPrairie]

I'm not sure I understand your question, but I do see a problem with your query; your missing tic marks (single quotes) around your string. It should look like:

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

 

[dynamictiger]

Thanks, I had just realised this when you posted this. Now working fine. Strange these were not required in 2000 but are in XP and 97.

Microsoft moving forwards?

 

[Aristarco]

The apostrophes to specify a string are always needed. I use Access 2000 and they are required or the error you've had shows its ugly face. i wonder why you say "but not in Access 2000". Strange for me that your code worked without the (') marks. To boldly code, where no programmer has compiled before!