Dim type error

[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 MyDB as Database - line of code. The code for the before update on the form is below. Anyone have any thoughts on why this error on the dim line is? Or any better suggestion to enforce this contraint? 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

 

[jrbarnett]

My guess is that you are using Access 2000 or XP and haven't added a reference to the DAO object library. Easy way go to tools -> references, scroll down and put a tick in the Microsoft DAO 3.6 Object Library.

Alternatively, you could just replace the entire routine with:

If Not Isnull(DLookup ("StaffID", "Appointments", StaffID='StaffID'AND Date='Date' AND Time='Time'&quot) Then
Cancel = True
Msgbox "Time and Date Unavailable"
End If

- you will need to replace the where string in DLookup of course but that is all.

John

 

[Tekime]

I just replied to the same post in "Access: Other Topics", asking the error message. I asked because I had the exact same problem, remedied via jrbarnett's first suggestion.

I'm not sure you want to get in the habit of cross-forum posting, it makes the solution to the problem harder to find. For the sake of all of us we should probably keep our questions limited to one forum.

http://www.fury-tech.com

 

[Tekime]

Copied from :

After I added the DAO object in the references and re-ran the module I am now getting a "Data Type Mismatch in criteria expression" and it stops at the line: Set MySet = MyDB.OpenRecordset(MyQuery)


I'm not totally sure, but this probably has to do with this line:

MyQuery = "SELECT StaffID, Date, Time FROM Appointments WHERE StaffID='StaffID'AND Date='Date' AND Time='Time'"

It's probably evaluating the literal strings in the query... perhaps something like this would work:

MyQuery = 
"SELECT StaffID, Date, Time " & _
"FROM Appointments " & _
"WHERE StaffID= '" & StaffID & "' " & _
"AND Date= '" & Date & "' " & _
"AND Time= '" & Time & "' "

I'm still an Access n00b though, I'm sure one of the resident gurus will be by to school us ;-)

[URL unfurl="true"]www.fury-tech.com

 

[CoreyWayne]

Thank you. And sorry about cross-posting. I guess I got a little excited about getting help.

 

[psemianonymous]

Try explicitly "Dimming" your recordset as "DAO.Recordset" - older versions of Access use DAO as the default recordset whereas newer versions use ADO as default. Thus with your newer version you're trying to do:

Set (ADODB.Recordset) = (DAO.Recordset)

and it's messing up.


Also, check your SQL statement very closely - I don't think you can run that one out-of-the-box, especially since "Date" and "Time" are functions. Just take note.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm