Crate multiple records in another form with a push of a botton

[kimkj]

Hi All



Im trying to create a traceability database and i stumple on a bit of a problem that i how some of you can help me with.

I have the following tables
Order table
Assembly table
Part table
EXC table
traceability table

Now, when i create a new order, i can through that create a new assembly. From the assembly table i can then create a part.

In the part form it now indicates what order the part belongs to and what assembly.
Now i need to create traceability for the part. I have in the assembly indicated how many assemblies (lets say 10) is in the order, and through the part form i indicate how many of this specific part (lets say 10) is in 1 assembly. By this i have a field that calculte how many parts i need to create to the complete order (10x10=100).

In the offshore buisiness all parts needs to be traced, so when i choose EXC3 or EXC 4 from a combo box, a botton needs to be active. When i click this botton i want to be able to create the amount of new part records based on the calculated field in the part form. For example if the calculated field indicates 100, the 100 part records will be created. All related to the order, and Assembly. If i choose EXC 2, the botton is inactive.
Further to this, each of the new parts needs to get a serial number that i specify on the part form. for example (288-3000) som the first part will be named 288-3000-001, 288-3000-002 and so on.
After creating the traceability set, if a user push the bottom againg, then text box saying that you cannot create more components than indicated in the calculated field.

If however, that 1 assembly is deleted from the order so only 9 asseblies is in the order, the calculated field in the part form will now indicate 90 pcs. for the complete order, i wante the traceability to update by going to the end of the record set, and delete serial 91-100. But if i ad one assembly to the order, then it needs to amend 10 parts.


I know this is a lot, but hopefully someone can help.

Thank you in advance.

[June7]

Use a loop structure to execute multiple record inserts. Something like:

For x = 1 to Me.tbxAmt
'code to create record
Next

Code can execute an SQL INSERT action statement or add record to an already open recordset object with AddNew and Update methods.

Common topic. Should be many examples. Search "access vba append multiple records".

[kimkj]

Hi June

Thanks for your fast reply,

I should maby say that I'm not a skilled VBA writer. I'm taking a cause while creating the database. This however is a bit over my capabilities.