Form Needs to Store Multiple Values Without Creating A New Record

[SeaTigr]

Greetings all,

Thank yous to everybody who has helped an Access newbie work on this project.

My latest problem:

I have a form with the system name, serial number, etc. I have an embedded form with patch #, patch date, and the patch generating organization. The embedded form is linked to a different table than the main form.

I would like users to be able to enter a patch # and patch date, and have the patch table to create a new row, but the main form not create a new record. So, if a new patch # and date is entered, the new # and date will be displayed on the form, while the new # & date, as well as the old #s and dates, will be stored in the # and date table.

The idea is that we need to keep a record of which patches were applied, and when they were applied, but most users will only need to know which patch is currently on the machine.



Currently, if I enter a new patch # and date, the new data is saved in place of the old in the table.

[jzwp11]

Since a system can have many patches that describes a one(system)-to-many(patches) relationship. Is that how you have the two tables structured?

Can you provide your table structure?

[SeaTigr]

Is this what you mean?

Table: Equipment
UnitID [primary key]

Table: Patch # and Date
PatchID
UnitID
Unit Patch #
Unit Patch Date

[jzwp11]

Yes partly. In the relationship window, is the join line going from the UnitID field of the Equipment table to the UnitID field of the patch table?

By the way, what is the datatype of the UnitID field in the patch table? It has to be a long number integer datatype field in order to match the autonumber datatype of the UnitID field of the equipment table. (I assume that you set the primary key as an autonumber).

Also, as a general recommendation, it is best not to have spaces or special characters (#, !, $ etc.) in your table or field names

[SeaTigr]

The PatchID is the autonumber. UnitID in the Patch table wasn't set as a number, but as text. It is set as a long number integer now.

[SeaTigr]

Also, the join line does go from UnitID to UnitID.

[jzwp11]

You will need to verify that you have a one-to-many relationship between the two tables by clicking the join line in the relationship window and making sure that the type is one-to-many. Make sure to also enforce referential integrity.

If the form still does not work properly, then you will have to double check that the form and subform are linked corrrectly. From design view of the main form, right click on the frame of the subform, open the property sheet go to the data tab and look for the Link Master Fields and Link Child Fields. Both should have unitID

[SeaTigr]

I verified a one-to-many relationship. The first table is the equipment table, the related table is the patch # and date table. Enforce referential integrity is now checked. Cascade update related fields, cascade delete related records are both unchecked. A '1' appears on the side of the join line next to the equipment table box, an infinity sign appears next to the patch # and date table box.

Both link master fields and link child fields show UnitID.

changing the patch number and patch date still modifies the fields in the row in the patch # and date table, rather than creating a new row for the same unitID.

[jzwp11]

OK, I'm not sure what is going on. Can you zip and post your database and we can take a look? Make sure to remove any sensitive data before posting.

[SeaTigr]

Here is my db. All sensitive information should be sanitized from it.

Thank you.

[jzwp11]

The record navigation buttons in the subform were turned off, so you could not migrate to the next record. I turned the navigation buttons back on, now you can move between the records in the subform. If you want to see all of the patch records at one time in the subform, you can change the subform from single form view to datasheet view. I've attached the database with this modification.

Also, I noticed that you had a lookup at the table level (the PIF System table--the system name field). Although Access has this capability, it generally causes a lot of problems as detailed in this link. I would leave the lookups (combo and list boxes) to your forms. I did not make this change in the attached database.

[SeaTigr]

That was a terrific help. I can't thank you enough.

I removed the lookups for the system name and system classification fields.

[jzwp11]

You're welcome.