Copy an existing record into appropriate tables after modifying.

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

Guest

I have a Form called "Account Information". This form has a subform called "Location". This subform has a subform called "Service Calls".

I use the main form to first enter date information. I then have a copy of the main form that is not data entry and is used for updating information for any of the previously entered information. All forms and subforms are relationally joined.

Would I would like to do would be able to create a duplicate of the original data for each record as brought up in the updatable form so that any changes to fields could be done and then be able to place this new record into the tables and be able to generate autonumbers as needed as well.

My main primary key is "Policy Number" and can not have duplicates. That is why I need to be able to duplcate the record, modify it and then paste it into the appropriate tables. The policy number would be changed during this update to allow the record to be entered.

Any suggestions?
 
bdehning said:
I have a Form called "Account Information". This form has a subform
Click to expand...
called "Location". This subform has a subform called "Service Calls".
I use the main form to first enter date information. I then have a copy
Click to expand...
of the main form that is not data entry and is used for updating information
for any of the previously entered information. All forms and subforms are
relationally joined.
Would I would like to do would be able to create a duplicate of the
Click to expand...
original data for each record as brought up in the updatable form so that
any changes to fields could be done and then be able to place this new
record into the tables and be able to generate autonumbers as needed as
well.
My main primary key is "Policy Number" and can not have duplicates. That
Click to expand...
is why I need to be able to duplcate the record, modify it and then paste it
into the appropriate tables. The policy number would be changed during this
update to allow the record to be entered.
Any suggestions?
Click to expand...

Are you trying to keep *all* of the original record for some purpose?

May the changes you want to make affect any field or are you talking about
adding additional information. You might want to change an address or a
name. You might want to add a phone number or a service call.

What you seem to want to do would be handled in code either by opening a
recordset or using a SQL query.
 
Main reason is to just to change a few fields in the main table Account Information. For example, when first entered the record would have date enteries of 1/1/04 amd 1/1/05 and then the following year would need a new record with dates of 1/1/05 and 1/1/06 being used and all remaining fields probably staying the same.

Like I said I have a form to update information. i just need the ability to add addtional records each year without having to manually enter fields from year to year.
 
Hi Brian,

Try the following sample:

Fill Record with Data from Previous Record Automatically
http://support.microsoft.com/?id=210236

Although it indicates "Access 2000" in the title, the code will work equally well for Access 97,
2002, and 2003. My only suggestion is to add a Form_Current event procedure to the calling form,
so that the AutoFillNewRecord function is called only if we are adding a new record. Something
like this:

Private Sub Form_Current()
On Error GoTo ProcError

If Me.NewRecord Then
Call AutoFillNewRecord([Forms]![Customers])
End If

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error in Form_Current Event
Procedure..."
Resume ExitProc
End Sub

I suppose you are pretty much guaranteed of this being a new record, if you are using the copy of
your form with Data Entry mode set to Yes. However, it is really not necessary to make a copy of
your form for updating information vs. adding information. You can use VBA code to set the
appropriate open mode depending on the situation. This way, you won't need to maintain two
copies of the same form.

Tom
___________________________________________


Main reason is to just to change a few fields in the main table Account Information. For example,
when first entered the record would have date enteries of 1/1/04 amd 1/1/05 and then the
following year would need a new record with dates of 1/1/05 and 1/1/06 being used and all
remaining fields probably staying the same.

Like I said I have a form to update information. i just need the ability to add addtional
records each year without having to manually enter fields from year to year.
--
Brian
___________________________________________


Are you trying to keep *all* of the original record for some purpose?

May the changes you want to make affect any field or are you talking about
adding additional information. You might want to change an address or a
name. You might want to add a phone number or a service call.

What you seem to want to do would be handled in code either by opening a
recordset or using a SQL query.

___________________________________________


I have a Form called "Account Information". This form has a subform called "Location". This
subform has a subform called "Service Calls".

I use the main form to first enter date information. I then have a copy of the main form that is
not data entry and is used for updating information for any of the previously entered
information. All forms and subforms are relationally joined.

Would I would like to do would be able to create a duplicate of the original data for each record
as brought up in the updatable form so that any changes to fields could be done and then be able
to place this new record into the tables and be able to generate autonumbers as needed as well.

My main primary key is "Policy Number" and can not have duplicates. That is why I need to be
able to duplcate the record, modify it and then paste it into the appropriate tables. The policy
number would be changed during this update to allow the record to be entered.

Any suggestions?
 
bdehning said:
Main reason is to just to change a few fields in the main table Account
Click to expand...
Information. For example, when first entered the record would have date
enteries of 1/1/04 amd 1/1/05 and then the following year would need a new
record with dates of 1/1/05 and 1/1/06 being used and all remaining fields
probably staying the same.
Like I said I have a form to update information. i just need the ability
Click to expand...
to add addtional records each year without having to manually enter fields
from year to year.

If the dates are used as period dates then each year should have a new
record in a table not be moved or duplicated someplace else.
If there is a lot of data and you want to use code there are a variety of
ways to get the previous information. Dlookup with the latest date would be
one way.
The better way is to have a separate related table with these dates and
other information that changes relative to the year.

If this is for your use, you are free to do what you wish, but if for
somebody else you should ask them if it will be audited. Accountants don't
like to see such movement without an audit trail.
 
I tried both the Microsoft example you gave me but I get a data type mismatach error. Your code it took but how do I invoke the code to create the new record with the data from an existing record.

I tried to insert a new record and also to advance the record number at the bottom.

Need some more assistance on how to proceed please. I suupose I am being stupid.
--
Brian


Tom Wickerath said:
Hi Brian,

Try the following sample:

Fill Record with Data from Previous Record Automatically
http://support.microsoft.com/?id=210236

Although it indicates "Access 2000" in the title, the code will work equally well for Access 97,
2002, and 2003. My only suggestion is to add a Form_Current event procedure to the calling form,
so that the AutoFillNewRecord function is called only if we are adding a new record. Something
like this:

Private Sub Form_Current()
On Error GoTo ProcError

If Me.NewRecord Then
Call AutoFillNewRecord([Forms]![Customers])
End If

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error in Form_Current Event
Procedure..."
Resume ExitProc
End Sub

I suppose you are pretty much guaranteed of this being a new record, if you are using the copy of
your form with Data Entry mode set to Yes. However, it is really not necessary to make a copy of
your form for updating information vs. adding information. You can use VBA code to set the
appropriate open mode depending on the situation. This way, you won't need to maintain two
copies of the same form.

Tom
___________________________________________


Main reason is to just to change a few fields in the main table Account Information. For example,
when first entered the record would have date enteries of 1/1/04 amd 1/1/05 and then the
following year would need a new record with dates of 1/1/05 and 1/1/06 being used and all
remaining fields probably staying the same.

Like I said I have a form to update information. i just need the ability to add addtional
records each year without having to manually enter fields from year to year.
--
Brian
___________________________________________


Are you trying to keep *all* of the original record for some purpose?

May the changes you want to make affect any field or are you talking about
adding additional information. You might want to change an address or a
name. You might want to add a phone number or a service call.

What you seem to want to do would be handled in code either by opening a
recordset or using a SQL query.

___________________________________________


I have a Form called "Account Information". This form has a subform called "Location". This
subform has a subform called "Service Calls".

I use the main form to first enter date information. I then have a copy of the main form that is
not data entry and is used for updating information for any of the previously entered
information. All forms and subforms are relationally joined.

Would I would like to do would be able to create a duplicate of the original data for each record
as brought up in the updatable form so that any changes to fields could be done and then be able
to place this new record into the tables and be able to generate autonumbers as needed as well.

My main primary key is "Policy Number" and can not have duplicates. That is why I need to be
able to duplcate the record, modify it and then paste it into the appropriate tables. The policy
number would be changed during this update to allow the record to be entered.

Any suggestions?
Click to expand...
 
Still need help
--
Brian


Mike Painter said:
Information. For example, when first entered the record would have date
enteries of 1/1/04 amd 1/1/05 and then the following year would need a new
record with dates of 1/1/05 and 1/1/06 being used and all remaining fields
probably staying the same.
to add addtional records each year without having to manually enter fields
from year to year.

If the dates are used as period dates then each year should have a new
record in a table not be moved or duplicated someplace else.
If there is a lot of data and you want to use code there are a variety of
ways to get the previous information. Dlookup with the latest date would be
one way.
The better way is to have a separate related table with these dates and
other information that changes relative to the year.

If this is for your use, you are free to do what you wish, but if for
somebody else you should ask them if it will be audited. Accountants don't
like to see such movement without an audit trail.
Click to expand...
 
I should have said I am getting a type mismatch error with the microsoft example, not data type mismatch.
 
Brian,
I tried both the Microsoft example you gave me but I get a data type mismatach error.
Click to expand...

Did you include a reference to the DAO object library, as indicated in a note in the article?

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run
properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on
the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object
Library check box is selected.
Your code it took...
Click to expand...
I assume you mean the Private Sub Form_Current() event procedure that I added. Is this correct?
I probably should have added that the event procedure that I suggested replaces the instruction
shown in step 5 of the KB article.
...but how do I invoke the code to create the new record with the data from an existing record.
Click to expand...
Have you tried getting the example to work in the Northwind database? If not, try getting it to
work there first following the example. Then try to adapt it to your database after you have it
working as intended in Northwind.
Need some more assistance on how to proceed please.
Click to expand...
Send me a private e-mail message if you still need help. My true e-mail address requires
removing four joined words from the username. I think its fairly obvious how to edit the
username to the correct value....

Note: Please do not post your e-mail address in a newsgroup posting.

Tom
__________________________________________


I tried both the Microsoft example you gave me but I get a data type mismatach error. Your code
it took but how do I invoke the code to create the new record with the data from an existing
record.

I tried to insert a new record and also to advance the record number at the bottom.

Need some more assistance on how to proceed please. I suupose I am being stupid.
--
Brian


__________________________________________




Hi Brian,

Try the following sample:

Fill Record with Data from Previous Record Automatically http://support.microsoft.com/?id=210236

Although it indicates "Access 2000" in the title, the code will work equally well for Access 97,
2002, and 2003. My only suggestion is to add a Form_Current event procedure to the calling form,
so that the AutoFillNewRecord function is called only if we are adding a new record. Something
like this:

Private Sub Form_Current()
On Error GoTo ProcError

If Me.NewRecord Then
Call AutoFillNewRecord([Forms]![Customers])
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error in Form_Current
Event Procedure..."
Resume ExitProc
End Sub

I suppose you are pretty much guaranteed of this being a new record, if you are using the copy of
your form with Data Entry mode set to Yes. However, it is really not necessary to make a copy of
your form for updating information vs. adding information. You can use VBA code to set the
appropriate open mode depending on the situation. This way, you won't need to maintain two
copies of the same form.

Tom
___________________________________________


Main reason is to just to change a few fields in the main table Account Information. For example,
when first entered the record would have date enteries of 1/1/04 amd 1/1/05 and then the
following year would need a new record with dates of 1/1/05 and 1/1/06 being used and all
remaining fields probably staying the same.

Like I said I have a form to update information. i just need the ability to add addtional
records each year without having to manually enter fields from year to year.
--
Brian
___________________________________________


Are you trying to keep *all* of the original record for some purpose?

May the changes you want to make affect any field or are you talking about adding additional
information. You might want to change an address or a name. You might want to add a phone number
or a service call.

What you seem to want to do would be handled in code either by opening a recordset or using a SQL
query.

___________________________________________


I have a Form called "Account Information". This form has a subform called "Location". This
subform has a subform called "Service Calls".

I use the main form to first enter date information. I then have a copy of the main form that is
not data entry and is used for updating information for any of the previously entered
information. All forms and subforms are relationally joined.

Would I would like to do would be able to create a duplicate of the original data for each record
as brought up in the updatable form so that any changes to fields could be done and then be able
to place this new record into the tables and be able to generate autonumbers as needed as well.

My main primary key is "Policy Number" and can not have duplicates. That is why I need to be
able to duplcate the record, modify it and then paste it into the appropriate tables. The policy
number would be changed during this update to allow the record to be entered.

Any suggestions?
 
Back
Top