Performing record changes upon save

[jlreis28]

Good afternoon.

I am trying to create an Access 2010 db that is used for tracking assets. When the data is entered in the main form, I need it to create three additional records with all the same information as the original with two changes. The DeviceID field (number) needs to be incremented up 300000 for the second record, 400000 for the third, and 500000 for the fourth. The second change is to a text field. I need to insert /tray1, /tray2, /tray3, /tray4 to the Command (text) field, one for each record.



I've tried using some of the code to create duplicate records but have yet to make it work even remotely correctly. I thought about using an append query to add all the data in to three additional tables but haven't been able to figure out a way to modify the data in the two fields I need changed. As my VBA and Access skills need some serious upgrading, I thought I would post and see if any one had an easy idea or two on how to make this work.

Thanks and I hope everyone has a great day.
Justin

[June7]

Run an UPDATE query in a Loop structure. Something like:

DoCmd.RunCommand acCommandSaveRecord
For i = 1 to 3
CurrentDb.Execute "INSERT INTO tablename(<whatever fieldnames you want>, DeviceID, field2) VALUES(" & <concatenate data from form> & ", " & Choose(i,3,4,5) & "00000, '/tray" & Choose(i,2,3,4) & "')"
Next

[orange]

It might be helpful if you showed us your tables and relationships as a jpg.

There is a data model for IT Asset Inventory here
http://www.databaseanswers.org/data_...sets/index.htm

[jlreis28]

Good morning and thanks for the replies.

The database is fairly simple, just made to inventory printers and some commands. It needs to associate four device ID's for every printer (DeviceID), each ID represents a tray (DefaultCommand). The goal is to be able to output an Excel file of the devices, each line represents the DeviceID with the correctly associated tray. But as I said before, it has been a long time since I've done anything in Access or VBA and it is making this a painful process.

[June7]

So tblDevice appears to show 4 fields. If that is the case then the relationship is 1 to 1 with tblDeviceMain and might as well just have both tables in one table. If you want 4 records in tblDevice for every record in tblDeviceMain, structure needs to be:

tblDevice
RecordID (PK)
MainRecID (FK)
DeviceID (up to 4 records for each MainRecID)
DefaultCommandID (FK)

tblDefCmd
RecordID (PK)
Command

[jlreis28]

Thanks. I gave up and made four tables, populated by queries, then ran an append query to combine everything. It isn't the cleanest way to do this but it is getting the job done. There is only going to be about 1000 records and this is a one-time use program.

Thank you to everyone who replied.

Justin