Access 2002 VBA Help

[CoreyWayne]

I have to enforce an enterprise constraint where an instructor can not have two appointments at the same time. The code that I got out of a newer Database Management book does not like the - Dim MtDB as Database - line of code. The cose for the before upate on the form is below. Anyone have any thoughts on why this error on the dim line is? Thank you in advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim MyDB As Database
Dim MySet As Recordset
Dim MyQuery As String

'Set up query to select all matching records
MyQuery = "SELECT StaffID, Date, Time FROM Appointments WHERE StaffID='StaffID'AND Date='Date' AND Time='Time'"

'Open Database and run query'
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset(MyQuery)

If (MySet.RecordCount > 1) Then
MsgBox "Time and date unavailable. Please Re-schedule"
Me.Undo
End If

MySet.Close
MyDB.Close

End Sub


Thank you in advance!

 

[cheerio]

Microsoft have developed two ways of working with databases.

For Access 97 the only method was called DAO and your specimen code is DAO code.

From Access 2000 onwards there is an alternative method called ADO which is the default.

So to make your code work you have to grant it access to the DAO library of commands. In a VBA module go to tools, references and tick a DAO reference. You can change the order so it should be moved above ADO to make it the default for your project.

Finally some objects such as a recordset exist in both ADO and DAO so it is a good idea to make the code explicit. For example you have:

Dim MySet As Recordset

change this to

Dim MySet As DAO.Recordset

Ken