Using the FindFirst Method with DAO

[bdoiron]

I am writing an event procedure to check to see if a particular Project number exist in a recordset. I am trying to use the findfirst method and are having some problems. Here is my code.

Private Sub Command3_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset, ProjectNo As String, SqlStr As String, StrProjectNo As String


Set db = CurrentDb()
Set rs = db.OpenRecordset("tblTrackingSheetFrm", dbOpenTable)
StrProjectNo = Me![ProjectNumber]
rs.FindFirst StrProjectNo

If rs.NoMatch Then
Forms![frmProjectCriteria].Visible = False
DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery "(1)qryDeletetblTrackingSheetFrm"
DoCmd.OpenQuery "(1A)qryDeletetblTrackingSheetTMP"
DoCmd.OpenQuery "(2)qryAppendProjectTasks"
DoCmd.OpenQuery "(3)qryMaketblLaborActuals"
DoCmd.OpenQuery "(3A)qryUpdatetblTrackingSheetTMP"
DoCmd.OpenQuery "(4)qryDeletetblMaterialActualsTMP"
DoCmd.OpenQuery "(5)qryAppendEquipment"
DoCmd.OpenQuery "(6)qryAppendInventory"
DoCmd.OpenQuery "(7)qryAppendPayables"
DoCmd.OpenQuery "(8)qryAppendPurchaseOrder"
DoCmd.OpenQuery "(9)qryUpdateMaterialActuals"
DoCmd.OpenQuery "(A)qryAppendtblTrackingSheetFrm"

DoCmd.SetWarnings WarningsOn
DoCmd.OpenForm "frmTrackingSheet", acNormal

Else

MsgBox " Project worksheet already opened by another user."

rs.Close
End If
End Sub

What this does is check to see if another user has a project open and if so doesnt allow that user to access that project. I am getting the following error when I execute the procedure on the findfirst Code line.

Runtime error 3251 Operation is not supported by this object type.
Can someone take a look and see what I am doing wrong.
Any help is greatly appreciated.

[Slick Willy]

Insert a msg box like below, run your code and see what value it shows you if any.


StrProjectNo = Me![ProjectNumber]
MsgBox StrProjectNo
rs.FindFirst StrProjectNo


Is what the msgbox shows you what you expected StrProjectNo to equal? or does it give you a different error like invalid use of null?

[asc11]

There are a couple problems with your code that I can see. First, the FindFirst method doesn't work on a table-type recordset. It will only work on a dynaset-type or snapshot-type recordset. To find a record in a table-type recordset you need to use the Seek method.

Second of all, if you change the type to dbOpenDynaset, your code will give you an "Invalid argument" error on the FindFirst method. The reason for this is because the argument for the FindFirst method must contain a field name, comparison operator, and a value. You have only given it a value to search without telling it what field to search in. Example:

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblTrackingSheetFrm", dbOpenDynaset)
StrProjectNo = Me![ProjectNumber]
rs.FindFirst "ProjectNumber = " & StrProjectNo

'If the data type of the field ProjectNumber is Text then use:
'rs.FindFirst "ProjectNumber = '" & StrProjectNo & "'"

Let us know if this helps.