Duplicating records using access 2002 VBA 1

[AndyWallace1]

I'm in the process of creating a booking system for the company projectors and laptops.
The main form works fine for individual bookings, but I want to include block bookings, where, for example, a user can book an item one day a week for a given number of weeks.

All I need is shown how to grab the current record, modify the booking date and write the new record, repeating until it reaches the target number of weeks.

Thank you in advance for your help.

 

[TelcoBT]

I'm not at my Access PC so cannot look up the syntax of DateAdd, and not knowing how you intend to record the booking period (eg fromDate to toDate or fromDate for x days) I would look at a loop using the dateAdd function to generate the booking date, and add a new records on each iteration of the loop, populating the fields as appropriate.

a)for x days

for days = 1 to x

addNew
populate fields
rs("bookingDate&quot = bookingDate
update

bookingDate=(DateAdd('d',bookingDate, 1))

next

b) fromDate to toDate

do while fromDate <= toDate

addNew
populate fields
rs(&quot;bookingDate&quot = fromDate
update

fromDate=(DateAdd('d',bookingDate, 1))

loop

I've rushed this off, so you would need to put in your code BUT also check the 'boundary' conditions for the dates, in case I've messed up.

eg if using the 'booking for X days', booking Monday for 5 days should give Friday, as Monday is inclusive, but adding 5 days to Monday would give Saturday, so you need to check my pseudo code does reflect this correctly!

 

[AndyWallace1]

Thanks for the tip. Sorry about not getting this until today but been on holiday.

Not tried it yet (got an MS-Blast virus to irradicate 1st), but will let u know how I get on.

regards

 

[AndyWallace1]

OK...
I've looked at your pseudo coding above and there's a couple of functions I don't understand.
In particular I'm referring to 'addNew' and 'populate fields'. Are these functions you've already created? If so please display the coding.

Based on the info you supplied I modified the pseudo code to acommodate my needs:

Dim Frequency as string ; &quot;d&quot;=days &quot;w&quot;=weeks
Dim blocks as long ; Number of blocks required

for days = 1 to Blocks

addNew
populate fields
rs(&quot;bookingDate&quot = bookingDate
update

if Frequency = &quot;w&quot;

bookingDate=(DateAdd('d',bookingDate, 7))

Else

bookingDate=(DateAdd('d',bookingDate, 1))

next




Providing I can get the addNew and populate fields functions to work I should be able to get block booking function to comply.

 

[AndyWallace1]

OK.... Still in need of a little help here.
I've had an attempt at coding this section and here's what I have so far:

Private Sub Block_Bookings_Click()
On Error GoTo err_Block_Bookings_Click

Dim rec As Recordset

Dim interval As String
Dim blocks As Integer
Dim days As Integer
Dim X As Integer

Do
interval = InputBox(&quot;Would you like to book on a Daily or Weekly basis?&quot;, &quot;Daily or Weekly&quot;, &quot;Please type 'd' for Daily or 'w' for weekly&quot
If interval = &quot;d&quot; Or interval = &quot;D&quot; Then days = 1
If interval = &quot;w&quot; Or interval = &quot;W&quot; Then days = 7
Loop Until interval = &quot;d&quot; Or interval = &quot;D&quot; Or interval = &quot;w&quot; Or interval = &quot;W&quot;

Do
blocks = InputBox(&quot;How any Blocks do you require?&quot;, &quot;Max 10 Blocks&quot;, &quot;No more than 10 blocks can be booked at one time&quot
Loop While blocks <= 0 Or blocks > 10

Exit_Block_Bookings_Click:
Exit Sub

err_Block_Bookings_Click:
MsgBox Err.Description
Resume Exit_Block_Bookings_Click
Set rec = Me.RecordsetClone()

For X = 1 To blocks
rec.MoveLast
rec.AddNew
rec!BookDate = DateAdd(&quot;d&quot;, days, BookDate)
rec.Update
Next

End Sub

==============================
I have no errors as far as the execution of the code goes, but can't get it to generate new records in the table. I'm quite obviously missing something along the way (a few screws methinks.) I think it might have something to do with the recordsetclone() function but not sure. I have a number of books at my disposal but none seem to spend too much time on this subject.

Can anyone assist and put me out of my misery?
I'm sure it wil only be a line or two of code.