FindFirst

[Rachel]

I have used this code many times and have no idea why it
is not working for me now. I receive a run-time error
3077 syntax error (missing operator) on the rst.findfirst
line. I have a field on a form that I want the users to
be able to update if new information is needed. The
field is tied to a table.

Dim dbs As Database
Dim rst As Recordset
Dim strName As String


Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("QualityAnalysts",
dbOpenDynaset)

'strName = Me!QAName
rst.FindFirst Me!QAName


If rst.NoMatch Then
rst.AddNew
rst.MoveLast
rst.Edit
rst!employeename = Me!QAName
rst.Update
rst.close
End If

 

[Marshall Barton]

I have used this code many times and have no idea why it
is not working for me now. I receive a run-time error
3077 syntax error (missing operator) on the rst.findfirst
line. I have a field on a form that I want the users to
be able to update if new information is needed. The
field is tied to a table.

Dim dbs As Database
Dim rst As Recordset
Dim strName As String


Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("QualityAnalysts",
dbOpenDynaset)

'strName = Me!QAName
rst.FindFirst Me!QAName


If rst.NoMatch Then
rst.AddNew
rst.MoveLast
rst.Edit
rst!employeename = Me!QAName
rst.Update
rst.close
End If

You need to tell FindFirst which field to use in the
comparison. For a numeric type field use:

rst.FindFirst "somefield = " & Me!QAName

and a text type field can be done this way:

rst.FindFirst "somefield = """ & Me!QAName & """"
or
rst.FindFirst "somefield = '" & Me!QAName & "'"

 

[Tim Ferguson]

Set rst = dbs.OpenRecordset("QualityAnalysts",
dbOpenDynaset)

'strName = Me!QAName
rst.FindFirst Me!QAName

As Marsh says, the FindFirst syntax is wrong. I'd try to streamline the
whole thing though:

' see if it's already there:
If DCount("*", QualityAnalysts", _
"EmployeeName = """ & Me!QAName & """") = 0 Then

' no matching records: make a new one
strSQL = "INSERT INTO QualityAnalysts(EmployeeName) " & _
"VALUES (""" & Me!QAName & """)"

' insert the record
dbs.Execute strSQL, dbFailOnError


End If

As a by-the-way, there are serious problems with the logic in the remaining
lines, and I don't believe they would ever work as intended.

Hope that helps


Tim F