Problem with FindFirst code

[CyndeQ]

Hello everyone -- Need some help. I am setting up an Access Database with employee information, here's the problem...I need to search for an EmployeeID and if it does not exist I need to add it, if it does exist I need certain fields on the form to autofill. It keeps crashing on the FindFirst statement...Here is my code

Private Sub ID_AfterUpdate()
  
 Dim strAdd As String
 Dim db As DAO.Database
 Dim rst As DAO.Recordset
  
 
Set db = CurrentDb
 Set rst = db.OpenRecordset("Employee Table";)
  
 rst.FindFirst "[ID] = " & Me.ID.Value 
 
 If rst.NoMatch Then
  
 'Add new employee information to database

 rst.AddNew
 rst![ID] = Me.ID.Value
 rst![SocSecNo] = Me.txtSocSecNo.Value
 rst![Craft] = Me.txtCraft.Value
 rst![JobNo] = Me.txtJobNo.Value
 rst![LastName] = Me.txtLastName.Value
 rst![Suffix] = Me.txtSuffix.Value
 rst![FirstName] = Me.txtFirstName.Value
 rst![MiddleInitial] = Me.txtMiddleInitial.Value
 rst![TWICCardNo] = Me.txtTWICCardNo.Value
 rst![TWICCardExp] = Me.dteTWICCardExp.Value
 rst![HireDate] = Me.HireDate.Value
 rst![TermDate] = Me.TermDate.Value
 rst.Update
 Else
  
 'Autofill form based on ID number entered

 Me.txtSocSecNo.Value = rst![SocSecNo]
 Me.txtCraft.Value = rst![Craft]
 Me.txtJobNo.Value = rst![JobNo]
 Me.txtLastName.Value = rst![LastName]
 Me.txtSuffix.Value = rst![Suffix]
 Me.txtFirstName.Value = rst![FirstName]
 Me.txtMiddleInitial.Value = rst![MiddleInitial]
 Me.txtTWICCardNo.Value = rst![TWICCardNo]
 Me.dteTWICCardExp.Value = rst![TWICCardExp]
 End If
  

 End Sub

Any help I can get will be very much appreciated...Thanks!

CyndeQ

 

[PHV]

It keeps crashing on the FindFirst statement
Which error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CyndeQ]

Runtime error 3251 -- Operation not supported fpr this type of object.

Tested with a "known" ID and got the same error but the code value is correct ???

 

[PHV]

Why not simply replace this:
Set rst = db.OpenRecordset("Employee Table"
rst.FindFirst "[ID] = " & Me.ID.Value
If rst.NoMatch Then

with this ?
Set rst = db.OpenRecordset("SELECT * FROM [Employee Table] WHERE ID=" & Me!ID, dbOpenSnapshot)
If rst.BOF And rst.EOF Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CyndeQ]

Now I'm getting data type mismatch...everything is text...

 

[PHV]

Set rst = db.OpenRecordset("SELECT * FROM [Employee Table] WHERE ID=[!]'[/!]" & Me!ID[!] & "'"[/!], dbOpenSnapshot)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Although PHV's approach is the better solution, I think the issue is with this line

Set rst = db.OpenRecordset("Employee Table";)

The message suggests that it is returning a recordset, but not a recordset that supports a findfirst (snapshot or dynaset). I know a dynaset does.

With the semicolon I am suprised it even compiled.
Set rst = db.openrecordset("Employee_Table",dbopendynaset)

I never use spaces in any names, it is just a poor practice.