Creating a new record in different table using a form.

[Gambit17]

Hi,Thank you for taking the time to read my post. My title may be misleading I wasn't exactly sure how to word it.At the moment I have a text box which once the user enters in a specific code, the form updates with the relevant record. It's pretty much just a search function. What I am wanting to know is, is it possible for any changes the user makes to that record not be saved in the current table from where the record is being drawn but in a new table.For example at the moment the form is pulling data from table cotton12 and if I make changes in the form it affects the data in table cotton12 how could I make it so that when I change data it creates a new record, leaving the original intact, in table cotton13.At the moment I have it so the user doesn't make any changes in the form other that selecting yes in a combo box, and then a make table query, pulls all the "yes" records into a new table. Then the user has to search again for each record to make changes.This is inefficient and time wasting. Any suggestions would be much appreciated.ThanksGambit17

[Dal Jeanis]

You could
(1) pull the relevant "searched" records into a temporary table
(2) Allow any updates against the data in the temporary table, marking changed records with a flag
(3) when the person is ready to commit all the changes, copy only the changed records to the new table, then delete all records from the temporary table.

That actually doesn't sound very different from what you are doing now, though.

Alternatively, you could set up your subform with unbound controls, and have them loaded from cotton12 records in the Form's onCurrent record. You're cycling through Cotton12, but the form data isn't bound to that table. In the AfterUpdate for each of the controls, you copy the current record either to a temp file or to cotton13, if it doesn't already exist over there, and then update with the changed value(s).

You have to decide if cotton13 can get multiple copies or not. If not, then you'd keep the key from cotton12 as the key, but not with autonumber. If so, then you'd have to think it through more than I can this time of night.

[Gambit17]

That sounds perfect, the 2nd option. Just so I understand correctly, if you have unbound controls, the form is pulling the info from the table(cotton12) ,but when you make changes it doesnt actually effect cotton12. It stores it in the Forms onCurrent Record and then on the AfterUpdate it copies to cotton13.

The record wont exist in cotton13 it will be an entirely new record. I would like there to perhaps be a command button that after the user has made the changes on the form he can click update to cotton13, instead of the after update. Is this possible or would the after update be easier? What would the code look like?

Thank You so much

[Dal Jeanis]

If you have unbound controls, they aren't bound to anything. Thus, Access doesn't automatically do anything to them.

In the On Current Event, you will use VBA to set the value of each control equal to the appropriate value from the current record. Thus,
they will appear to be bound to something. You will also set an invisible checkbox that says "nothing has been updated".

In the After Update event of each control, you will change the checkbox to say that "something has been updated". Thus, if no controls are updated, then no copy will be made. (You could make this a visible checkbox, so the user could tick it to cause a copy of the record without any fields different. You'd have to decide if you wanted the user to be able to uncheck it. If they do, you should reset all the fields to their original values.

When the user attempts to leave the record, you will clone the record from cotton12 to cotton13, inserting the new record with the current values from the controls.

I'll have to review which events you'll need to capture to cause that clone to occur. Anybody have that list handy in their brain?