Create record in TWO tables using one FORM

[kealaz]

Hello,

Can I write to two tables using one form? I have a form to add a new part
into our parts system. This form is currently writing to my Parts Log
[tblPARTSLOG].

tblPARTSLOG
PART_NO
DESCRIP
NAME
MANUF
MANUF_PN

I would like it to also create a record in my Inventory Log [tblNVENTORY].

tblNVENTORY
PART_NO
NAME
NO_ON_HAND
COST
VALUE


For the time being, I would like the record in tblNVENTORY to be created
with PART_NO and NAME, the same as entered in the form, with values for the 3
remaining fields to be "0", so that I can go into that table at a later time
and change those values to reflect the amount we have on hand.


Any help with this would be greatly appreciated.

Thanks a bunch!!!

 

[Arvin Meyer MVP]

First of all, you do not need to store the NAME field in 2 tables.

Next, a subform for tblInventory and linked on PART_NO is all that's
required to write to that table.

 

[kealaz]

Thanks Arvin! I appreciate you taking the time to help.

When I enter info into the main form, nothing is being saved from the
subform.... unless I enter the subform and re-type the default value. If I
type in the subform, it does make the entry.

Right now, if I tab into the form and tab out w/o typing there is no entry
in tblNVENTORY. If I tab into the form and type in ONE control, then the
entry IS made. Is there a way to automate this process?

I want to eventually make the visibilty on the subform "no", so that the
entry is made in tblNVENTORY automatically, and the user doesn't even see the
subform.


Is what I'm trying to do possible?

Is using a subform the best way? or Is there an easier way?

Thank you very much for any help you can provide!!!

 

[kealaz]

I have this working now. Thanks so much for your help!!!

 

[Bob Quintal]

Thanks Arvin! I appreciate you taking the time to help.

When I enter info into the main form, nothing is being saved from
the subform.... unless I enter the subform and re-type the default
value. If I type in the subform, it does make the entry.

Right now, if I tab into the form and tab out w/o typing there is
no entry in tblNVENTORY. If I tab into the form and type in ONE
control, then the entry IS made. Is there a way to automate this
process?

I want to eventually make the visibilty on the subform "no", so
that the entry is made in tblNVENTORY automatically, and the user
doesn't even see the subform.


Is what I'm trying to do possible?

Is using a subform the best way? or Is there an easier way?

Thank you very much for any help you can provide!!!

It's possible, but it's wrong. Your inventory table should be set up
in a separate form, with the table containing the fields

tblinventoryTransactions.
PART_NO
TRANSACTION_TYPE
TRANSACTION_DATE
TRANSACTION_QTY
PRICE_PER

You add a row when you add parts and set the TRANSACTION_TYPE to
"ADD"
You add a row when you remove parts and set the TRANSACTION_TYPE to
"USE"

You get current inventory by summing the clculation
TRANSACTION_QTY*IIF(TRANSACTION_TYPE = "ADD", 1,-1)

That way you save the proper price_per for each time you add
inventory because the price is often changing.

If you insist on doing it your way, use code to execute an Append
query that uses the PART_NO textbox on the form as a value.
Your tblINVENTORY doesn't need to be on the form at all.
Better yet, if you insist on doing it your way, use EXCEL.