Hello, I have designed a database with a FacilityMaster, Employee, and beds information. I am designing an application within access and I'm trying to validate the new records that will be entered into those tables. In the beds table I have information about the beds available per facility and the occupancy dates. I am trying to make sure that when a new record is entered, the user does not enter the dates that are already in use or not to enter the number of beds that are already used. Any help would be appreciated. I was told to loop through a record set and add those constraints, but I'm a beginner with VBA.
VBA Access Code Help
- Thread starter cocho
- Start date
- Local time
- Today, 13:23
- Joined
- Feb 28, 2001
- Messages
- 30,240
This can be ambitious, but the concept isn't that difficult. As a novice, you have a lot of topics to learn about. I'll try to remember to highlight topics you can try when you search this site. The "Search" option is in the solid bar just under the top header stuff that tells you "Welcome, {username}" and shows you the page you are on. That search function will take you very far.
You should also search the forum for the keywords "Booking" and "Reservation" - both of which should lead you to many questions/threads on this topic.
OK, let's look at it: You have a beds table. The other tables you named hold staff member or employee data, or so it seems. Your concern here is booking the beds, not booking the members. So let's focus on that concept.
Typically, what is done is to have a separate booking table that is sparse. That means that if a bed isn't booked, you don't have an entry for a booking for that bed. You do NOT have a day-by-day record that shows "Booked" on one day and "Not Booked" on another day. That is wasteful of space and is an example of what we call "flat file" thinking. You would use the calendar-like table in Excel, perhaps. Not in Access.
In the Beds table, you should have a bed ID number, usually an autonumber or other LONG integer type, as the prime key (PK). Every bed has a unique ID number so you can tell which one is booked and which one isn't. If there is information about bed size, smoking or non-smoking, handicap-accessible, or other special notes, that usually goes in the Beds table as secondary descriptive fields.
To book a bed you need a Bookings table. You will need this table to contain the bed ID as a foreign key (FK) because the Bookings table will link each booking record back to a bed. That is, you will have a one-bed-to-many-bookings relationship, which can be searched as a one-to-many relationship. A bed is booked on a given date if it has a Bookings table entry for that BedID for that date.
The bookings table might contain these items:
BedID, LONG integer (as FK to the bed table)
BkgStart, date/time field for start of booking
BkgEnd, date/time field for end of booking
BkdBy, LONG integer (as FK to the list of your employees who can make bookings)
BkdTo, LONG integer (as FK to a presumed list of customers).
Other stuff you might need regarding this booking can also go in the table, such as but not limited to the booking rate used for this customer (presuming the possibility of promotional discounts/rates). It's your problem, I'll let you figure out what else you need.
You will need a query or two because you will want to be able to search for beds that are NOT booked for a given proposed booking. You do this by looking for all beds for which the bookings meet three criteria. This next part is tricky but not that difficult. Make a query that uses the BETWEEN ... AND ... operator.
Your goal will be to make a list of beds that are NOT booked so here is one way to do that. You will have a proposed start and end date somewhere on your booking form, so I made up a couple of names starting with "Prp" meaning "Proposed."
This is a code FRAGMENT to show you how to build the query that you might use to drive the .RowSource of a combo box that you would use to select a bed from the list of what wasn't booked. That "NOT IN" construct is followed by a sub-query that tells you which beds ARE booked, but the "NOT IN" makes the query only show you what was NOT in that list. You can certainly embellish this to include bed information in the combo box, I'm just showing you how to approach the "is it booked yet" question.
Another approach would be to split out the sub-query in case you wanted to make a list of what WAS booked on a given day, perhaps for a report of some kind. In that case, you would then simply have that filtration query do it's select "WHERE BedID NOT IN (SELECT BedID FROM BookedBedsQuery)...".
I know this is a lot to think about, but it isn't that hard if you just take your time and look at what the parts do. I STRONGLY urge you to use the search feature of the forum, too. You'll see a lot more.
Before you ask the obvious question about the query segment comparing the dates:
The idea is that a bed is booked if:
(a) Your proposed start date is between the bed's actual start and end dates for a booking.
(b) Your proposed end date is between the bed's actual start and end dates for a booking.
(c) Your proposed start and end dates fully contain the actual booking start and end dates.
There is a fourth option, where the proposed start and end dates are fully contained by an extant booking, but cases (a) or (b) will catch that fourth case.
You should also search the forum for the keywords "Booking" and "Reservation" - both of which should lead you to many questions/threads on this topic.
OK, let's look at it: You have a beds table. The other tables you named hold staff member or employee data, or so it seems. Your concern here is booking the beds, not booking the members. So let's focus on that concept.
Typically, what is done is to have a separate booking table that is sparse. That means that if a bed isn't booked, you don't have an entry for a booking for that bed. You do NOT have a day-by-day record that shows "Booked" on one day and "Not Booked" on another day. That is wasteful of space and is an example of what we call "flat file" thinking. You would use the calendar-like table in Excel, perhaps. Not in Access.
In the Beds table, you should have a bed ID number, usually an autonumber or other LONG integer type, as the prime key (PK). Every bed has a unique ID number so you can tell which one is booked and which one isn't. If there is information about bed size, smoking or non-smoking, handicap-accessible, or other special notes, that usually goes in the Beds table as secondary descriptive fields.
To book a bed you need a Bookings table. You will need this table to contain the bed ID as a foreign key (FK) because the Bookings table will link each booking record back to a bed. That is, you will have a one-bed-to-many-bookings relationship, which can be searched as a one-to-many relationship. A bed is booked on a given date if it has a Bookings table entry for that BedID for that date.
The bookings table might contain these items:
BedID, LONG integer (as FK to the bed table)
BkgStart, date/time field for start of booking
BkgEnd, date/time field for end of booking
BkdBy, LONG integer (as FK to the list of your employees who can make bookings)
BkdTo, LONG integer (as FK to a presumed list of customers).
Other stuff you might need regarding this booking can also go in the table, such as but not limited to the booking rate used for this customer (presuming the possibility of promotional discounts/rates). It's your problem, I'll let you figure out what else you need.
You will need a query or two because you will want to be able to search for beds that are NOT booked for a given proposed booking. You do this by looking for all beds for which the bookings meet three criteria. This next part is tricky but not that difficult. Make a query that uses the BETWEEN ... AND ... operator.
Your goal will be to make a list of beds that are NOT booked so here is one way to do that. You will have a proposed start and end date somewhere on your booking form, so I made up a couple of names starting with "Prp" meaning "Proposed."
Code:
Dim PrpStart As String
Dim PrpEnd As String
Dim SelQry As String
...
PrpStart = "#" & CStr([date-start-control]) & "#"
PrpEnd = "#" & Cstr([date-end-control]) & "#"
...
SelQry = "SELECT BedID FROM Beds LEFT JOIN Bookings " & _
"ON Beds.BedID = Bookings.BedID " & _
"WHERE BedID NOT IN ( " & _
"SELECT Bookings.BedID FROM Bookings WHERE " &
"(" & PropStart & " BETWEEN Bookings.BkgStart AND Bookings.BkgEnd ) OR " & _
"(" & PropEnd & " BETWEEN Bookings.BkgStart AND Bookings.BkgEnd ) OR " & _
"((" & PropStart & " < Bookings.BkgStart) AND " & _
"(" & PropEnd & " > Bookings.BkgEnd & ") );"This is a code FRAGMENT to show you how to build the query that you might use to drive the .RowSource of a combo box that you would use to select a bed from the list of what wasn't booked. That "NOT IN" construct is followed by a sub-query that tells you which beds ARE booked, but the "NOT IN" makes the query only show you what was NOT in that list. You can certainly embellish this to include bed information in the combo box, I'm just showing you how to approach the "is it booked yet" question.
Another approach would be to split out the sub-query in case you wanted to make a list of what WAS booked on a given day, perhaps for a report of some kind. In that case, you would then simply have that filtration query do it's select "WHERE BedID NOT IN (SELECT BedID FROM BookedBedsQuery)...".
I know this is a lot to think about, but it isn't that hard if you just take your time and look at what the parts do. I STRONGLY urge you to use the search feature of the forum, too. You'll see a lot more.
Before you ask the obvious question about the query segment comparing the dates:
The idea is that a bed is booked if:
(a) Your proposed start date is between the bed's actual start and end dates for a booking.
(b) Your proposed end date is between the bed's actual start and end dates for a booking.
(c) Your proposed start and end dates fully contain the actual booking start and end dates.
There is a fourth option, where the proposed start and end dates are fully contained by an extant booking, but cases (a) or (b) will catch that fourth case.
- Local time
- Today, 11:23
- Joined
- Aug 30, 2003
- Messages
- 36,270
A visual on the overlap problem, and what I think is a simpler criteria:
http://www.baldyweb.com/OverLap.htm
http://www.baldyweb.com/OverLap.htm
