Run time error 91

[Guest]

Hi, I receive a run time error 91 (object variable or with block not set) on
the openrecordset line of the following code, can anyone correct it for me
please? I am trying to step through a recordset to ensure the user is not
trying to book a duplicate activity. It is called from a form via an onclick
event of a yes/no field.

' Call function to check Record does not exist for Specialist on chosen
date
If UniqueRec([Next Audit], [Specialist]) = False Then
Me![SelectAudit] = False
Msg = "Auditor has existing Activity on that date, please choose
another Date"
MsgBox Msg, vbCritical, "Duplicate Data"
End If

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset

rs.OpenRecordset ("Select * from Itinerary")
rs.FindFirst "[ReviewDate] = #" & CheckDate & "#" And "[Specialist] =
SpecID"
If rs.NoMatch Then
UniqueRec = True
Else
UniqueRec = False
End If

End Function

 

[Guest]

Dim db As DAO.Database
Dim rs As DAO.Recordset
set db = currentdb
set rs = db.OpenRecordset ("Select * from Itinerary")
rs.FindFirst "[ReviewDate] = #" & CheckDate & "#" And "[Specialist] =

 

[Tim Ferguson]

Be kind to your network:

Function UniqueRec(CheckDate As Date, SpecID As Integer) As Boolean

Dim jetWhere as String

jetWhere = "ReviewDate = " & Format(CheckDate,"\#\yyyy\-mm\-dd\#") & _
" AND Specialist = " & Format(SpecID,"0")

' this would work:
UniqueRec = (DCount("*", "Itinerary", jetWhere) =0)

' so would this:
' UniqueRec = Not IsNull(DLookup("CheckDate", "Itinerary", jetWhere))

End Function


Opening a whole table and doing a FindFirst is an extremely inefficient
way to go about anything...


Hope that helps


Tim F

 

[Guest]

Great thanks guys, worked a treat!

Sue