Checking and then saving to a table....?

[Guest]

Hi,

I'm very new to access and I am trying to create a very simple room booking
database that lets the user, wait for it...... book a room! At the moment it
is very basic and I only have three tables:
tblUsers (fields include: UserID, Password)
tblRooms (fields: RoomID, RoomDesc, RoomCapacity)
tblSchedule (fields: ScheduleID, UserID, RoomID, Date, StartTime, EndTime).

I also have two forms, (1) frmLogin and (2) frmBookings - i have mastered
the login screen so that if the user enters the correct user ID and password,
then it will open the frmBookings, if not, error msg displayed.
I will get to my question shortly, promise!
So on the Bookings form, I have a list box (lstRoomReq) that shows the
RoomDesc and RoomCapacity, underneath that I have three further boxes for the
date required (txtDate), start (lstStart) and end (lstEnd) times. I then have
a command button (cmdOK), which is where I am starting to struggle.

Basically, what I need is for when the User selects the room they require
and the date, start & end times, when they click on cmdOK, it searchs to see
if that room is booked out for that date and time, in which case an error
msgbox will be displayed. If it is free, then I want the info to be entered
in the tblSchedule table and for a msg to be displayed confirming the booking.

I'm sorry i've gone so long winded but I thought the more details I gave,
the more likely it is someone will be able to help.

Here's hoping you can, thanks for being patient and getting this far down
the post!!
Cheers
Lindsey

 

[Steve Schapel]

Lindsey,

Thank you for the very clear explanation of your project.
Here is some "air code" that might illustrate one possible approach...

Private Sub cmdOK_Click()
Dim IsItBooked As Boolean
IsItBooked = DCount("*","tblSchedule","RoomID=" & Me.lstRoomReq & _
" And [Date]=#" & Me.txtDate & "#" & _
" And ((StartTime>#" & Me.lstStart &
"#" & _
" And StartTime<#" & Me.lstEnd & "#)" & _
" Or (StartTime<#" & Me.lstStart & "#" & _
" And EndTime>#" & Me.lstStart &
"#))") > 0
If IsItBooked Then
If MsgBox("Sorry, this room is unavailable. Do you want to try
again?", vbYesNo) = vbNo Then
Me.Undo
End If
Else
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Booking confirmed"
End If

End Sub

 

[Guest]

Hi Steve, thanks for your reply

Unfortunately, i've used the code below and when i click on OK - it comes up
with the following error:

Run time error '3075'
Syntax error (missing operator) in query expression
'RoomID=BB001And[Date]=#20/03/2005#And
((StartTime>#08:00#And StartTime<#10:00#)Or
(StartTime<#08:00#And EndTime>#08:00#))'

As i said, i'm very new to access and my debugging skills are not what they
should be - have you any ideas?

thanks again
Linds

Lindsey,

Thank you for the very clear explanation of your project.
Here is some "air code" that might illustrate one possible approach...

Private Sub cmdOK_Click()
Dim IsItBooked As Boolean
IsItBooked = DCount("*","tblSchedule","RoomID=" & Me.lstRoomReq & _
" And [Date]=#" & Me.txtDate & "#" & _
" And ((StartTime>#" & Me.lstStart &
"#" & _
" And StartTime<#" & Me.lstEnd & "#)" & _
" Or (StartTime<#" & Me.lstStart & "#" & _
" And EndTime>#" & Me.lstStart &
"#))") > 0
If IsItBooked Then
If MsgBox("Sorry, this room is unavailable. Do you want to try
again?", vbYesNo) = vbNo Then
Me.Undo
End If
Else
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Booking confirmed"
End If

End Sub

--
Steve Schapel, Microsoft Access MVP

Hi,

I'm very new to access and I am trying to create a very simple room booking
database that lets the user, wait for it...... book a room! At the moment it
is very basic and I only have three tables:
tblUsers (fields include: UserID, Password)
tblRooms (fields: RoomID, RoomDesc, RoomCapacity)
tblSchedule (fields: ScheduleID, UserID, RoomID, Date, StartTime, EndTime).

I also have two forms, (1) frmLogin and (2) frmBookings - i have mastered
the login screen so that if the user enters the correct user ID and password,
then it will open the frmBookings, if not, error msg displayed.
I will get to my question shortly, promise!
So on the Bookings form, I have a list box (lstRoomReq) that shows the
RoomDesc and RoomCapacity, underneath that I have three further boxes for the
date required (txtDate), start (lstStart) and end (lstEnd) times. I then have
a command button (cmdOK), which is where I am starting to struggle.

Basically, what I need is for when the User selects the room they require
and the date, start & end times, when they click on cmdOK, it searchs to see
if that room is booked out for that date and time, in which case an error
msgbox will be displayed. If it is free, then I want the info to be entered
in the tblSchedule table and for a msg to be displayed confirming the booking.

I'm sorry i've gone so long winded but I thought the more details I gave,
the more likely it is someone will be able to help.

Here's hoping you can, thanks for being patient and getting this far down
the post!!
Cheers
Lindsey

 

[Steve Schapel]

Lindsey,

Looks like you've missed out a lot of spaces in the expression, in
particular on either side of the word And and Or. Also, I had assumed
RoomID was numerical. Now I see it is text, that part of the code
should be like this...
IsItBooked = DCount("*","tblSchedule","RoomID='" & Me.lstRoomReq &
"'" & _
" And [Date]=#" & Me.txtDate & "#" & _ ....

 

[Guest]

Steve, i've done it now - you're a star!!

Thanks again for all your help

Lindsey

Lindsey,

Looks like you've missed out a lot of spaces in the expression, in
particular on either side of the word And and Or. Also, I had assumed
RoomID was numerical. Now I see it is text, that part of the code
should be like this...
IsItBooked = DCount("*","tblSchedule","RoomID='" & Me.lstRoomReq &
"'" & _
" And [Date]=#" & Me.txtDate & "#" & _ ....

--
Steve Schapel, Microsoft Access MVP

Hi Steve, thanks for your reply

Unfortunately, i've used the code below and when i click on OK - it comes up
with the following error:

Run time error '3075'
Syntax error (missing operator) in query expression
'RoomID=BB001And[Date]=#20/03/2005#And
((StartTime>#08:00#And StartTime<#10:00#)Or
(StartTime<#08:00#And EndTime>#08:00#))'

As i said, i'm very new to access and my debugging skills are not what they
should be - have you any ideas?

thanks again
Linds

 

[Steve Schapel]

Lindsey,

Great. Now that we've got that cleared up, I should mention something
else... The word 'Date' is a Reserved Word (i.e. has a special meaning)
in Access, and as such it is best not to use it as the name of a field
or control.