.findfirst syntax invalid

[zoonkai]

on this form named enter_attendance i have a combo box to select names andother combo box to select the class and a text box for the date

when the command button is clicked it should check for the possibility of a duplicate record...and if it wont' be...it will add a new record to the attendance table

i'm having a problem with the syntax.....it doesn''t like
the .findfirst criteria

strcriteria = "[last_name] = '" & txt_Last_name & "' and [first_name] = '" & txt_First_name & "' and [date] = '" & txt_date & "' and [class] = '" & txt_Class & "'"

here's the error i keep getting
run time error 3070
the microsoft jet database engine does not recognize 'last_name' as a valid field name or expression


any help would be greatly appreciated

******************************
Private Sub Present_Click()
Dim dbs As Database, rst As Recordset
Dim strcriteria As String

Set dbs = CurrentDb
strcriteria = "[last_name] = '" & txt_Last_name & "' and [first_name] = '" & txt_First_name & "' and [date] = '" & txt_date & "' and [class] = '" & txt_Class & "'"
Set rst = dbs.OpenRecordset("test", dbOpenDynaset)
rst.FindFirst strcriteria
If rst.NoMatch Then
rst.addnew
rst!Last_Name = txt_Last_name
rst!First_Name = txt_First_name
rst!Time = txt_date
rst!Class = txt_Class
rst.Update

Else: MsgBox "this would create duplicate records"

End If
rst.Close
'set dbs = Nothing

End Sub
*************************** Donald (Zoonkai) Dixon
donnan@don-nan.com

http://www.don-nan.com

 

[MichaelRed]

the implication is that [Last_Name] is not a valid field in the recordset.

Other than that, I would try simpler criteria (just [last_name] ?) andbuild this up one part at a time. Try it in the debug window. See what strCriteria looks like after the concatenation of hte various parts.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ntp]

If "test" is a table you can't use the findfirst method unless the field you are applying the criteria to is an indexed field. If that is the case one way to get around is to create a temporary query and base your recordset on that.

Example:

...
Set qdf = currentdb.CreateQueryDef("","SELECT * FROM test&quot
set rst = qdf.openrecordset
rst.findfirst strCriteria
...

ntp

 

[MichaelRed]

No. FindFirst DOES NOT require an indexed filed. NTP is incorrect in hte statement.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over