Another update problem?

[Ray]

Any help? I have two tables. TABLE 1 one which shows all the days of
the year with

[BookingID] (Number)
[Revdate](date) Each day of the year
[Booked](Yes/No)

TABLE 2 with reservation details with

[BookingID] (Autonumber)
[DateIn](date)
[NoOfNights] (number)

I believe that I'm sort of on the right track. What I would like to be
able to do is to update TABLE 1 with the information from TABLE 2 i.e.
a BookingNo and [Booked] (Yes/No). TABLE 2 shows the night the guest
arrives and number of nights they stay. I would like to show in TABLE
1 that those dates are now booked.

TABLE 2

20/2/2004 1 night BookingID = 1

24/2/2004 – 25/2/2004 2 nights BookingID = 2 etc
TABLE 1
Booking ID RevDate Booked

0 19/2/2004 No
1 20/2/2004 Yes
0 21/2/2004 No
0 22/2/2004 No
0 23/2/2004 No
2 24/2/2004 Yes
2 25/2/2004 Yes
0 26/2/2004 No

I imagine that I use something like an update VB but I'm fairly lost
at this stage. The [NoofNights] needs to update the dates. Have I made
myself sufficiently clear?

TIA - Ray

[Mike Storr]

On 14 Feb 2004 13:32:45 -0800, Ray wrote:

Any help? I have two tables. TABLE 1 one which shows all the days of
the year with

[BookingID] (Number)
[Revdate](date) Each day of the year
[Booked](Yes/No)

TABLE 2 with reservation details with

[BookingID] (Autonumber)
[DateIn](date)
[NoOfNights] (number)

I believe that I'm sort of on the right track. What I would like to be
able to do is to update TABLE 1 with the information from TABLE 2 i.e.
a BookingNo and [Booked] (Yes/No). TABLE 2 shows the night the guest
arrives and number of nights they stay. I would like to show in TABLE
1 that those dates are now booked.

TABLE 2

20/2/2004 1 night BookingID = 1

24/2/2004 – 25/2/2004 2 nights BookingID = 2 etc
TABLE 1
Booking ID RevDate Booked

0 19/2/2004 No
1 20/2/2004 Yes
0 21/2/2004 No
0 22/2/2004 No
0 23/2/2004 No
2 24/2/2004 Yes
2 25/2/2004 Yes
0 26/2/2004 No

I imagine that I use something like an update VB but I'm fairly lost
at this stage. The [NoofNights] needs to update the dates. Have I made
myself sufficiently clear?

TIA - Ray

After writing this, i realized that you have a date range in the example of
the Table2 structure. Waste of time if you are storing the number of nights
there as well. The example below requires only one date to be in that
field. Becomes more complicated with two. I didn't test this or anything,
so no warranties implied.

Function AppendDatesBooked() as Boolean
On Error Goto Stoprun
AppendDatesBooked = False

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As Database
Dim BookID As Long
Dim NumNights As Long
Dim StrtDate as Date
Dim cntr As Long

Set db = CurrentDb
Set rst1 = db.OpenRecordset("Table1", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Table2", dbOpenSnapShot)

With rset2
Do Until .EOF = True
BookID = !BookingID
NumNights = !NoOfNightd
StrtDate = !DateIn
Do Until cntr = NumNights
rset1.AddNew
rset1!BookingID = BookID
rset1!RevDate = DateAdd("d", cntr, StrtDate)
rset1.Booked = True
rset1.Update
cntr = cntr +1
Loop
.MoveNext
Loop

AppendDatesBooked = True

Exit_Here:
Exit Function

stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here

End Function
--
Mike Storr
veraccess.com

[Ray]

Mike Storr <st******@sympatico.ca> wrote in message news:<4z****************************@40tude.net>.. .

On 14 Feb 2004 13:32:45 -0800, Ray wrote:

Any help? I have two tables. TABLE 1 one which shows all the days of
the year with

[BookingID] (Number)
[Revdate](date) Each day of the year
[Booked](Yes/No)

TABLE 2 with reservation details with

[BookingID] (Autonumber)
[DateIn](date)
[NoOfNights] (number)

I believe that I'm sort of on the right track. What I would like to be
able to do is to update TABLE 1 with the information from TABLE 2 i.e.
a BookingNo and [Booked] (Yes/No). TABLE 2 shows the night the guest
arrives and number of nights they stay. I would like to show in TABLE
1 that those dates are now booked.

TABLE 2

20/2/2004 1 night BookingID = 1

24/2/2004 ? 25/2/2004 2 nights BookingID = 2 etc
TABLE 1
Booking ID RevDate Booked

0 19/2/2004 No
1 20/2/2004 Yes
0 21/2/2004 No
0 22/2/2004 No
0 23/2/2004 No
2 24/2/2004 Yes
2 25/2/2004 Yes
0 26/2/2004 No

I imagine that I use something like an update VB but I'm fairly lost
at this stage. The [NoofNights] needs to update the dates. Have I made
myself sufficiently clear?

TIA - Ray

After writing this, i realized that you have a date range in the example of
the Table2 structure. Waste of time if you are storing the number of nights
there as well. The example below requires only one date to be in that
field. Becomes more complicated with two. I didn't test this or anything,
so no warranties implied.

Function AppendDatesBooked() as Boolean
On Error Goto Stoprun
AppendDatesBooked = False

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As Database
Dim BookID As Long
Dim NumNights As Long
Dim StrtDate as Date
Dim cntr As Long

Set db = CurrentDb
Set rst1 = db.OpenRecordset("Table1", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Table2", dbOpenSnapShot)

With rset2
Do Until .EOF = True
BookID = !BookingID
NumNights = !NoOfNightd
StrtDate = !DateIn
Do Until cntr = NumNights
rset1.AddNew
rset1!BookingID = BookID
rset1!RevDate = DateAdd("d", cntr, StrtDate)
rset1.Booked = True
rset1.Update
cntr = cntr +1
Loop
.MoveNext
Loop

AppendDatesBooked = True

Exit_Here:
Exit Function

stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here

End Function

Thanks Mike for your reply which works! However, instead of appending
the date to the table I would like to have a table which lists evry
date for the year and then to update the [Booked] to yes or no. That
way I can search for a booked room and a non-booked room. Can the
above code be modified to do this for the days booked. The booking
dates come from a form based on Table1.

have I made myself clear? TIA - Ray

[Mike Storr]

On 15 Feb 2004 10:21:35 -0800, Ray wrote:

Thanks Mike for your reply which works! However, instead of appending
the date to the table I would like to have a table which lists evry
date for the year and then to update the [Booked] to yes or no. That
way I can search for a booked room and a non-booked room. Can the
above code be modified to do this for the days booked. The booking
dates come from a form based on Table1.

have I made myself clear? TIA - Ray

If I understand correctly, you want a table, pre-populated with dates, then
to update them as necessary? This is rather inefficient. Besides, how many
of each day of the year do you add? One for each room right? That means
you'd have one record in the table for each room on each day of the year -
wheter it was being used or not. You'd be better served with a different
table structure. I think you should rethink you're schema before
continuing.
This isn't homework is it? Seems to be a lot of room booking questions
lately.

--
Mike Storr
veraccess.com