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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |