Using a single entry on a form to add two records

[Tom123]

Hi,



I have two tables; one is called SERIAL NUMBERS and the other is called SN HISTORY.

SERIAL NUMBERS has information on parts in an inventory with unique serial numbers which includes the S/N, description, and current location. It is important to know where the part is.

SN HISTORY needs to track the location changes in the serial numbered part. The fields are S/N, date and location.

For example if I have a part with the serial number KG005 and it's in Boston and then I send it to New York then I want the current location in the SERIAL NUMBERS to be New York and in the SN HISTORY table I want two records. The first says it was in Boston on date A and in New York on date B.

I want to have a form for changing the location where you input the new location, date and serial number. The form would have to query the SERIAL NUMBERS table and look for the serial number that the user inputs. Then it would replace the current location in that table with the new location in the form. It would also add all of the things entered in the form as a new record in SN HISTORY. So my question is: is it possible to link one entry in a form to two fields in two different tables?

[ItsMe]

That sounds like a good approach. Have you created the table? Did you define the relations and key fields? I don't really see a question here. I would start with the table. I would then build a form that will populate the table, worrying how said form will interact with the app at a later date.

[June7]

Is this an inventory tracking? You need to show reduction of inventory in Boston and the increase in New York? If this is not inventory of expendables, just tracking location of asset, then only need to input single record in SN HISTORY to document the new location and date of transfer. There should already be a record showing asset installed in Boston so therefore it must have come from Boston.

Regardless, should not record the current location in SERIAL NUMBERS. The current location can be determined by pulling data from the SN HISTORY table. This can be done with a TOP N query http://allenbrowne.com/subquery-01.html#TopN or DLookup domain aggregate expression.

Otherwise, code would have to run INSERT and UPDATE sql actions.

[Tom123]

I figured out how to remove the current location from SERIAL NUMBERS. I just put a totals in the query and set it to display last. I would still like one form to input the serial numbers for the first time. How do I make one field put the same data in two records? I cant figure it out.

[June7]

Input record in SERIAL NUMBERS and run code that checks for record in SN HISTORY, if none for that asset, then run INSERT sql.

Or use form/subform arrangement.

Enter new asset info on main form (SN, description, etc.)

Then in the subform enter the location info.

Can use this same form/subform to enter new location record for existing asset.