Really Need your help

[SharedKnowledge]

Hi,

I've created a MS Access (2007) database storing details of a charities clients. Is there a validation rule/piece of code for the following:

FIELDS: Date, Time, Duration, Room

CODE FOR: Not being able to select a room if it is on the same date and time/duration?

I'd really appreciate it if you could get back to me.

Many thanks,

Problem shared is a problem halved

[sid78669]

In Design Menu, Click on Indexes.
Enter Your data there as follow
Index_Name;Field_Name;Sort_Order;Primary;Unique;IgnoreNulls
Date_Index;Date;Ascending;No;Yes;No
Time_Index;Time;Ascending;No;Yes;No
Duration_Index;Duration;Ascending;No;Yes;No
Room_Index;Room;Ascending;No;Yes;No

Try this and let me know if it works.

[ChrisPadgham]

Private Sub cmdBook_Click()
On Error GoTo Err_cmdBook_Click

Dim rsRoomBookings As Recordset
Set rsRoomBookings = CurrentDb.OpenRecordset("SELECT * FROM RoomBookings WHERE BookingDate='" & Format$(Me.DesiredDate, "dd-mmm-yyyy") & "' AND BookingTime='" & Format$(Me.DesiredTime, "hh:nn") & "' AND RoomName='" & Me.cboDesiredRoom & "'")
If rsRoomBookings.RecordCount > 0 Then
    MsgBox "I am sorry that room is booked at that time already"
Else
    CurrentDb.Execute "INSERT INTO RoomBookings (BookingDate, BookingTime, RoomName, BookedTo) VALUES('" & Format$(Me.DesiredDate, "dd-mmm-yyyy") & "', '" & Format$(Me.DesiredTime, "hh:nn") & "', '" & Me.cboDesiredRoom & "', '" & Me.PersonName & "');"
    MsgBox "Booked"

Exit_cmdBook_Click:
    Exit Sub

Err_cmdBook_Click:
    MsgBox Err.Description
    Resume Exit_cmdBook_Click
    
End Sub

[SharedKnowledge]

Thank you both for getting back to me on this. Unfortunately, I won't have access to the database until Tuesday/Wednesday of next week to try your suggestions.

ChrisPadgham, how do I add this code into the database? What do I click after Visual Basic?

Thanks again for your time: it's much appreciated

SharedKnowledge

[ChrisPadgham]

You need to add a button with a title of something like "Book Room". the code goes in the click event

[SharedKnowledge]

Thanks again for getting back to me on this ChrisPadgham - I'll hopefully have time to volunteer within the next few days, will let you know if it works.

PS, sid78669 - unfortunately changing the indexes didn't work, but thanks for your time and suggestion!

Take care,

[SharedKnowledge]

Hi guys,

Unfortunately these suggestions didn't work.

Thanks for your time though!