append query and update field problem

[hikaru]

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[Klatuu]

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[hikaru]

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[Klatuu]

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[hikaru]

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP


:

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[Klatuu]

You are using the main form's NewRecord property in the subform. It should
be using the subform's NewRecord property. Also, you should not have the
foreign key field in the list of fields you set a default value for.
--
Dave Hargis, Microsoft Access MVP

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

:

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP


:

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[hikaru]

sorry to keep bothering you, but it still not working properly.

this is the record i'm trying to copy:
http://img232.imageshack.us/img232/6980/form1wm3.jpg
the selected VO, is the second one 'abc2'. when I click the add new of the
main form, it copies only that selected VO!

here is the form viewing added record:
http://img223.imageshack.us/img223/7320/form2eq3.jpg

******the code of the main form:
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value",
"Cont_Apr_Value", _
"Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _
"Cont_Consultant", "Cont_Overall", "Cont_Comments",
"Contract_No")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
End If

End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub






******the code of the VOs form (subform):
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

'varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks", _
"Cont_Monthly_No")

varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks")

For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

'If GetTag Then
' Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
'End If

End Sub

Private Sub Form_AfterUpdate()

Call TagValues(False)
End Sub

Private Sub Form_Current()
'Call TagValues(Me.Parent.NewRecord)
Call TagValues(Me.NewRecord)

End Sub

You are using the main form's NewRecord property in the subform. It should
be using the subform's NewRecord property. Also, you should not have the
foreign key field in the list of fields you set a default value for.
--
Dave Hargis, Microsoft Access MVP

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP


:

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

:

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP


:

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

'this make the record shown is the last one.
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)

Set rst = Nothing

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler



End Sub

 

[Klatuu]

Sorry, I was wrong before. If you want to copy a record in each subform when
you add a new record in the main form. Then you need to create alll the new
records in click event of the main form, but you still have to populate the
tag values in the current event of each subform.

When you want to address controls in a subform from the main form, you have
to refer to the subform control's form object. That is the subform control,
not the form being used as the subform. The syntax is:

Me.SubformControlName.Form!ControlName
--
Dave Hargis, Microsoft Access MVP

sorry to keep bothering you, but it still not working properly.

this is the record i'm trying to copy:
http://img232.imageshack.us/img232/6980/form1wm3.jpg
the selected VO, is the second one 'abc2'. when I click the add new of the
main form, it copies only that selected VO!

here is the form viewing added record:
http://img223.imageshack.us/img223/7320/form2eq3.jpg

******the code of the main form:
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value",
"Cont_Apr_Value", _
"Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _
"Cont_Consultant", "Cont_Overall", "Cont_Comments",
"Contract_No")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
End If

End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub






******the code of the VOs form (subform):
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

'varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks", _
"Cont_Monthly_No")

varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks")

For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

'If GetTag Then
' Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
'End If

End Sub

Private Sub Form_AfterUpdate()

Call TagValues(False)
End Sub

Private Sub Form_Current()
'Call TagValues(Me.Parent.NewRecord)
Call TagValues(Me.NewRecord)

End Sub

You are using the main form's NewRecord property in the subform. It should
be using the subform's NewRecord property. Also, you should not have the
foreign key field in the list of fields you set a default value for.
--
Dave Hargis, Microsoft Access MVP

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

:

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP


:

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

:

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP


:

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"
& Me.Cont_Month & "#) AND ((Tbl_Cont_Monthly_Change.Contract_No)=""" &
Me.Contract_No & """));"


Debug.Print strSql_ContMonthly
DBEngine(0)(0).Execute strSql_ContMonthly, dbFailOnError


ContNo = Me.Contract_No
stDocName = "Frm_Cont_Monthly_Details_Edit"

DoCmd.close
DoCmd.OpenForm stDocName, , , "Contract_No = '" & ContNo & "'"
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

 

[hikaru]

hi again,

i didn't understand how i should do it the way you told me, sorry about that..
but i tried this way (add the insert sql of VOs in the addNew cmd button)..
it works fine, but it inserts the subrecords (VOs) for the last viewed
contract (forien key [Cont_Monthly_No]<>Cont_Monthly_No of the new record but
= the previous one). so, do you know how i can make the VOs record stored for
the new Contract? many thanks.
**********************
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

If Me.[subFrm_VO].Form.RecordsetClone.RecordCount > 0 Then
strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
"SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & Me.Cont_Monthly_No & " " & _
"FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
Me.Cont_Monthly_No & "));"
DBEngine(0)(0).Execute strSqlVO, dbFailOnError
''Else
'' MsgBox "Main record duplicated, but there were no related records."
End If

DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub
**********************

Sorry, I was wrong before. If you want to copy a record in each subform when
you add a new record in the main form. Then you need to create alll the new
records in click event of the main form, but you still have to populate the
tag values in the current event of each subform.

When you want to address controls in a subform from the main form, you have
to refer to the subform control's form object. That is the subform control,
not the form being used as the subform. The syntax is:

Me.SubformControlName.Form!ControlName
--
Dave Hargis, Microsoft Access MVP

sorry to keep bothering you, but it still not working properly.

this is the record i'm trying to copy:
http://img232.imageshack.us/img232/6980/form1wm3.jpg
the selected VO, is the second one 'abc2'. when I click the add new of the
main form, it copies only that selected VO!

here is the form viewing added record:
http://img223.imageshack.us/img223/7320/form2eq3.jpg

******the code of the main form:
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value",
"Cont_Apr_Value", _
"Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _
"Cont_Consultant", "Cont_Overall", "Cont_Comments",
"Contract_No")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
End If

End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub






******the code of the VOs form (subform):
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

'varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks", _
"Cont_Monthly_No")

varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks")

For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

'If GetTag Then
' Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
'End If

End Sub

Private Sub Form_AfterUpdate()

Call TagValues(False)
End Sub

Private Sub Form_Current()
'Call TagValues(Me.Parent.NewRecord)
Call TagValues(Me.NewRecord)

End Sub

You are using the main form's NewRecord property in the subform. It should
be using the subform's NewRecord property. Also, you should not have the
foreign key field in the list of fields you set a default value for.
--
Dave Hargis, Microsoft Access MVP


:

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

:

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP


:

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

:

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP


:

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim lngID As Long 'Primary key value of the new record.
Dim rst As DAO.Recordset
Dim strSql_ContMonthly As String 'SQL statement.
Dim strSql_VO As String 'SQL statement.
Dim strSql_Obs As String 'SQL statement.
Dim strSql_Ltr As String 'SQL statement.
Dim stDocName As String
Dim rst2 As DAO.Recordset
Dim ContNo As String

'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 "Go to previous record to copy last month's details."
Else
'Duplicate the main record: add to form's clone.
Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

strSql_ContMonthly = "INSERT INTO Tbl_Cont_Monthly_Change (
Cont_Month, Cont_Name, " & _
"Cont_Org_Value, Cont_Apr_Value,
Cont_Start_Date, Cont_Comp_Date, " & _
"Cont_Contractor, Cont_Consultant, Cont_Overall,
Cont_Comments, Contract_No ) " & _
"SELECT Tbl_Cont_Monthly_Change.Cont_Month,
Tbl_Cont_Monthly_Change.Cont_Name, " & _
"Tbl_Cont_Monthly_Change.Cont_Org_Value,
Tbl_Cont_Monthly_Change.Cont_Apr_Value, " & _
"Tbl_Cont_Monthly_Change.Cont_Start_Date,
Tbl_Cont_Monthly_Change.Cont_Comp_Date, " & _
"Tbl_Cont_Monthly_Change.Cont_Contractor,
Tbl_Cont_Monthly_Change.Cont_Consultant, " & _
"Tbl_Cont_Monthly_Change.Cont_Overall,
Tbl_Cont_Monthly_Change.Cont_Comments, " & _
"Tbl_Cont_Monthly_Change.Contract_No " & _
"FROM Tbl_Cont_Monthly_Change " & _
"WHERE (((Tbl_Cont_Monthly_Change.Cont_Month)=#"

 

[hikaru]

here is an update in my code, but this time i get an error msg says:
"You cannot add or change a record because a related record is required in
table 'Tbl_Cont_Monthly_Change'."

****************
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO As String
Dim bkmrkdCont As Integer

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
bkmrkdCont = Me.RecordsetClone!Cont_Monthly_No



If Me.[subFrm_VO].Form.RecordsetClone.RecordCount > 0 Then

DoCmd.GoToRecord , , acNewRec

Form.Refresh

strSqlVO = "INSERT INTO Tbl_VO ( VO_Desc, VO_Value, VO_Remarks )" & _
" SELECT Tbl_VO.VO_Desc, Tbl_VO.VO_Value,
Tbl_VO.VO_Remarks" & _
" FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_VO ON
Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_VO.Cont_Monthly_No" & _
" WHERE (((Tbl_VO.Cont_Monthly_No)=" & bkmrkdCont & "));"


'strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
' "SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & Me.Cont_Monthly_No & " " & _
' "FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
bkmrkdCont & "));"
'strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
' "SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & contmonthID & " " & _
' "FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
Me.Cont_Monthly_No & "));"

DBEngine(0)(0).Execute strSqlVO, dbFailOnError
Else
DoCmd.GoToRecord , , acNewRec
'' MsgBox "Main record duplicated, but there were no related records."
End If

'DoCmd.GoToRecord , , acNewRec
''Set rst = Nothing

Form.Refresh

Debug.Print bkmrkdCont
Debug.Print Me.Cont_Monthly_No
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

hi again,

i didn't understand how i should do it the way you told me, sorry about that..
but i tried this way (add the insert sql of VOs in the addNew cmd button)..
it works fine, but it inserts the subrecords (VOs) for the last viewed
contract (forien key [Cont_Monthly_No]<>Cont_Monthly_No of the new record but
= the previous one). so, do you know how i can make the VOs record stored for
the new Contract? many thanks.
**********************
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

If Me.[subFrm_VO].Form.RecordsetClone.RecordCount > 0 Then
strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
"SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & Me.Cont_Monthly_No & " " & _
"FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
Me.Cont_Monthly_No & "));"
DBEngine(0)(0).Execute strSqlVO, dbFailOnError
''Else
'' MsgBox "Main record duplicated, but there were no related records."
End If

DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub
**********************

Sorry, I was wrong before. If you want to copy a record in each subform when
you add a new record in the main form. Then you need to create alll the new
records in click event of the main form, but you still have to populate the
tag values in the current event of each subform.

When you want to address controls in a subform from the main form, you have
to refer to the subform control's form object. That is the subform control,
not the form being used as the subform. The syntax is:

Me.SubformControlName.Form!ControlName
--
Dave Hargis, Microsoft Access MVP

sorry to keep bothering you, but it still not working properly.

this is the record i'm trying to copy:
http://img232.imageshack.us/img232/6980/form1wm3.jpg
the selected VO, is the second one 'abc2'. when I click the add new of the
main form, it copies only that selected VO!

here is the form viewing added record:
http://img223.imageshack.us/img223/7320/form2eq3.jpg

******the code of the main form:
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value",
"Cont_Apr_Value", _
"Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _
"Cont_Consultant", "Cont_Overall", "Cont_Comments",
"Contract_No")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
End If

End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub






******the code of the VOs form (subform):
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

'varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks", _
"Cont_Monthly_No")

varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks")

For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

'If GetTag Then
' Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
'End If

End Sub

Private Sub Form_AfterUpdate()

Call TagValues(False)
End Sub

Private Sub Form_Current()
'Call TagValues(Me.Parent.NewRecord)
Call TagValues(Me.NewRecord)

End Sub




:

You are using the main form's NewRecord property in the subform. It should
be using the subform's NewRecord property. Also, you should not have the
foreign key field in the list of fields you set a default value for.
--
Dave Hargis, Microsoft Access MVP


:

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

:

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP


:

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

:

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If
End Sub

All you need to do is put the names of your controls in this line:

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")

You need to call this function in two events. The form's Current and After
Update events. Here is how you do that:

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

Now, when you first enter and existing record, the values will be saved in
the Tag properties of the controls. Then if you change any values, the Tag
values will be updated in the form After Update event.

When you go to a new record, the values of the Tag properties will populate
the controls for the new record. Notice also that the date control (change
it to your name) will have the 1 month added to the date in this code:

If GetTag Then
Me.txtEnteredOn = DateAdd("m", 1, Me.txtEnteredOn)
End If

It is as simple as that.
--
Dave Hargis, Microsoft Access MVP


:

hi all,

i'm using a code in the following form:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

when the user click on "Add New", the last record should be duplicated
according to the append query "strSql_ContMonthly", the the form is closed
and opened again so that the new record would be shown. it works fine until
here, but the following line gives me error:
Me.Cont_Month = DateSerial(Year(Cont_Month), Month(Cont_Month) + 1, 1)
the error msg:
Error 2467 - The expression you entered refers to an object that is closed
or doesn't exist.

so i want the new record's date to be 1 month after the previous record, how
can I do this? any help please?


the code:

Private Sub cmdNew_Click()

 

[Klatuu]

Before you can add records in the child table, a record has to exist in the
parent table that the child table is related to.
Adding a record to a form's recordset doesn't add it to the underlying
table. You will need to requery the main form so the record is added to the
table.
--
Dave Hargis, Microsoft Access MVP

here is an update in my code, but this time i get an error msg says:
"You cannot add or change a record because a related record is required in
table 'Tbl_Cont_Monthly_Change'."

****************
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO As String
Dim bkmrkdCont As Integer

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
bkmrkdCont = Me.RecordsetClone!Cont_Monthly_No



If Me.[subFrm_VO].Form.RecordsetClone.RecordCount > 0 Then

DoCmd.GoToRecord , , acNewRec

Form.Refresh

strSqlVO = "INSERT INTO Tbl_VO ( VO_Desc, VO_Value, VO_Remarks )" & _
" SELECT Tbl_VO.VO_Desc, Tbl_VO.VO_Value,
Tbl_VO.VO_Remarks" & _
" FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_VO ON
Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_VO.Cont_Monthly_No" & _
" WHERE (((Tbl_VO.Cont_Monthly_No)=" & bkmrkdCont & "));"


'strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
' "SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & Me.Cont_Monthly_No & " " & _
' "FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
bkmrkdCont & "));"
'strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
' "SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & contmonthID & " " & _
' "FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
Me.Cont_Monthly_No & "));"

DBEngine(0)(0).Execute strSqlVO, dbFailOnError
Else
DoCmd.GoToRecord , , acNewRec
'' MsgBox "Main record duplicated, but there were no related records."
End If

'DoCmd.GoToRecord , , acNewRec
''Set rst = Nothing

Form.Refresh

Debug.Print bkmrkdCont
Debug.Print Me.Cont_Monthly_No
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

hi again,

i didn't understand how i should do it the way you told me, sorry about that..
but i tried this way (add the insert sql of VOs in the addNew cmd button)..
it works fine, but it inserts the subrecords (VOs) for the last viewed
contract (forien key [Cont_Monthly_No]<>Cont_Monthly_No of the new record but
= the previous one). so, do you know how i can make the VOs record stored for
the new Contract? many thanks.
**********************
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark

If Me.[subFrm_VO].Form.RecordsetClone.RecordCount > 0 Then
strSqlVO = "INSERT INTO Tbl_VO ( VO_Month, VO_Desc, VO_Value,
VO_Remarks, Cont_Monthly_No )" & _
"SELECT Tbl_VO.VO_Month, Tbl_VO.VO_Desc,
Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & Me.Cont_Monthly_No & " " & _
"FROM Tbl_VO WHERE (((Tbl_VO.Cont_Monthly_No)=" &
Me.Cont_Monthly_No & "));"
DBEngine(0)(0).Execute strSqlVO, dbFailOnError
''Else
'' MsgBox "Main record duplicated, but there were no related records."
End If

DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub
**********************

Sorry, I was wrong before. If you want to copy a record in each subform when
you add a new record in the main form. Then you need to create alll the new
records in click event of the main form, but you still have to populate the
tag values in the current event of each subform.

When you want to address controls in a subform from the main form, you have
to refer to the subform control's form object. That is the subform control,
not the form being used as the subform. The syntax is:

Me.SubformControlName.Form!ControlName
--
Dave Hargis, Microsoft Access MVP


:

sorry to keep bothering you, but it still not working properly.

this is the record i'm trying to copy:
http://img232.imageshack.us/img232/6980/form1wm3.jpg
the selected VO, is the second one 'abc2'. when I click the add new of the
main form, it copies only that selected VO!

here is the form viewing added record:
http://img223.imageshack.us/img223/7320/form2eq3.jpg

******the code of the main form:
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
DoCmd.GoToRecord , , acNewRec
'Set rst = Nothing
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value",
"Cont_Apr_Value", _
"Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _
"Cont_Consultant", "Cont_Overall", "Cont_Comments",
"Contract_No")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
End If

End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub






******the code of the VOs form (subform):
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

'varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks", _
"Cont_Monthly_No")

varCtlNames = Array("VO_Month", "VO_Desc", "VO_Value", "VO_Remarks")

For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

'If GetTag Then
' Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)
'End If

End Sub

Private Sub Form_AfterUpdate()

Call TagValues(False)
End Sub

Private Sub Form_Current()
'Call TagValues(Me.Parent.NewRecord)
Call TagValues(Me.NewRecord)

End Sub




:

You are using the main form's NewRecord property in the subform. It should
be using the subform's NewRecord property. Also, you should not have the
foreign key field in the list of fields you set a default value for.
--
Dave Hargis, Microsoft Access MVP


:

unfortunately, it's not working

I used this code in the first sub form:
Private Sub Form_Current()
Call TagValues(Me.Parent.NewRecord)
End Sub

so that when user click the add new of the main form the main record is
copied + all the records in sub forms that are related to that main record.

but what happens is that it copy only the first subrecord (so if there are
more in subform VO, it wan't copy them) and it copy the forien key as well,
so it's stored in the previous record.. I hope you understand what i am
trying to say.., any help is appriated.. thanks.

:

Copy the routine into each subform and use the same technique
--
Dave Hargis, Microsoft Access MVP


:

it works just fine for me, thanks a lot... but I need to also add the
subforms (subtables) fields if they are available:
http://img169.imageshack.us/img169/3230/frmcontmonthlydetailsedqn4.jpg

there are 3 subforms that are based on 3 tables that are related to the
table that the main form is based on.

how can I do that?

:

This may be a repeat, but I am not sure my last responce got posted.

There is a much easier way to do this. The usual way to copy values from
one record to a new record is to use the controls Tag properties to save the
value of the previous record. Here is a function that will save the values
of the Tag properties of the controls you want to include or use the tag
values to populate the controls.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn")
For lngX = 0 To UBound(varCtlNames)
If GetTag Then

 

[Klatuu]

If you go back to my original post, it will explain what you need for the
main form.
Since the list of controls will be different for each subform, each will
have to have it's own version of the sub or you will have to modify the sub
to determine which form is calling and have a list of controls for each sub.
You would also have to make it Public in a standard module.

I am guessing you don't have 25 subforms loaded at the same time, so you
probably load each subform dynamically. In that case, if you need to copy
some value from the main form, you will need to to that in the subform's load
event. Now there is one problem with that. If when you load the main form,
you have a subform control that has a form in it's recordsource, it may not
work correctly for that form. The reason being (and I have no idea why) a
subform actually loads before the main form, so if you reference controls on
the main form, they may not be instansiated yet.

So, my recommendation would be that you ensure no subform loads with the
main form. Then when you load a subform, it's load event can get the values
from the main form. As to populating values from a previous record in a
subform, you have to have at least one record to copy the values from.
--
Dave Hargis, Microsoft Access MVP

OK... so I'm having the same issues and I'm trying to follow this procedure,
but I'm having difficulty understanding 'what' goes 'where'. My Main Form has
25 individual subforms that can be called up and added to each record. Do I
need to add all this code to the Main form, or to each Sub form? And where
does the expression listed here go? I can get the Main form to duplicate, but
I need the subs (however many there are with each record - up to 25) to
duplicate along with it. It seems that Hikaru is using two different methods
in his code (Recordset Clones and Tags). Can we start somewhere around the
beginning again? What goes on the Main form (including click events for each
sub) and what gets loaded for each sub (whether it's the OnCurrent event or
the AfterUpdate event). I really appreciate it.

Sorry, I was wrong before. If you want to copy a record in each subform when
you add a new record in the main form. Then you need to create alll the new
records in click event of the main form, but you still have to populate the
tag values in the current event of each subform.

When you want to address controls in a subform from the main form, you have
to refer to the subform control's form object. That is the subform control,
not the form being used as the subform. The syntax is:

Me.SubformControlName.Form!ControlName

sorry to keep bothering you, but it still not working properly.

[quoted text clipped - 264 lines]

Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

 

[Klatuu]

In this thread, it is the first reply with my name.
You posted the same question 4 times. Be patient, it takes about 5 minutes
before a new post shows and, you have to wait until I get an email and have
time to respond. We are all volunteers who do this on our own time.
--
Dave Hargis, Microsoft Access MVP

How do I find your original post?

If you go back to my original post, it will explain what you need for the
main form.
Since the list of controls will be different for each subform, each will
have to have it's own version of the sub or you will have to modify the sub
to determine which form is calling and have a list of controls for each sub.
You would also have to make it Public in a standard module.

I am guessing you don't have 25 subforms loaded at the same time, so you
probably load each subform dynamically. In that case, if you need to copy
some value from the main form, you will need to to that in the subform's load
event. Now there is one problem with that. If when you load the main form,
you have a subform control that has a form in it's recordsource, it may not
work correctly for that form. The reason being (and I have no idea why) a
subform actually loads before the main form, so if you reference controls on
the main form, they may not be instansiated yet.

So, my recommendation would be that you ensure no subform loads with the
main form. Then when you load a subform, it's load event can get the values
from the main form. As to populating values from a previous record in a
subform, you have to have at least one record to copy the values from.

OK... so I'm having the same issues and I'm trying to follow this procedure,
but I'm having difficulty understanding 'what' goes 'where'. My Main Form has

[quoted text clipped - 23 lines]

Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast

 

[hikaru]

FINALLY, the code works fine now, and here are the final codes [all in the
main form], I hope this will work with you brutuss100.... And thank you very
very much Klatuu for your time and efforts:

BTW: I'm using AutoNumbers for Tbl_Cont_Monthly_Change and the subforms'
tables,,

***************
Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO As String
Dim strSqlObs As String
Dim strSqlLtr As String
Dim bkmrkdCont As Integer
Dim MaxRec As Integer

On Error GoTo Err_cmdNew_Click


Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
'move to last record 'to copy its details
rst.MoveLast
'bookmark the last record
Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark
'takes the value of the bookmarked Cont_Monthly_No for subforms' sql
statements
bkmrkdCont = Me.RecordsetClone!Cont_Monthly_No

'add the new record based on Function: TagValues
DoCmd.GoToRecord , , acNewRec

Form.Refresh
'takes the value of greatest (last saved which is of the
Tbl_Cont_Monthly_Change report that we've just created) Cont_Monthly_No
MaxRec = DMax("Cont_Monthly_No", "Tbl_Cont_Monthly_Change")

'Debug.Print MaxRec
'sql to insert the VOs of the last record (last
Tbl_Cont_Monthly_Change report) into the new record
strSqlVO = "INSERT INTO Tbl_VO ( VO_Desc, VO_Value, VO_Remarks,
Cont_Monthly_No )" & _
" SELECT Tbl_VO.VO_Desc, Tbl_VO.VO_Value,
Tbl_VO.VO_Remarks," & MaxRec & "" & _
" FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_VO ON
Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_VO.Cont_Monthly_No" & _
" WHERE (((Tbl_VO.Cont_Monthly_No)=" & bkmrkdCont & "));"

DBEngine(0)(0).Execute strSqlVO, dbFailOnError

'sql to insert the Obstructions of the last record (last
Tbl_Cont_Monthly_Change report) into the new record
strSqlObs = "INSERT INTO Tbl_Obstructions ( Obs_Desc,
Cont_Monthly_No )" & _
" SELECT Tbl_Obstructions.Obs_Desc," & MaxRec & "" & _
" FROM Tbl_Cont_Monthly_Change INNER JOIN
Tbl_Obstructions ON Tbl_Cont_Monthly_Change.Cont_Monthly_No =
Tbl_Obstructions.Cont_Monthly_No" & _
" WHERE (((Tbl_Obstructions.Cont_Monthly_No)=" &
bkmrkdCont & "));"

DBEngine(0)(0).Execute strSqlObs, dbFailOnError

'sql to insert the Letters of the last record (last
Tbl_Cont_Monthly_Change report) into the new record
strSqlLtr = "INSERT INTO Tbl_Letters ( Ltr_Subject, Ltr_Date,
Cont_Monthly_No )" & _
" SELECT Tbl_Letters.Ltr_Subject, Tbl_Letters.Ltr_Date,"
& MaxRec & "" & _
" FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_Letters ON
Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_Letters.Cont_Monthly_No" & _
" WHERE (((Tbl_Letters.Cont_Monthly_No)=" & bkmrkdCont &
"));"

DBEngine(0)(0).Execute strSqlLtr, dbFailOnError


Form.Refresh

Debug.Print bkmrkdCont
Debug.Print Me.Cont_Monthly_No
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description, vbInformation, "Invalid Move"
Resume Exit_cmdNew_Click
End Sub

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long
Dim contmonthID As Integer

varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value",
"Cont_Apr_Value", _
"Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _
"Cont_Consultant", "Cont_Overall", "Cont_Comments",
"Contract_No")

For lngX = 0 To UBound(varCtlNames)
If GetTag Then
Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag
Else
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

If GetTag Then
Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month)

End If

contmonthID = Me.Cont_Monthly_No
'Debug.Print contmonthID

End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub

How do I find your original post?

If you go back to my original post, it will explain what you need for the
main form.
Since the list of controls will be different for each subform, each will
have to have it's own version of the sub or you will have to modify the sub
to determine which form is calling and have a list of controls for each sub.
You would also have to make it Public in a standard module.

I am guessing you don't have 25 subforms loaded at the same time, so you
probably load each subform dynamically. In that case, if you need to copy
some value from the main form, you will need to to that in the subform's load
event. Now there is one problem with that. If when you load the main form,
you have a subform control that has a form in it's recordsource, it may not
work correctly for that form. The reason being (and I have no idea why) a
subform actually loads before the main form, so if you reference controls on
the main form, they may not be instansiated yet.

So, my recommendation would be that you ensure no subform loads with the
main form. Then when you load a subform, it's load event can get the values
from the main form. As to populating values from a previous record in a
subform, you have to have at least one record to copy the values from.

OK... so I'm having the same issues and I'm trying to follow this procedure,
but I'm having difficulty understanding 'what' goes 'where'. My Main Form has

[quoted text clipped - 23 lines]

Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast