Duplicate a booking record with a number of frequency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
With help, I created a coding to duplicate a booking record. Now I need to
duplicate a booking record by a number of frequency. In other words, if I
want to duplicate a booking by 52 weeks, it would do so automatically with
the correct future dates installed. This saves having to duplicate the form
52 times manually.
Below the coding I already have that just duplicate a booking record
details. I would like to put in a coding to do the frequency thing.


Private Sub bDuplicateCurrentRecord_Click()
On Error GoTo Err_bDuplicateCurrentRecord_Click

Beep

Dim sBookingID As String
Dim sSerialNo As String
Dim sNoOfBookingPerRequistionNo2 As String
Dim sNameOfHost As String
Dim sTrustOrNonTrust As String
Dim sDepartment As String
Dim sContactTelephone As String
Dim sContactMobile As String
Dim sContactFax As String
Dim sDateOfRequest As String
Dim sDateOfFunction As String
Dim sTimeRequiredStart As String
Dim sTimeRequiredEnd As String
Dim sVenue As String
Dim sReasonForBooking As String
Dim sNoOfGuests As String
Dim sStandingOrder As String
Dim sComments As String
Dim sBookingReceivedBy As String
Dim sNonStandardCharge As String

sBookingID = BookingID
sSerialNo = SerialNo
sNoOfBookingPerRequistionNo2 = NoOfBookingPerRequistionNo2
sNameOfHost = NameOfHost
sTrustOrNonTrust = TrustOrNonTrust
sDepartment = Department
sContactTelephone = ContactTelephone
sContactMobile = ContactMobile
sContactFax = ContactFax
sDateOfRequest = DateOfRequest
sDateOfFunction = DateOfFunction
sTimeRequiredStart = TimeRequiredStart
sTimeRequiredEnd = TimeRequiredEnd
sVenue = Venue
sReasonForBooking = ReasonForBooking
sNoOfGuests = NoOfGuests
sStandingOrder = StandingOrder
sComments = Comments
sBookingReceivedBy = BookingReceivedBy
sNonStandardCharge = NonStandardCharge

DoCmd.GoToRecord , , acNewRec

'BookingID.Value = sBookingID ILLEGAL ASSSIGNMENT!!!!
SerialNo = sSerialNo
NoOfBookingPerRequistionNo2 = sNoOfBookingPerRequistionNo2
NameOfHost = sNameOfHost
TrustOrNonTrust = sTrustOrNonTrust
Department = sDepartment
ContactTelephone = sContactTelephone
ContactMobile = sContactMobile
ContactFax = sContactFax
DateOfRequest = sDateOfRequest
DateOfFunction = sDateOfFunction
TimeRequiredStart = sTimeRequiredStart
TimeRequiredEnd = sTimeRequiredEnd
Venue = sVenue
ReasonForBooking = sReasonForBooking
NoOfGuests = sNoOfGuests
StandingOrder = sStandingOrder
Comments = sComments
BookingReceivedBy = sBookingReceivedBy
NonStandardCharge = sNonStandardCharge

sBookingID = ""
sSerialNo = ""
sNoOfBookingPerRequistionNo2 = ""
sNameOfHost = ""
sTrustOrNonTrust = ""
sDepartment = ""
sContactTelephone = ""
sContactMobile = ""
sContactFax = ""
sDateOfRequest = ""
sDateOfFunction = ""
sTimeRequiredStart = ""
sTimeRequiredEnd = ""
sVenue = ""
sReasonForBooking = ""
sNoOfGuests = ""
sStandingOrder = ""
sComments = ""
sBookingReceivedBy = ""
sNonStandardCharge = ""

Exit_bDuplicateCurrentRecord_Click:
Exit Sub

Err_bDuplicateCurrentRecord_Click:
If Err = 94 Then 'Invalid use of Null'
Beep
MsgBox "A box is incomplete. Please ensure that all boxes are completed
before attempting to duplicate the current booking", vbInformation, "Invalid
Duplication"
Exit Sub
Else
If Err = 2113 Then 'The data you entered is not valid for this box.
Exit Sub
Else
MsgBox Err.Description
Resume Exit_bDuplicateCurrentRecord_Click
End If
End If

End Sub

Can anyone help me out?

Thanks.

Paul
 
Paul,
the correct future dates installed. This saves having to duplicate the form
52 times manually.
Click to expand...

I hope you really don't mean "duplicate the form 52 times"!!! :O


To add more than 1 record at a time, you need use a loop:

Dim vintNumNewRecs as Integer

vintNumNewRecs = 52
For i = 1 to vintNumNewRecs
' some statements
Next i

Some where (a form?) you would need get the number of new records to add, a
starting date and an interval (every 3 days, every 7 days, every 5 days but
not on weekend, etc). Once you have calculated the date, create a valid SQL
string and insert the record.


BTW, if [BookingID] is an Autonumber, you cannot assign it a value (you get
an error)

And you don't need to clear the variables defined in the Sub. When you reach
"End Sub" or "Exit Sub", the variables are destroyed.

(sSerialNo = ""
sNoOfBookingPerRequistionNo2 = ""
sNameOfHost = ""
sTrustOrNonTrust = "")
 
SteveS said:
Paul,
the correct future dates installed. This saves having to duplicate the form
52 times manually.
Click to expand...

I hope you really don't mean "duplicate the form 52 times"!!! :O


To add more than 1 record at a time, you need use a loop:

Dim vintNumNewRecs as Integer

vintNumNewRecs = 52
For i = 1 to vintNumNewRecs
' some statements
Next i

Some where (a form?) you would need get the number of new records to add, a
starting date and an interval (every 3 days, every 7 days, every 5 days but
not on weekend, etc). Once you have calculated the date, create a valid SQL
string and insert the record.


BTW, if [BookingID] is an Autonumber, you cannot assign it a value (you get
an error)

And you don't need to clear the variables defined in the Sub. When you reach
"End Sub" or "Exit Sub", the variables are destroyed.

(sSerialNo = ""
sNoOfBookingPerRequistionNo2 = ""
sNameOfHost = ""
sTrustOrNonTrust = "")


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


paulphonak said:
Hi!
With help, I created a coding to duplicate a booking record. Now I need to
duplicate a booking record by a number of frequency. In other words, if I
want to duplicate a booking by 52 weeks, it would do so automatically with
the correct future dates installed. This saves having to duplicate the form
52 times manually.
Below the coding I already have that just duplicate a booking record
details. I would like to put in a coding to do the frequency thing.


Private Sub bDuplicateCurrentRecord_Click()
On Error GoTo Err_bDuplicateCurrentRecord_Click

Beep

Dim sBookingID As String
Dim sSerialNo As String
Dim sNoOfBookingPerRequistionNo2 As String
Dim sNameOfHost As String
Dim sTrustOrNonTrust As String
Dim sDepartment As String
Dim sContactTelephone As String
Dim sContactMobile As String
Dim sContactFax As String
Dim sDateOfRequest As String
Dim sDateOfFunction As String
Dim sTimeRequiredStart As String
Dim sTimeRequiredEnd As String
Dim sVenue As String
Dim sReasonForBooking As String
Dim sNoOfGuests As String
Dim sStandingOrder As String
Dim sComments As String
Dim sBookingReceivedBy As String
Dim sNonStandardCharge As String

sBookingID = BookingID
sSerialNo = SerialNo
sNoOfBookingPerRequistionNo2 = NoOfBookingPerRequistionNo2
sNameOfHost = NameOfHost
sTrustOrNonTrust = TrustOrNonTrust
sDepartment = Department
sContactTelephone = ContactTelephone
sContactMobile = ContactMobile
sContactFax = ContactFax
sDateOfRequest = DateOfRequest
sDateOfFunction = DateOfFunction
sTimeRequiredStart = TimeRequiredStart
sTimeRequiredEnd = TimeRequiredEnd
sVenue = Venue
sReasonForBooking = ReasonForBooking
sNoOfGuests = NoOfGuests
sStandingOrder = StandingOrder
sComments = Comments
sBookingReceivedBy = BookingReceivedBy
sNonStandardCharge = NonStandardCharge

DoCmd.GoToRecord , , acNewRec

'BookingID.Value = sBookingID ILLEGAL ASSSIGNMENT!!!!
SerialNo = sSerialNo
NoOfBookingPerRequistionNo2 = sNoOfBookingPerRequistionNo2
NameOfHost = sNameOfHost
TrustOrNonTrust = sTrustOrNonTrust
Department = sDepartment
ContactTelephone = sContactTelephone
ContactMobile = sContactMobile
ContactFax = sContactFax
DateOfRequest = sDateOfRequest
DateOfFunction = sDateOfFunction
TimeRequiredStart = sTimeRequiredStart
TimeRequiredEnd = sTimeRequiredEnd
Venue = sVenue
ReasonForBooking = sReasonForBooking
NoOfGuests = sNoOfGuests
StandingOrder = sStandingOrder
Comments = sComments
BookingReceivedBy = sBookingReceivedBy
NonStandardCharge = sNonStandardCharge

sBookingID = ""
sSerialNo = ""
sNoOfBookingPerRequistionNo2 = ""
sNameOfHost = ""
sTrustOrNonTrust = ""
sDepartment = ""
sContactTelephone = ""
sContactMobile = ""
sContactFax = ""
sDateOfRequest = ""
sDateOfFunction = ""
sTimeRequiredStart = ""
sTimeRequiredEnd = ""
sVenue = ""
sReasonForBooking = ""
sNoOfGuests = ""
sStandingOrder = ""
sComments = ""
sBookingReceivedBy = ""
sNonStandardCharge = ""

Exit_bDuplicateCurrentRecord_Click:
Exit Sub

Err_bDuplicateCurrentRecord_Click:
If Err = 94 Then 'Invalid use of Null'
Beep
MsgBox "A box is incomplete. Please ensure that all boxes are completed
before attempting to duplicate the current booking", vbInformation, "Invalid
Duplication"
Exit Sub
Else
If Err = 2113 Then 'The data you entered is not valid for this box.
Exit Sub
Else
MsgBox Err.Description
Resume Exit_bDuplicateCurrentRecord_Click
End If
End If

End Sub

Can anyone help me out?

Thanks.

Paul
Click to expand...
Click to expand...

Thanks for the reply. I am no programmer but correct me. Each record is
controlled by BookingID which is an autonumber. On the form, I click on
'Duplicate a record' macro which duplicate both form and subform data. If a
client wants to book 52 weekly coffee provision, I would have to duplicate
the command 52 times! And then have to go to each duplicated record to
correct the DateOfFunction field (08/11/06, 15/11/06, 22/11/06, etc).
Manually! The code above is for the duplicate macro.
I am not sure how I would incorporate your suggestion '... you would need
get the number of new records to add, a starting date and an interval (every
3 days, every 7 days, every 5 days but not on weekend, etc). Once you have
calculated the date, create a valid SQL string and insert the record.' I
don't know if I need to create this valid SQL string within the above code.
I am really struck here!

Paul
 
Paul,
Thanks for the reply. I am no programmer but correct me. Each record is
controlled by BookingID which is an autonumber. On the form, I click on
'Duplicate a record' macro which duplicate both form and subform data. If a
Click to expand...

The code (not macro) you posted add one new record in what appears to me as
the main form record source. I would guess there is a button on the main form
named "bDuplicateCurrentRecord" with code in the Click event.
client wants to book 52 weekly coffee provision, I would have to duplicate
the command 52 times! And then have to go to each duplicated record to
correct the DateOfFunction field (08/11/06, 15/11/06, 22/11/06, etc).
Manually! The code above is for the duplicate macro.
Click to expand...

That is why you would use VBA code in an event procedure instead of a macro.
In code you can do calculations, change variables, then insert a new record
into one or more tables/recordsets that you can't do in a macro.
I am not sure how I would incorporate your suggestion '... you would need
get the number of new records to add, a starting date and an interval (every
3 days, every 7 days, every 5 days but not on weekend, etc). Once you have
calculated the date, create a valid SQL string and insert the record.' I
don't know if I need to create this valid SQL string within the above code.
I am really struck here!
Click to expand...


I don't know how you database is set up (table names, field names,
relationships), but here is an example of what the code might look like.

This is AIR CODE....watch for line wrap...

'-------- beg sample-----
Private Sub bDuplicateCurrentRecord_Click()
On Error GoTo Err_bDuplicateCurrentRecord_Click

Dim strSQL As String
Dim strValues As String

Dim intNumRecs2Add As Integer
Dim dteFutureDate As Date
Dim intDayInterval As Integer
Dim i As Integer

intNumRecs2Add = Nz(Me.tbNumRecs2Add, 0) ' tb = textbox

dteFutureDate = Me.DateOfFunction

For i = 0 To intNumRecs2Add - 1

strSQL = "INSERT INTO MyTable ("
strValues = " VALUES ("

If Len(Trim(Nz(Me.SerialNo, ""))) > 0 Then
strSQL = strSQL & "[SerialNo],"
strValues = strValues & "'" & Me.SerialNo & "', "
End If

If Nz(Me.NoOfBookingPerRequistionNo2, 0) Then
strSQL = strSQL & "[NoOfBookingPerRequistionNo2],"
strValues = strValues & Me.NoOfBookingPerRequistionNo2 & ", "
End If

If Len(Trim(Nz(Me.NameOfHost, ""))) Then
strSQL = strSQL & "[NameOfHost],"
strValues = strValues & "'" & Me.NameOfHost & "', "
End If

If Len(Trim(Nz(Me.Department, ""))) Then
strSQL = strSQL & "[Department],"
strValues = strValues & "'" & Me.Department & "', "
End If

If Len(Trim(Nz(Me.ContactTelephone, ""))) Then
strSQL = strSQL & "[ContactTelephone],"
strValues = strValues & "'" & Me.ContactTelephone & "', "
End If

If Len(Trim(Nz(Me.ContactMobile, ""))) Then
strSQL = strSQL & "[ContactMobile],"
strValues = strValues & "'" & Me.ContactMobile & "', "
End If

If Len(Trim(Nz(Me.ContactFax, ""))) Then
strSQL = strSQL & "[ContactFax],"
strValues = strValues & "'" & Me.ContactFax & "', "
End If

If Len(Trim(Nz(Me.DateOfRequest, ""))) Then
strSQL = strSQL & "[DateOfRequest],"
strValues = strValues & "#" & Me.DateOfRequest & "#, "
End If

'Future dates
If Len(Trim(Nz(dteFutureDate, ""))) > 0 Then
strSQL = strSQL & "[DateOfFunction],"
strValues = strValues & "#" & dteFutureDate & "#, "
End If

If Len(Trim(Nz(Me.TimeRequiredStart, ""))) Then
strSQL = strSQL & "[TimeRequiredStart],"
strValues = strValues & "#" & Me.TimeRequiredStart & "#, "
End If

If Len(Trim(Nz(Me.TimeRequiredEnd, ""))) Then
strSQL = strSQL & "[TimeRequiredEnd],"
strValues = strValues & "#" & Me.TimeRequiredEnd & "#, "
End If

strSQL = Left(strSQL, Len(strSQL) - 2) & ") " & Left(strValues,
Len(strValues) - 2) & ");"
MsgBox strSQL

' insert the record
' CurrentDb.Execute strSQL, dbFailOnError

'calc new new future date
dteFutureDate = DateAdd("d", Me.intDayInterval, dteFutureDate)

Next i

Exit_bDuplicateCurrentRecord_Click:
Exit Sub

Err_bDuplicateCurrentRecord_Click:
If Err = 94 Then 'Invalid use of Null'
Beep
MsgBox "A box is incomplete. Please ensure that all boxes are
completed before attempting to duplicate the current booking", vbInformation,
"InvalidDuplication "
Exit Sub
Else
If Err = 2113 Then 'The data you entered is not valid for this box.
Exit Sub
Else
MsgBox Err.Description
Resume Exit_bDuplicateCurrentRecord_Click
End If
End If

End Sub
'----------end sample ----------------------


HTH
 
SteveS said:
Paul,
Thanks for the reply. I am no programmer but correct me. Each record is
controlled by BookingID which is an autonumber. On the form, I click on
'Duplicate a record' macro which duplicate both form and subform data. If a
Click to expand...

The code (not macro) you posted add one new record in what appears to me as
the main form record source. I would guess there is a button on the main form
named "bDuplicateCurrentRecord" with code in the Click event.
client wants to book 52 weekly coffee provision, I would have to duplicate
the command 52 times! And then have to go to each duplicated record to
correct the DateOfFunction field (08/11/06, 15/11/06, 22/11/06, etc).
Manually! The code above is for the duplicate macro.
Click to expand...

That is why you would use VBA code in an event procedure instead of a macro.
In code you can do calculations, change variables, then insert a new record
into one or more tables/recordsets that you can't do in a macro.
I am not sure how I would incorporate your suggestion '... you would need
get the number of new records to add, a starting date and an interval (every
3 days, every 7 days, every 5 days but not on weekend, etc). Once you have
calculated the date, create a valid SQL string and insert the record.' I
don't know if I need to create this valid SQL string within the above code.
I am really struck here!
Click to expand...


I don't know how you database is set up (table names, field names,
relationships), but here is an example of what the code might look like.

This is AIR CODE....watch for line wrap...

'-------- beg sample-----
Private Sub bDuplicateCurrentRecord_Click()
On Error GoTo Err_bDuplicateCurrentRecord_Click

Dim strSQL As String
Dim strValues As String

Dim intNumRecs2Add As Integer
Dim dteFutureDate As Date
Dim intDayInterval As Integer
Dim i As Integer

intNumRecs2Add = Nz(Me.tbNumRecs2Add, 0) ' tb = textbox

dteFutureDate = Me.DateOfFunction

For i = 0 To intNumRecs2Add - 1

strSQL = "INSERT INTO MyTable ("
strValues = " VALUES ("

If Len(Trim(Nz(Me.SerialNo, ""))) > 0 Then
strSQL = strSQL & "[SerialNo],"
strValues = strValues & "'" & Me.SerialNo & "', "
End If

If Nz(Me.NoOfBookingPerRequistionNo2, 0) Then
strSQL = strSQL & "[NoOfBookingPerRequistionNo2],"
strValues = strValues & Me.NoOfBookingPerRequistionNo2 & ", "
End If

If Len(Trim(Nz(Me.NameOfHost, ""))) Then
strSQL = strSQL & "[NameOfHost],"
strValues = strValues & "'" & Me.NameOfHost & "', "
End If

If Len(Trim(Nz(Me.Department, ""))) Then
strSQL = strSQL & "[Department],"
strValues = strValues & "'" & Me.Department & "', "
End If

If Len(Trim(Nz(Me.ContactTelephone, ""))) Then
strSQL = strSQL & "[ContactTelephone],"
strValues = strValues & "'" & Me.ContactTelephone & "', "
End If

If Len(Trim(Nz(Me.ContactMobile, ""))) Then
strSQL = strSQL & "[ContactMobile],"
strValues = strValues & "'" & Me.ContactMobile & "', "
End If

If Len(Trim(Nz(Me.ContactFax, ""))) Then
strSQL = strSQL & "[ContactFax],"
strValues = strValues & "'" & Me.ContactFax & "', "
End If

If Len(Trim(Nz(Me.DateOfRequest, ""))) Then
strSQL = strSQL & "[DateOfRequest],"
strValues = strValues & "#" & Me.DateOfRequest & "#, "
End If

'Future dates
If Len(Trim(Nz(dteFutureDate, ""))) > 0 Then
strSQL = strSQL & "[DateOfFunction],"
strValues = strValues & "#" & dteFutureDate & "#, "
End If

If Len(Trim(Nz(Me.TimeRequiredStart, ""))) Then
strSQL = strSQL & "[TimeRequiredStart],"
strValues = strValues & "#" & Me.TimeRequiredStart & "#, "
End If

If Len(Trim(Nz(Me.TimeRequiredEnd, ""))) Then
strSQL = strSQL & "[TimeRequiredEnd],"
strValues = strValues & "#" & Me.TimeRequiredEnd & "#, "
End If

strSQL = Left(strSQL, Len(strSQL) - 2) & ") " & Left(strValues,
Len(strValues) - 2) & ");"
MsgBox strSQL

' insert the record
' CurrentDb.Execute strSQL, dbFailOnError

'calc new new future date
dteFutureDate = DateAdd("d", Me.intDayInterval, dteFutureDate)

Next i

Exit_bDuplicateCurrentRecord_Click:
Exit Sub

Err_bDuplicateCurrentRecord_Click:
If Err = 94 Then 'Invalid use of Null'
Beep
MsgBox "A box is incomplete. Please ensure that all boxes are
completed before attempting to duplicate the current booking", vbInformation,
"InvalidDuplication "
Exit Sub
Else
If Err = 2113 Then 'The data you entered is not valid for this box.
Exit Sub
Else
MsgBox Err.Description
Resume Exit_bDuplicateCurrentRecord_Click
End If
End If

End Sub
'----------end sample ----------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks for your reply. I typed the new coding and when I ticked on the Duplicate macro on my form, the following appeared:
Click to expand...

Microsoft Visual Basic Compile error for the '.tbNumRecs2Add' part of the
following line:

intNumRecs2Add = Nz(Me.tbNumRecs2Add, 0) 'tb = textbox

I put a new field, NumRecs2Add, on the form so that a number of duplication
could be inputted, eg. 52.

Where can I go from here? Would it be easier to attach the actual database?
Or whatever? Help!!

Paul
 
Paul,



I don't think a macro can do what you want. What you posted is VBA code.

Microsoft Visual Basic Compile error for the '.tbNumRecs2Add' part of the
following line:

intNumRecs2Add = Nz(Me.tbNumRecs2Add, 0) 'tb = textbox

I put a new field, NumRecs2Add, on the form so that a number of duplication
could be inputted, eg. 52.
Click to expand...

Fields are in tables, controls are on forms. You should have added a text
box control to the form, not a field in a table. The name of the text box
should be "tbNumRecs2Add" - the 'tb' prefix stands for text box.

Where can I go from here? Would it be easier to attach the actual database?
Or whatever? Help!!
Click to expand...

If you want, you can send me the mdb. First, delete or change any sensitive
or confidential data. Do a Compact and Repair, then WinZip it. But you will
learn more if you try to debug this yourself (with help).
 
Back
Top