Recordset Findfirst Problem

[ColPat]

Can someone please help.
I have a table tbWOEmployee, record number field "WOEmpRec"
strDeleteRecNo is an integer field holding a record number in tbWOEmployee

Code crashes when it gets to the line
rstWOEmployee.FindFirst "[WOEmpRec] = " & strDeleteRecNo & ""

Message is "Operation is Not Supported For This Type of Object"


Dim tbWOEmployee As DAO.Database
Dim rstWOEmployee As DAO.Recordset

Set tbWOEmployee = CurrentDb
Set rstWOEmployee = tbWOEmployee.OpenRecordset("tbWOEmployee")

If strEditLab = "EditLab" Then

rstWOEmployee.FindFirst "[WOEmpRec] = " & strDeleteRecNo & ""
If rstWOEmployee.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rstWOEmployee.Bookmark
rstWOEmployee("WOEmpDate").Value = Me.cbDate
rstWOEmployee.Update


Me.Refresh
End If

[SoftwareMatters]

Try taking the quotes off the end of the line that crashes like this:

rstWOEmployee.FindFirst "[WOEmpRec] = " & strDeleteRecNo

[Rawb]

When you don't specify the recordset type (dbOpenDynaset, dbOpenSnapshot, dbOpenTable, etc), Access assumes you want to use the dbOpenTable type for local tables (if you're using Linked Tables in a Split Database, Access defaults to dbOpenDynaset).

Therefore, the following two code snippits function the same:

Set rstWOEmployee = tbWOEmployee.OpenRecordset("tbWOEmployee")

Set rstWOEmployee = tbWOEmployee.OpenRecordset("tbWOEmployee", dbOpenTable)

Since you're using the Table type, you can't use FindFirst. Try either changing your recordset to a Dynaset (by adding dbOpenDynaset to your OpenRecordset line) or use the .Seek method instead of .FindFirst.

[ColPat]

Thank you very much Rawb. I added the dbOpenDynaset statement to the OpenRecordSet line.
Now I am getting down to the
Me.Bookmark = rstWOEmployee.Bookmark
And then getting the error "You entered an expression that has an invalid reference to the property Bookmark"

The full code of the Accept option is below. Am still only a beginner at this and would really appreciate if there is a better way of coding.
The second part of the If, Else, Endif works ok and saves the record. The problem is in updating data in an existing record.
Form is not bound to the table as it will have similar add & edit functions for different tables.

Private Sub coAcceptData_Click()
On Error GoTo Err_coAcceptData_Click

Dim EmpNumber As String
Dim EmpFname As String
Dim EmpLname As String
Dim EmpPosition As String
Dim EmpRate As String
Dim EmpHrs As Integer
Dim EmpMins As Integer
Dim EmpDate As Date
Dim tbWOEmployee As DAO.Database
Dim rstWOEmployee As DAO.Recordset

Set tbWOEmployee = CurrentDb
Set rstWOEmployee = tbWOEmployee.OpenRecordset("tbWOEmployee", dbOpenDynaset)

If strEditLab = "EditLab" Then

rstWOEmployee.FindFirst "[WOEmpRec] = " & strDeleteRecNo & ""
If rstWOEmployee.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rstWOEmployee.Bookmark crashes here
rstWOEmployee("WOEmpDate").Value = Me.cbDate
rstWOEmployee.Update
Me.Refresh
End If

Me.txTradeName.Visible = False
Me.cbTradeName.Visible = True
Me.Label21.Visible = False
Me.cbTradeName_Label.Visible = True
Me.cbDate = ""
Me.txHours = ""
Me.txMins = ""
strEditLab = ""
Me.Refresh
Else
EmpNumber = Me.cbTradeName.Column(0)
EmpFname = Me.cbTradeName.Column(1)
EmpLname = Me.cbTradeName.Column(2)
EmpPosition = Me.cbTradeName.Column(3)
EmpRate = Me.cbTradeName.Column(4)
EmpDate = Me.cbDate
EmpHrs = Me.txHours
EmpMins = Me.txMins
rstWOEmployee.AddNew
rstWOEmployee("WONumber").Value = strWOLabMat
rstWOEmployee("EmployeeID").Value = EmpNumber
rstWOEmployee("FirstName").Value = EmpFname
rstWOEmployee("LastName").Value = EmpLname
rstWOEmployee("Position").Value = EmpPosition
rstWOEmployee("Rate").Value = EmpRate
rstWOEmployee("WOEmpDate").Value = EmpDate
rstWOEmployee("WOHours").Value = EmpHrs
rstWOEmployee("WOMinutes").Value = EmpMins
rstWOEmployee.Update
Me.cbTradeName = ""
Me.cbDate = ""
Me.txHours = ""
Me.txMins = ""

Me.Refresh
Me.cbTradeName.SetFocus
End If

Exit_coAcceptData_Click:
Set rstWOEmployee = Nothing
Set db = Nothing
Exit Sub

Err_coAcceptData_Click:
MsgBox Err.Description
Resume Exit_coAcceptData_Click
Set

[Rawb]

Probably a better way to handle database edits (at least in VBA Code) is using a With code block like this (I've highlighted the changes):

Private Sub coAcceptData_Click()
  On Error GoTo Err_coAcceptData_Click

  Dim EmpNumber, EmpFname, EmpLname, EmpPosition, EmpRate As String
  Dim EmpHrs, EmpMins As Integer
  Dim EmpDate As Date

  Dim tbWOEmployee As DAO.Database
  Dim rstWOEmployee As DAO.Recordset

  Set tbWOEmployee = CurrentDb
  Set rstWOEmployee = tbWOEmployee.OpenRecordset("tbWOEmployee", dbOpenDynaset)

  If strEditLab = "EditLab" Then
    rstWOEmployee.FindFirst "[WOEmpRec] =  " & strDeleteRecNo & ""

    If rstWOEmployee.NoMatch Then
      MsgBox "No entry found.", vbInformation
    Else
      With rstWOEmployee
        .Edit ' Can also have .Delete and .AddNew here!
        !WOEmpDate = Me.cbDate ' Database fields are always prefixed with a "bang" (!) character.
        .Update ' Always have this as the last statement in a With block
        .Bookmark = .LastModified
      End With

      Me.Refresh
    End If

    Me.txTradeName.Visible = False
    Me.cbTradeName.Visible = True
    Me.Label21.Visible = False
    Me.cbTradeName_Label.Visible = True
    Me.cbDate = ""
    Me.txHours = ""
    Me.txMins = ""
    strEditLab = ""
    Me.Refresh
  Else
    EmpNumber = Me.cbTradeName.Column(0)
    EmpFname = Me.cbTradeName.Column(1)
    EmpLname = Me.cbTradeName.Column(2)
    EmpPosition = Me.cbTradeName.Column(3)
    EmpRate = Me.cbTradeName.Column(4)
    EmpDate = Me.cbDate
    EmpHrs = Me.txHours
    EmpMins = Me.txMins

    With rstWOEmployee
      .AddNew
      !WONumber = strWOLabMat
      !EmployeeID = EmpNumber
      !FirstName = EmpFname
      !LastName = EmpLname
      !Position = EmpPosition
      !Rate = EmpRate
      !WOEmpDate = EmpDate
      !WOHours = EmpHrs
      !WOMinutes = EmpMins
      .Update
      .Bookmark = .LastModified
    End With

    Me.Refresh
    Me.cbTradeName.SetFocus
  End If

Exit_coAcceptData_Click:
  rstWOEmployee.Close

  Set rstWOEmployee = Nothing
  Set tbWOEmployee = Nothing

  Exit Sub

Err_coAcceptData_Click:
  MsgBox Err.Description
  Resume Exit_coAcceptData_Click

I noticed you use a couple of variables in your function that aren't set anywhere: strEditLab and strWOLabMat. I'm hoping that these variables are just assigned outside the function, but I thought I'd better mention it just in case

[ColPat]

Many, many thanks Rawb.
I'll try it tonight when I get home. The 2 variables are in a module as public as they get used across a couple of forms.

Would using strSQL code reduce the number of lines required?
On the same form I have a delete button to delete a record using

Dim strSQLDelete As String

strSQLDelete = "Delete from tbWOEmployee WHERE (WOEmpRec = " & strDeleteRecNo & " );"

CurrentDb.Execute strSQLDelete, dbFailOnError

and it just seems to be neat and concise coding compared to recordsets. Tried a few ways wit strSQL for the update and add but couldn't get a workable solution.
Again, many thanks for the help - very much appreciated

[ColPat]

Fantastic, thanks Rawb. Works an absolute treat. Very much appreciated