Append multiple records using two dates as criteria

[DubCap01]

Hi all

I have an unbound form, and on that I have the following fields:

RoomNumber '102
ArriveDate '26-Jan-2015
DepartDate '29-Jan-2015
BookingID '45


GuestID '23

What I am aiming to do is append a record to TblBookings for each day INCLUDING ArriveDate but excluding DepartDate, by RoomNumber - so in effect using the above dates, it would append 3 records. This in effect stops that room number from being double booked, as there is a record for the date when it IS booked... I have my filters working correctly on the >out part of the results, as if I manually type the values into the table, the rooms get excluded where I want them to be excluded.

I need help with a For i = .... next i , and the sql statement to get it to append the records

Anybody want to help me out please?
Thanks in anticipation

Pete

[June7]

Try:

Dim d As Date
d = Me.StartDate
Do Until d = Me.EndDate
CurrentDb.Execute "INSERT INTO tablename(RoomNumber, ResDate, BookingID) VALUES(" & Me.RoomNumber & ", #" & d & "#, " & Me.BookingID & ")"
d = d + 1
Loop

Or

For i = 0 to EndDate - StartDate - 1
CurrentDb.Execute "INSERT INTO tablename(RoomNumber, ResDate, BookingID) VALUES(" & Me.RoomNumber & ", #" & Me.StartDate + i & "#, " & Me.BookingID & ")"
Next

[DubCap01]

Hi June7,

thanks for the fast and excellent reply. I tried both options, option one worked perfectly, option two didn't

Does the job, thank you so much for your help!

Pete

[June7]

Ooops! Missed an & in the second. I have fixed the post.