Using SUBFORM to create record in second table from form

[kealaz]

I have a form to enter new parts into our Parts Log system. When a new part
is entered, I would like a new entry to be created into TWO different tables.


tblPARTSLOG

and

tblNVENTORY

The information that the user is providing (for the new part) will only be
written to tblPARTSLOG.

tblPARTSLOG
PART_NO
DESCRIP
NAME
MANUF
MANUF_PN

When a new part is created, I also need a record to be created in
tblNVENTORY. Other than the PART_NO, which is how the tables are related,
all of the other fields should get the default value of 0.

tblNVENTORY
PART_NO
NO_ON_HAND
COST
VALUE


I have created a form, frmPARTSLOG, and have a subform in it, frmNVENTORY01.
All of the controls (text boxes) in my subform have the default values set
to the values that I want entered into the table (tblNVENTORY)


Problem:
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!!!

 

[Wayne-I-M]

Hi

There is a basic design item for access databases - each field should
contain only one item of information and this item should be unique. Of
course you can have more tha 1 John and more than 1 Jim in a names table but
in this case it is still unique as it refers to a secific record.

If you store you data in more than one table you are risking messing up the
whole thing. Sorry but thats just how it is.

As an example. in you table tblNVENTORY you have PART_NO you could add
COST, VALUE in your tblPARTSLOG.
I think that NO_ON_HAND is a calculated field (never a good idea). You
could have the results of Item in - Item out (for example) in a query and
call it NO_ON_HAND:### - ###

If you really need o have access to this data in more than one place then
create a speperate table for it and link this (relate) to other tables that
have a relationship with the data.

 

[kealaz]

Hi Wayne,

Thank you for replying. I do have this information in seperate tables, and
they are linked / related. One table is a list of the parts we have, while
the other table, linked on PART_NO, a unique identifier, will have inventory
information. I'm not sure I understand what your suggestion is. Please
specify.

Thank you again for your time and assistance.

 

[Wayne-I-M]

Main point I was getting at is that you don't need to create a new record in
the linked table. This will be done anyway as soon as you add the Part then
then linked table will be able to acecpt data that s linked. If nothing is
added to your second table the the record isn't needed so will not be created
- I think you are missunderstanding how the relationships work.

But
From the information you have given is seems to me that you ony need one table

As an example - if you have
PartNo in your Parts table
and you have a salesman table
These tow are linked by a Sales Table (SalesID - PartNo - SalesmanID)
Then a record would be ccreate in this (Sales table) as soon you you have a
sale

Hope that makes sense you don't need to worry about"cteating" a record as
it will anyway
--
Wayne
Manchester, England.

Hi Wayne,

Thank you for replying. I do have this information in seperate tables, and
they are linked / related. One table is a list of the parts we have, while
the other table, linked on PART_NO, a unique identifier, will have inventory
information. I'm not sure I understand what your suggestion is. Please
specify.

Thank you again for your time and assistance.

 

[kealaz]

THANK YOU STEVE!!! This works perfectly!

 

[John Spencer]

It is certainly possible to automatically create a new record in
tblInventory based on an entry on the form.

I would use the after update event of the form to check for the
existence of the needed record in tblInventory and add one if it did not
exist. UNTESTED SAMPLE CODE follows

Private Sub Form_AfterUpdate()
Dim dbAny as DAO.Database
Dim StrSQL as String


If Len(Me.Part_no & "") > 0 then

IF DCount("*","tblInventory","Part_no=""" & Me.Part_NO & """) <1 Then

Set dbAny = CurrentDb()

StrSQL = "INSERT INTO tblInventory (Part_No) " & _
" VALUES (""" & Me.Part_No & """)"

Dbany.Execute strSQL, dbFailOnError

END IF
End If

End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================