Adding a new Record and updating mutiple Tables

[LouD]

I have 5 tables in my db and when i bring up the linked form I get my
product lists

Tbls are tbStock, which contain unitcost, sellprice n qty
tblProduct, contains ID, medicalname n genericname
tblCat contains ID, catname and catcode
tblPacket which ID, contains packetcode and description
tblSize which ID, contains the size code and description

the form has the StockID(hidden), catname(combo), medicalname,
genricname, packdescription(combo), unicost, sellprice n qty

2 buttons add and save record.

when the form opens i can see all the dat in my tables.

when add button is clicked the table emties and i can key in new details
but i cant write to the cost,sell n qty fiels dont allow me to key in
the detail, and when i click the save button the all other info is
created as new records in the all tables but the price and qty info is not.

Following is the code that the wizard wrote:

Option Compare Database

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:

Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub
Private Sub cmdAddNewSave_Click()
On Error GoTo Err_cmdAddNewSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdAddNewSave_Click:
Exit Sub

Err_cmdAddNewSave_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewSave_Click

End Sub

[Larry Linson]

I don't see any foreign key fields to relate the tables. I'd guess that
tbStock, or a Query including it, is RecordSource for the Form, and, perhaps
we can assume that unitcost, sellprice, and qty are Control Source for Text
Boxes, but for the Combos it appears you are indicating RowSource, not
Control Source. If you could clarify, it'd help someone to help you.

Larry Linson
Microsoft Access MVP

"LouD" <du******@yahoo.com> wrote in message
news:WC********************@news-server.bigpond.net.au...

I have 5 tables in my db and when i bring up the linked form I get my
product lists

Tbls are tbStock, which contain unitcost, sellprice n qty
tblProduct, contains ID, medicalname n genericname
tblCat contains ID, catname and catcode
tblPacket which ID, contains packetcode and description
tblSize which ID, contains the size code and description

the form has the StockID(hidden), catname(combo), medicalname,
genricname, packdescription(combo), unicost, sellprice n qty

2 buttons add and save record.

when the form opens i can see all the dat in my tables.

when add button is clicked the table emties and i can key in new details
but i cant write to the cost,sell n qty fiels dont allow me to key in
the detail, and when i click the save button the all other info is
created as new records in the all tables but the price and qty info is not.
Following is the code that the wizard wrote:

Option Compare Database

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:

Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub
Private Sub cmdAddNewSave_Click()
On Error GoTo Err_cmdAddNewSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdAddNewSave_Click:
Exit Sub

Err_cmdAddNewSave_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewSave_Click

End Sub

[LouD]

I am not sure how I can describe this, if its ok with u I could email yu
my db

Larry Linson wrote:

I don't see any foreign key fields to relate the tables. I'd guess that
tbStock, or a Query including it, is RecordSource for the Form, and, perhaps
we can assume that unitcost, sellprice, and qty are Control Source for Text
Boxes, but for the Combos it appears you are indicating RowSource, not
Control Source. If you could clarify, it'd help someone to help you.

Larry Linson
Microsoft Access MVP

"LouD" <du******@yahoo.com> wrote in message
news:WC********************@news-server.bigpond.net.au...

I have 5 tables in my db and when i bring up the linked form I get my
product lists

Tbls are tbStock, which contain unitcost, sellprice n qty
tblProduct, contains ID, medicalname n genericname
tblCat contains ID, catname and catcode
tblPacket which ID, contains packetcode and description
tblSize which ID, contains the size code and description

the form has the StockID(hidden), catname(combo), medicalname,
genricname, packdescription(combo), unicost, sellprice n qty

2 buttons add and save record.

when the form opens i can see all the dat in my tables.

when add button is clicked the table emties and i can key in new details
but i cant write to the cost,sell n qty fiels dont allow me to key in
the detail, and when i click the save button the all other info is
created as new records in the all tables but the price and qty info is

not.

Following is the code that the wizard wrote:

Option Compare Database

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:

Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub
Private Sub cmdAddNewSave_Click()
On Error GoTo Err_cmdAddNewSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,

acMenuVer70

Exit_cmdAddNewSave_Click:
Exit Sub

Err_cmdAddNewSave_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewSave_Click

End Sub

[LouD]

Larry

Thanks for your assitance I have now resolved the problem as your
question triggered the answer, when i created the forms using the
multiple table i omited using the id field from the secondary tables.
only using the fields I wanted in the form. Consequently there was no
link to the control IDs. I deleted the form and redisgned it by dropping
all the fields from the 5 tables and then deleted the the fields i didnt
want visible. This has now solved the problem
Thanks and regards
Louis

Larry Linson wrote:

I don't see any foreign key fields to relate the tables. I'd guess that
tbStock, or a Query including it, is RecordSource for the Form, and, perhaps
we can assume that unitcost, sellprice, and qty are Control Source for Text
Boxes, but for the Combos it appears you are indicating RowSource, not
Control Source. If you could clarify, it'd help someone to help you.

Larry Linson
Microsoft Access MVP

"LouD" <du******@yahoo.com> wrote in message
news:WC********************@news-server.bigpond.net.au...

I have 5 tables in my db and when i bring up the linked form I get my
product lists

Tbls are tbStock, which contain unitcost, sellprice n qty
tblProduct, contains ID, medicalname n genericname
tblCat contains ID, catname and catcode
tblPacket which ID, contains packetcode and description
tblSize which ID, contains the size code and description

the form has the StockID(hidden), catname(combo), medicalname,
genricname, packdescription(combo), unicost, sellprice n qty

2 buttons add and save record.

when the form opens i can see all the dat in my tables.

when add button is clicked the table emties and i can key in new details
but i cant write to the cost,sell n qty fiels dont allow me to key in
the detail, and when i click the save button the all other info is
created as new records in the all tables but the price and qty info is

not.

Following is the code that the wizard wrote:

Option Compare Database

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:

Exit Sub

Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click

End Sub
Private Sub cmdAddNewSave_Click()
On Error GoTo Err_cmdAddNewSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,

acMenuVer70

Exit_cmdAddNewSave_Click:
Exit Sub

Err_cmdAddNewSave_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewSave_Click

End Sub