Duplicate a record in form and subform and update a field

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

Guest

I have used Allen Browne's code to duplicate a record in the main form and
the related records in the subform and that worked beautifully. I have an
effective date to which I add 1 year in the main form's record using DateAdd
and that works great, but in the subform's record, the effective date is not
incremented. How do I accomplish this? I am just beginning to learn VBA.
Thanks.
Cathy
 
Cathy,

I'm not sure what you're doing exactly, but to assign a value to a subform
control in an event procedure of a main form control, the syntax is:

Me![YourSubform].Form![YourSubformControl] = YourValue

Me![YourSubform] refers to the subform *control* on the main form, while
Me![YourSubform].Form refers to the subform *itself*. From there you can
refer to any controls *on* the subform.

Hope that helps.
Sprinks
 
Here is my code.
Private Sub Command243_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement
Dim lngID As Long 'Primary key value of the new record

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone
With Me.RecordsetClone
.AddNew
!clientid = Me.clientid
!effectivedate = DateAdd("yyyy", 1, Me.effectivedate)
!medical = Me.medical
!hra = Me.hra
!hsa = Me.hsa
!dental = Me.dental
!flex = Me.flex
!FlexOnly = Me.FlexOnly
!rx = Me.rx
!vision = Me.vision
!std = Me.std
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !clientid

'Duplicate the related records: append query
If Me.[sbffees].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO Fees ( ClientID, EffectiveDate) " & _
"SELECT " & lngID & " As NewID, EffectiveDate " & _
"FROM Fees WHERE ClientID = " & Me.clientid & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command243_Click"
Resume Exit_Handler
End Sub
When I duplicate the main record, I add one year to the effective date. What
I want to happen when I duplicate the record for the subform (using the
INSERT INTO is for the effective date in that record to be the updated
effective date (the one I added a year to). Can you help me with that? Thanks.
Cathy
Sprinks said:
Cathy,

I'm not sure what you're doing exactly, but to assign a value to a subform
control in an event procedure of a main form control, the syntax is:

Me![YourSubform].Form![YourSubformControl] = YourValue

Me![YourSubform] refers to the subform *control* on the main form, while
Me![YourSubform].Form refers to the subform *itself*. From there you can
refer to any controls *on* the subform.

Hope that helps.
Sprinks

csingram said:
I have used Allen Browne's code to duplicate a record in the main form and
the related records in the subform and that worked beautifully. I have an
effective date to which I add 1 year in the main form's record using DateAdd
and that works great, but in the subform's record, the effective date is not
incremented. How do I accomplish this? I am just beginning to learn VBA.
Thanks.
Cathy
Click to expand...
Click to expand...
 
Cathy,

I'm not very experienced working with recordsets, but your SELECT statement
is choosing the EffectiveDate from the existing Fees records, NOT from the
new master record. It's also not clear to me what your primary keys
are--presumably it's a compound key of ClientID and EffectiveDate.

To insert the correct EffectiveDate, either assign the result of the DateAdd
function to a Date variable or recalculate the new date on the fly, and use
this value in your Select statement. The following uses the former method.

Dim dteNewEffDate as Date
With Me.Recordsetclone
dteNewEffDate = DateAdd("yyyy", 1, Me.effectivedate)
....
End With

"SELECT " & lngID & " As NewID, dteNewEffDate As
NewEffectiveDate " & _
"FROM Fees WHERE ClientID = " & Me.clientid & ";"

Hope that helps.
Sprinks

csingram said:
Here is my code.
Private Sub Command243_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement
Dim lngID As Long 'Primary key value of the new record

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone
With Me.RecordsetClone
.AddNew
!clientid = Me.clientid
!effectivedate = DateAdd("yyyy", 1, Me.effectivedate)
!medical = Me.medical
!hra = Me.hra
!hsa = Me.hsa
!dental = Me.dental
!flex = Me.flex
!FlexOnly = Me.FlexOnly
!rx = Me.rx
!vision = Me.vision
!std = Me.std
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !clientid

'Duplicate the related records: append query
If Me.[sbffees].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO Fees ( ClientID, EffectiveDate) " & _
"SELECT " & lngID & " As NewID, EffectiveDate " & _
"FROM Fees WHERE ClientID = " & Me.clientid & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command243_Click"
Resume Exit_Handler
End Sub
When I duplicate the main record, I add one year to the effective date. What
I want to happen when I duplicate the record for the subform (using the
INSERT INTO is for the effective date in that record to be the updated
effective date (the one I added a year to). Can you help me with that? Thanks.
Cathy
Sprinks said:
Cathy,

I'm not sure what you're doing exactly, but to assign a value to a subform
control in an event procedure of a main form control, the syntax is:

Me![YourSubform].Form![YourSubformControl] = YourValue

Me![YourSubform] refers to the subform *control* on the main form, while
Me![YourSubform].Form refers to the subform *itself*. From there you can
refer to any controls *on* the subform.

Hope that helps.
Sprinks

csingram said:
I have used Allen Browne's code to duplicate a record in the main form and
the related records in the subform and that worked beautifully. I have an
effective date to which I add 1 year in the main form's record using DateAdd
and that works great, but in the subform's record, the effective date is not
incremented. How do I accomplish this? I am just beginning to learn VBA.
Thanks.
Cathy
Click to expand...
Click to expand...
Click to expand...
 
I still was not able to do what I wanted to do with this code, but I decided
to do it another way until I learn VBA better. This was helpful though.
Thanks.
Cathy

Sprinks said:
Cathy,

I'm not very experienced working with recordsets, but your SELECT statement
is choosing the EffectiveDate from the existing Fees records, NOT from the
new master record. It's also not clear to me what your primary keys
are--presumably it's a compound key of ClientID and EffectiveDate.

To insert the correct EffectiveDate, either assign the result of the DateAdd
function to a Date variable or recalculate the new date on the fly, and use
this value in your Select statement. The following uses the former method.

Dim dteNewEffDate as Date
With Me.Recordsetclone
dteNewEffDate = DateAdd("yyyy", 1, Me.effectivedate)
...
End With

"SELECT " & lngID & " As NewID, dteNewEffDate As
NewEffectiveDate " & _
"FROM Fees WHERE ClientID = " & Me.clientid & ";"

Hope that helps.
Sprinks

csingram said:
Here is my code.
Private Sub Command243_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement
Dim lngID As Long 'Primary key value of the new record

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone
With Me.RecordsetClone
.AddNew
!clientid = Me.clientid
!effectivedate = DateAdd("yyyy", 1, Me.effectivedate)
!medical = Me.medical
!hra = Me.hra
!hsa = Me.hsa
!dental = Me.dental
!flex = Me.flex
!FlexOnly = Me.FlexOnly
!rx = Me.rx
!vision = Me.vision
!std = Me.std
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !clientid

'Duplicate the related records: append query
If Me.[sbffees].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO Fees ( ClientID, EffectiveDate) " & _
"SELECT " & lngID & " As NewID, EffectiveDate " & _
"FROM Fees WHERE ClientID = " & Me.clientid & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"Command243_Click"
Resume Exit_Handler
End Sub
When I duplicate the main record, I add one year to the effective date. What
I want to happen when I duplicate the record for the subform (using the
INSERT INTO is for the effective date in that record to be the updated
effective date (the one I added a year to). Can you help me with that? Thanks.
Cathy
Sprinks said:
Cathy,

I'm not sure what you're doing exactly, but to assign a value to a subform
control in an event procedure of a main form control, the syntax is:

Me![YourSubform].Form![YourSubformControl] = YourValue

Me![YourSubform] refers to the subform *control* on the main form, while
Me![YourSubform].Form refers to the subform *itself*. From there you can
refer to any controls *on* the subform.

Hope that helps.
Sprinks

:

I have used Allen Browne's code to duplicate a record in the main form and
the related records in the subform and that worked beautifully. I have an
effective date to which I add 1 year in the main form's record using DateAdd
and that works great, but in the subform's record, the effective date is not
incremented. How do I accomplish this? I am just beginning to learn VBA.
Thanks.
Cathy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top