SQL Where clause

[WilliamI]

I have tried many syntax variation but still get 'runtime error 3601. too few
parameters. Expected 1' from the following code:

Private Sub AcctClass_AfterUpdate()

Dim db As Database, frm As Form
Dim rst As Recordset, strSQL As String

strSQL = "SELECT AcctClass.AcctClassN " & _
"FROM AcctClass " & _
"WHERE AcctClass.AcctClass=" & Forms!fdlgChartAccts_DE!AcctClass & "
;"

Set frm = Forms!fdlgChartAccts_DE
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

frm!AcctClassN = rst!AcctClassN

rst.Close

End Sub

 

[Douglas J. Steele]

What data type is AcctClass? If it's text, you need to enclose the value in
quotes:

strSQL = "SELECT AcctClass.AcctClassN " & _
"FROM AcctClass " & _
"WHERE AcctClass.AcctClass= '" & Forms!fdlgChartAccts_DE!AcctClass &
"'"

Exagerated for clarity, that's

strSQL = "SELECT AcctClass.AcctClassN " & _
"FROM AcctClass " & _
"WHERE AcctClass.AcctClass= ' " & Forms!fdlgChartAccts_DE!AcctClass
& " ' "

(Note that the semicolon isn't required)

 

[WilliamI]

I now get a type 13 error - type mismatch. AcctClass is a table with a text
field named AcctClass. AcctClassN is a Long Integer field. I can't find any
type conflict between the table fields and the form controls.

 

[Douglas J. Steele]

Since you're not getting an error on Dim db As Database, you obviously have
a reference set to DAO. However, if you also have a reference set to ADO,
then Dim rst As Recordset will be interpretted as an ADO recordset, not a
DAO one (which is what you need.)

Change the declaration to Dim rst As DAO.Recordset. (While not required,
there's nothing wrong with changing the database declaration to Dim db As
DAO.Database, just so it's obvious)