One form, saving to table and creating a new modified record

[ems2000]

Hi, I have a problem write records from a form that was obtained using a query from a table 1, and want the form to save the record, then create a new record entry to the same table 1, with a couple of fields changed.

Table 1 (Actually named Work Order Table)
Equipment Name -Boat
Sub Component -Motor
Work Description -PM
Last Completed date -100509
WO Status -Closed

The form has all these fields displayed from the query.

I need to save this record which I am doing with the save on click command to table 1. I need to know how to write a new record to the table 1 and have Work Description changed to "issued" the dat changed to blank and the WO Status changed to "Open".

Presently I am using several queries to do this and have tried and failed using recordset moved. Any help would be appreciated.

 

[wazz]

how about an append query? your save button can do the save and append, or a separate button for the append with values you want already filled in.

 

[boblarson]

I seriously question why you would have records saved like that. It would appear to be a normalization problem as you shouldn't have to store that many redundant fields.

 

[wazz]

i thought i'd skip that part... :|

 

[boblarson]

i thought i'd skip that part... :|

I don't blame you a bit. It seems that if we mention that part, we get sucked into explaining how it should be and that can take a lot of time/effort which we might not want to expend. So I seriously understand, believe me.

 

[wazz]

i knew you would.

 

[ems2000]

Thanks for the feedback. The database only has four tables and this one is the master table and these are independent fields. They accumlate over time. I am presently using an append and update query and thought there was an easier method to duplicate the record with some changes using a recordset command or something else.

Thanks again

PS I am knew to Access so may be I am on the wrong track altogether based on your answers.

 

[boblarson]

Thanks for the feedback. The database only has four tables and this one is the master table and these are independent fields. They accumlate over time. I am presently using an append and update query and thought there was an easier method to duplicate the record with some changes using a recordset command or something else.

Thanks again

PS I am knew to Access so may be I am on the wrong track altogether based on your answers.

Can you post either a screenshot of your relationships (with the tables expanded so we can see what fields you have) or just post an empty copy of the database? I have a free "database reset" tool on my Access Tools page of my website (see my signature) which will clear all of your tables (except for ones you specify) and then compacts it so you can then zip the database by right-clicking on the file and selecting SEND TO > COMPRESSED FOLDER.

Make sure to use a COPY of your database with that reset tool.

 

[ems2000]

Thanks again.
I have one equipment table, all relevant to that.
1 Employee table
1 Parts table
and 1 table for storing all the records, which all fields within the record are generally populated.

The other table is used to collect various details from the forms. In fact the form uses combo boxes to collect the field information which then populates the work order table. The only change I can think of to normalize the data would be to remove the equipment name and subcompoenent from the main table, but then how would I link the record info?

Its just something I have been playing with.

I attached a screen shot of the tables as requested