Using For...Each...Next Statement

[Craig]

Hello,

I am developing a database to track property rentals. Guests can rent
properties, but rentals cannot overlap.

When a new rental is entered, I want to check whether the given property is
available. I believe that I can do this using a For...Each...Next statement,
but I am new to VBA programming and am not sure of the exact syntax.

Here is what I want to do specifically, in simplified form. If anyone can
help me with this, I believe I can modify it to fit the real (more
complicated) form. If not, I'll be back!

I have a form (frmForm) whose recordset comes from tblTable. tblTable
basically has three fields/attributes: ID (unique autonumber ID for each
record), date (start date of each reservation), and days (number of days each
reservation runs for).

On the form are two fields, XDate and XDays, and a command button.

So, when the user completes XDate and XDays, and clicks the button, the
database should:

1. Only consider records in tblTable where date < XDate.
2. For each of those records, add days + date to get EndDate.
3. If EndDate > XDate, then the reservations overlap. End the loop.
4. If the loop goes through all records without getting a true condition at
#3, the end the loop, and add XDate and XDays to the database as a new
reservation.

Hopefully this makes sense! As I said, I feel like it is very simple to do,
I'm mainly just not sure how to call the recordset and refer to the "eaches"
in my imagined routine.

Any help is greatly appreciated!

Craig

 

[Marshall Barton]

I am developing a database to track property rentals. Guests can rent
properties, but rentals cannot overlap.

When a new rental is entered, I want to check whether the given property is
available. I believe that I can do this using a For...Each...Next statement,
but I am new to VBA programming and am not sure of the exact syntax.

Here is what I want to do specifically, in simplified form. If anyone can
help me with this, I believe I can modify it to fit the real (more
complicated) form. If not, I'll be back!

I have a form (frmForm) whose recordset comes from tblTable. tblTable
basically has three fields/attributes: ID (unique autonumber ID for each
record), date (start date of each reservation), and days (number of days each
reservation runs for).

On the form are two fields, XDate and XDays, and a command button.

So, when the user completes XDate and XDays, and clicks the button, the
database should:

1. Only consider records in tblTable where date < XDate.
2. For each of those records, add days + date to get EndDate.
3. If EndDate > XDate, then the reservations overlap. End the loop.
4. If the loop goes through all records without getting a true condition at
#3, the end the loop, and add XDate and XDays to the database as a new
reservation.

That's not quite the right check. What if the date in the
table is > XDate but less then Xdate+XDays?

The correct check for an overlap is more like:
date < XDate+XDays And XDate < date+days
You may or may not want one or both of the < to be <=

A good way ti check for an overlap anywhere in the table is
along these lines:

If DCount("*", "tblTable", "]date] < #" _
& DateAdd("d", XDays, XDate) & "# And #" _
& XDate & "# < DateAdd("d", days, date)") > 0 Then
MsgBox "There is a conflict"
Else
. . .
Emd If

 

[pietlinden]

I did something very much like what you're doing like 9 years ago.
Albert Kallal gave me some great advice. It was an equipment
reservation system, but the idea is the same, and so is the problem:

What [things] are available between [start date] and [end date] and
reservations/loans/rentals cannot overlap.