writing to 2 tables

[gray78]

I have a form and I want the data input to be stored in two tables, a history table and an inventory table.

Can this be do and if so how??

Thanks

 

[willir]

Yes it can be done.

But we need to know...
- name of fields in the history and inventory table
- name of the controls (text, combo and list boxes) on the form.

 

[gray78]

History table fields
tag #,Facility,Location,Category,Item Description,Manufacturer,Model Name, Model number,
serial number, Status,Installed,Last Update,
Remarks

Inventory Table Fields:
Location, Category, Item Description, Manufatuer, Model Name,Model number, serial number, last ipdate, remarks.

 

[gray78]

combo box controls
SELECT History.[Tag #], History.[Item Description] FROM History;

 

[willir]

I have a form and I want the data input to be stored in two tables, a history table and an inventory table

I suspect you want to do write data to the "history" table after creating an entry for the inventory from a form -- correct?

You did not provide the names of the controls on the form, so I will have to assume they match

One approach (there are many other ways)

Use the AfterUpdate event procedure. Open the form in design mode, and make sure the "Properties" window is open (from the menu, "View" -> "Properties").

Select the form by clicking in the square box located the top left corner where the verticle and horizontal rulers meet. Select the "Event" tab on the Properties window, and click on the field next to the "AfterUpdate". From the drop-down list, select "EventProcedure" and then click on the "..." command button that appears to the right. This will open up the VBA coding window.

I am going to use the RecordSet approach since there is uncertainty if some fields are numeric or text or date. (Although an SQL Insert statement is more effecient)

Assumptions:
- The names provided are correct
- Inventory is name of Inventory table
- History is name of History table

Enter the following...

Dim rst as DAO.Recordset

'Open the History table
Set rst = Currentdb.OpenRecordset("History")

'Take the Inventory info on the form and
'use it to insert a record in the history table

With rst
   .AddNew
      !Location = Me.Location
      !Category = Me.Category
      ![Item Description] = Me. [Item Description]
      !Manufacturer = Me.Manufacturer 
      ![Model Name] = Me.[Model Name]
      ![Model number] = Me.[Model number]
      ![serial number] = Me.[serial number]
      ![Last Update] = Me.[last update]
   .Update

   .Close

End rst

Set rst = Nothing

If you get an error with the above, you may be missing the DAO reference. Add it by...
Alt-F11 to open the VBA coding window
"Tools" -> "References"
Verify / Add entry "Microsoft DAO 3.6 Object Library" (or ver 3.5)

There is a problem with the above code![/code]

I have no idea how you intend to create the entries for...
[tag #]
Facility
Status
Installed

If you want to go the other way, take history and write to the inventory table, just change the code to accommodate. Change the table opened from History -> Inventory; change the fields, if and when required, to accommodate the fields in the Inventory table.

Richard