Simple Solution?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a bit of a problem with linked tables. If anyone can bear with this
description and provide some help I would be most appreciative.

I suspect the solution is quite simple but I cannot find it!
---

I have the following tables in place:
tblJobRequirementsDetail,
tblPurchaseOrder, tblPurchaseOrderDetail
tblIssueStockOrder, tblIssueStockOrderDetail

All 'Detail' tables are linked to a central products table.

Users open a form and enter some products and required quantities.
This info is saved in tblJobRequirementsDetail.

I then have some code that uses five record sets (rsMain, rsPOdetail, rsPO,
rs IOdetail and rsIO)

rsmain is the recordset of current required products.

I then have some code that loops through the required products and checks
the stock for each one.
if the item is in stock I want to add it to a new issueorder and if it is
out of stock,
I want to add it to a new purchase order

Therefore I am using rs.addnew.fields to add records to the rsPOdetail
recordset (and similar for rsIOdetail) but it wont let me do this because a
related record is required in the main purchase order table.

to combat this I created a new record in the Purchase Order table and set
the linked field (ie the POid) to that of a main PO record, but it still
gives an error.
---
an example of the code where the error is is as follows:

With rsIOdetail

.AddNew
.Fields("IssueStockOrderID") = rsIO("IssueStockOrderID")
.Fields("ProductID") = rs("ProductID")
.Fields("QtyToIssue") = rs("QtyReq")
.Fields("StockLocationID") = 1
.Update 'ERROR HERE!

End With


can anyone help by suggesting a better way,
any advice offered would be greatly appreciated.
 
Sounds like you have referential integrity turned on. When your using RI
and are dealing with records in a one to many relationship, a record
MUST exist on the 'ONE' side before you can add records to the table on
the 'MANY' side. You'll need to first add a record to the purchase order
table, before you can add detail records.
If I read the post correctly...

First
Use DAO to add the record to the purchase order table. Right before the
..Update statement, capture the value for the record's key into a variable.

purchaseOrderKey = rs(keyFieldName)


Second
Use DAO to add the detail records to the appropriate table, be certain
that you have a statement that sets the foreign key (the value in the
detail table that relates back to the PO table) using the value captured
above.

rs(foreignKeyFieldName) = purchaseOrderKey
..Update

That should do it.
 
I did actuallly realise this (and was trying to do it this way),But...
I didnt do a '.update' on the master records so I wasnt actually adding the
record.

Thank you for reading through and replying to my (rather long) post.

Much appreciated,
cheers,
Kenny
 
Back
Top