A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Copy an existing record into appropriate tables after modifying.



 
 
Thread Tools Display Modes
  #1  
Old July 3rd, 2004, 08:05 PM
bdehning
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifying.

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?
--
Brian
  #2  
Old July 3rd, 2004, 09:09 PM
Mike Painter
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifying.


"bdehning" wrote in message
...
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?


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.


  #3  
Old July 3rd, 2004, 10:03 PM
bdehning
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin

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


"Mike Painter" wrote:


"bdehning" wrote in message
...
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?


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.



  #4  
Old July 4th, 2004, 12:30 AM
Tom Wickerath
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin

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
___________________________________________

"bdehning" wrote in message
...

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
___________________________________________

"Mike Painter" wrote in message
. com...

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.

___________________________________________

"bdehning" wrote in message
...

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?
--
Brian


  #5  
Old July 4th, 2004, 01:07 AM
Mike Painter
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin


"bdehning" wrote in message
...
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.

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.


  #6  
Old July 4th, 2004, 02:30 AM
bdehning
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin

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" wrote:

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
___________________________________________

"bdehning" wrote in message
...

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
___________________________________________

"Mike Painter" wrote in message
. com...

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.

___________________________________________

"bdehning" wrote in message
...

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?
--
Brian



  #7  
Old July 4th, 2004, 04:16 AM
bdehning
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin

Still need help
--
Brian


"Mike Painter" wrote:


"bdehning" wrote in message
...
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.

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.



  #8  
Old July 4th, 2004, 04:06 PM
bdehning
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin

I should have said I am getting a type mismatch error with the microsoft example, not data type mismatch.
--
Brian


"bdehning" wrote:

Still need help
--
Brian


"Mike Painter" wrote:


"bdehning" wrote in message
...
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.

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.



  #9  
Old July 7th, 2004, 08:44 AM
Tom Wickerath
external usenet poster
 
Posts: n/a
Default Copy an existing record into appropriate tables after modifyin

Brian,

I tried both the Microsoft example you gave me but I get a data type mismatach error.


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...

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.

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.

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
__________________________________________

"bdehning" wrote in message
...

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" wrote in message
...


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
___________________________________________

"bdehning" wrote in message
...

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
___________________________________________

"Mike Painter" wrote in message
. com...

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.

___________________________________________

"bdehning" wrote in message
...

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?
--
Brian


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get Current Record from the subform with the datasheet Tom Using Forms 1 June 18th, 2004 12:35 PM
Button to print current record only Fatz Using Forms 6 June 17th, 2004 01:58 PM
Need help with Tables Design and Relationships Tom Database Design 24 May 19th, 2004 06:51 PM
Avoid Creating A Duplicate Record Mark New Users 4 May 11th, 2004 01:52 AM
copy data - tables rekoop Database Design 1 May 4th, 2004 03:18 PM


All times are GMT +1. The time now is 02:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.