Better way to update two tables ?

[TD]

I need help understanding a better way to do this. I have an unbound
form named frmParts that has two subforms neither of which is bound to
frmParts. One subform is named frmSubParts and it's record source is
tblParts. The other subform is named frmSubAssemblies and it's record
source is tblAssemblies.

tblParts
RecID AutoNumber Primary Key
VendorPartNumber Text
ProtecPartNumber Text
PPN_Number Number
Description Text
Assy Yes/No

tblAssemblies
RecID AutoNumber Primary Key
Part_RecID Number Long
VendorPartNumber Text
ProtecPartNumber Text
Description Text
Qnty Number
I have a hidden text field named txtLink, who's control source is set
to "=Forms!frmParts!frmSubParts!RecID". On frmSubAssemblies I have the
Link Master Fields set to txtLink and the Link Child Fields set to
Part_RecID.

The purpose of this form is to setup part and assemblie information.
An assemblie is made up of two or more parts.

What I have now works but I'm looking for a better way to do this. I
have code that adds the part info to tblParts then retrieves the RecID
of the new part and then it writes the info into tblAssemblies;
tblParts.RecID gets written to tblAssemblies.Part_RecID.

Is there a way to use a query to do all of this?

Here is the code under the ADD button:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim AppPath As String
Dim sql As String

If Me.chkAssy = True Then

If Nz(Me.txtPPN, "") = "" Or Nz(Me.txtDesc, "") = "" Or
Nz(Me.cboParts, "") = "" Or Nz(Me.txtQnty, "") = "" Then
MsgBox "Please fill in the appropriate fields!", vbOKOnly,
"WAIT!"
Me.txtVPN.SetFocus
GoTo Exit_cmdAdd_Click
End If

sql = ""
sql = sql & "INSERT INTO tblParts (VendorPartNumber,
ProtecPartNumber, PPN_Number, Description, Assy) "
sql = sql & "SELECT UCase(Forms!frmParts!txtVPN),
UCase(Forms!frmParts!txtPPN), "
sql = sql &
"CLng(Right(Forms!frmParts!txtPPN,Len(Forms!frmPar ts!txtPPN)-1)), "
sql = sql & "Forms!frmParts!txtDesc, Forms!frmParts!chkAssy;"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

sql = ""
sql = sql & "SELECT tblParts.RecID FROM tblParts WHERE "
'sql = sql & "(((tblParts.VendorPartNumber)= '" &
[Forms]![frmParts]![txtVPN] & "') AND "
sql = sql & "(((tblParts.ProtecPartNumber)= '" &
[Forms]![frmParts]![txtPPN] & "'));"
'sql = sql & "((tblParts.Description)= '" &
[Forms]![frmParts]![txtDesc] & "'));"

AppPath = CurrentDb.Name
Set db = OpenDatabase(AppPath)

Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

sql = ""
sql = sql & "INSERT INTO tblAssemblies (Part_RecID,
VendorPartNumber, "
sql = sql & "ProtecPartNumber, Description, Qnty) VALUES "
sql = sql & "('" & rst(0) & "', '" & Me.cboParts.Column(1) &
"', '" & Me.cboParts.Column(2) & "', "
sql = sql & "'" & Me.cboParts.Column(3) & "', '" & Me.txtQnty &
"');"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

rst.Close
db.Close

Me.txtVPN = ""
Me.txtPPN = ""
Me.txtDesc = ""
Me.chkAssy = False
Me.cboParts = ""
Me.txtQnty = ""

Forms![frmParts]![frmSubParts].Requery
Forms![frmParts]![frmSubAssemblies].Requery

GoTo Exit_cmdAdd_Click

ElseIf Me.chkAssy = False Then

If Nz(Me.txtPPN, "") = "" And Nz(Me.txtDesc, "") = "" And
Nz(Me.cboParts, "") = "" And Nz(Me.txtQnty, "") = "" Then
MsgBox "Please fill in the appropriate fields!", vbOKOnly,
"WAIT!"
Me.txtVPN.SetFocus
GoTo Exit_cmdAdd_Click
End If

If (Nz(Me.txtPPN, "") <> "" And Nz(Me.txtDesc, "") <> "") Then

sql = ""
sql = sql & "INSERT INTO tblParts ( VendorPartNumber,
ProtecPartNumber, PPN_Number, Description ) "
sql = sql & "SELECT Forms!frmParts!txtVPN AS VPN,
Forms!frmParts!txtPPN AS PPN, "
sql = sql &
"CLng(Right(Forms!frmParts!txtPPN,Len(Forms!frmPar ts!txtPPN)-1)) AS
PPN_N, "
sql = sql & "Forms!frmParts!txtDesc AS [Desc];"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

rst.Close
db.Close

Me.txtVPN = ""
Me.txtPPN = ""
Me.txtDesc = ""
Me.chkAssy = False
Me.cboParts = ""
Me.txtQnty = ""

Forms![frmParts]![frmSubParts].Requery
Forms![frmParts]![frmSubAssemblies].Requery

GoTo Exit_cmdAdd_Click

End If

If Nz(Me.cboParts, "") <> "" And Nz(Me.txtQnty, "") <> "" Then

If Forms![frmParts]![frmSubParts]![Assy] = False Then
MsgBox "This part is not an assembly", vbOKOnly,
"WAIT!"
GoTo Exit_cmdAdd_Click
End If

sql = ""
sql = sql & "INSERT INTO tblAssemblies (Part_RecID,
VendorPartNumber, "
sql = sql & "ProtecPartNumber, Description, Qnty) VALUES "
sql = sql & "('" & Forms![frmParts]![frmSubParts]![RecID] &
"', '" & Me.cboParts.Column(1) & "', "
sql = sql & "'" & Me.cboParts.Column(2) & "', '" &
Me.cboParts.Column(3) & "', '" & Me.txtQnty & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

rst.Close
db.Close

Me.txtVPN = ""
Me.txtPPN = ""
Me.txtDesc = ""
Me.chkAssy = False
Me.cboParts = ""
Me.txtQnty = ""

Forms![frmParts]![frmSubAssemblies].Requery

GoTo Exit_cmdAdd_Click

End If

End If

Exit_cmdAdd_Click:
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

[John Welch]

Well, to start, it looks to me like you need three tables instead of two:
tblParts stores information about a particular part, like part numbers and a
description
tblAssemblies stores info about a particular assembly, again 'part' (i.e.
assembly) numbers and a description
tblPartsInAssemblies will store info about which parts are in which
assemblies, and quantities.

for example:

tblParts:
partID description
1 "screw"
2 "bolt"

tblAssemblies:
assemblyID description
1 'widget'
2 'gadget'

tblPartsInAssemblies
partID AssemblyID quantity
1 1 2
2 1 3
1 2 4

now with subforms you can see which parts belong to a particular assembly or
which assemblies include a particular part

"TD" <dl**@adsi-sc.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

I need help understanding a better way to do this. I have an unbound
form named frmParts that has two subforms neither of which is bound to
frmParts. One subform is named frmSubParts and it's record source is
tblParts. The other subform is named frmSubAssemblies and it's record
source is tblAssemblies.

tblParts
RecID AutoNumber Primary Key
VendorPartNumber Text
ProtecPartNumber Text
PPN_Number Number
Description Text
Assy Yes/No

tblAssemblies
RecID AutoNumber Primary Key
Part_RecID Number Long
VendorPartNumber Text
ProtecPartNumber Text
Description Text
Qnty Number
I have a hidden text field named txtLink, who's control source is set
to "=Forms!frmParts!frmSubParts!RecID". On frmSubAssemblies I have the
Link Master Fields set to txtLink and the Link Child Fields set to
Part_RecID.

The purpose of this form is to setup part and assemblie information.
An assemblie is made up of two or more parts.

[TD]

John Welch (remove remove) wrote:

Well, to start, it looks to me like you need three tables instead of two:

Thanks John for the reply. That's an interesting thought, I'll take a
look at it, but I guess what I really was wondering is this.

I have a one-to-many relationship setup based on tblParts.RecID and
tblAssemblies.Part_RecID.

The way the code is now I have to add the info to tblParts and save the
record before the autonumber is generated for RecID. Then I have to
search tblParts for the part I just added to retrieve the RecID for
that part. Then I have to add the parts to tblAssemblies inputting
tblParts.RecID into tblAssemblies.Part_RecID.

What I would really like is to use a query to add the data to both
tables and populate the linking fields (tblParts.RecID and
tblAssemblies.Part_RecID) without me having to populate them manually.

Any ideas?

[TD]

Anyone?