Adding data with a different date

[Steve Walker]

Access 2000 o/s Win 2000

I am using an access database (one table) to record a room booking system
for classrooms. We have four ICT suites and need to book out lesson times
for other departments. I am using a Win 2000 Server with IIS5 to run an
intranet - I have got the connection going and it can now access the
database to book a room.

My problem is that I need to duplicate certain room bookings for every week
in the booking table. A computer suite needs to be booked for regular
lessons and when free can be booked by others. Once populated with the
regular lessons I then need to add each lesson every week How do I
duplicate those lessons?

Any link or advice would be appreciated.

Steve Walker

 

[Tim Ferguson]

Once populated with the
regular lessons I then need to add each lesson every week How do I
duplicate those lessons?

Save this as a query:

PARAMETERS pDateFrom DATETIME,
DateTo DATETIME,
CompanyCode TEXT;
INSERT INTO Bookings
(DateFrom, DateTo, RoomNumber, CompanyCode)
VALUES
(pDateFrom, pDateTo, pRoomNumber, pCompanyCode)


And run this as a sub:

Set qdf = QueryDefs("swInsertBooking")

For w = 1 to 5 ' do the next five weeks
' start a week from today
qdf.Parameters("pDateFrom") = Date() + w * 7
' and make it three days
qdf.Parameters("pDateTo") = Date() + w * 7 + 3

' you'd probably get these values from a form
qdf.Parameters("pRoomNumber") = 23
qdf.Parameters("pCompanyCode") = "ACME"

' and make it so
qdf.Execute dbFailOnError

If Err <> 0 Then
' something bad happened: maybe a double booking

End If

Next w

Set qdf = Nothing


.... or something like it.

Hope it helps. But if this is a real room booking system, would you not be
better served by something like Outlook or Groupwise that you don't have to
program and that you know is going to work right out of the box?

Just a thought..

Tim F

 

[Steve Walker]

Thanks Tim this should do the job. Thought about Outlook but my lovely
little lot would probably try and get into it as soon as they could. At
least with a server I can find out who tries to access what when. Thanks
again.

Steve Walker

 

[Tim Ferguson]

At
least with a server I can find out who tries to access what when.

Getting to grips with Access security is another whole learning curve. All
the best and come back if there is anything we can help with!

B Wishes


Tim F

 

[Steve Walker]

Thanks again Tim

Steve

Getting to grips with Access security is another whole learning curve. All
the best and come back if there is anything we can help with!

B Wishes


Tim F