J
Joan
I have a problem, I have tried everything I know but
haven't been able to fix. Hope someone out there can
help.
I have 4 tbles. POHeader, POLines - linked on PONumber.
ReceiptHeader and ReceiptLines - ReceiptHeader PrimaryKey
is ReceiptNo and POHeader is linked by PONumber. Here
is what I'd like to do.
ON POForm there is cmd button to run DoCmd SQL statements,
Insert INTO ReceiptHeader and a separate one to Insert
INTO ReceiptLines. It's working for me but I can't
update the ReceiptNo from the Header into the Lines
table - I need to do this so I can use for other
functions and to create the link between Header and Lines
tbls for form (I do it now with a qry but I am now
limited in updates to other tables because of this).
My problem: a PO could have more then one receipt. If it
does then when I try to update the receipts lines tables
with the receipt no from header it duplicates rows in the
lines table. Please anyone who can help....
My code is as follows:
Dim SQLReceipt As String
Dim SQLReceiptLine As String
SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber " & _
"FROM tblPOHeader " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= '" & Me!PONumber & "')
AND (([tblPOHeader]![Post])<>True))"
DoCmd.RunSQL SQLReceipt
'SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
PONumber, ItemDesc, OnOrder ) " & _
'"SELECT tblPOLine.Description, tblPOLine.PONumber,
tblItemMaster.Description, tblPOLine.QtyOrdered " & _
'"FROM tblPOLine INNER JOIN tblItemMaster ON
tblPOLine.Description = tblItemMaster.ItemNo " & _
'"WHERE (((tblPOLine.PONumber)= '" & Me!PONumber & "'))"
DoCmd.RunSQL SQLReceiptLine
(The above is remarked out because I've been trying
different things on trying to get the receiptno updated
based ont he new receiptno created in the sql statement
above - this is where I run into difficulty - I can't do
it without creating duplicate records in receiptlines)
HELP!!!
haven't been able to fix. Hope someone out there can
help.
I have 4 tbles. POHeader, POLines - linked on PONumber.
ReceiptHeader and ReceiptLines - ReceiptHeader PrimaryKey
is ReceiptNo and POHeader is linked by PONumber. Here
is what I'd like to do.
ON POForm there is cmd button to run DoCmd SQL statements,
Insert INTO ReceiptHeader and a separate one to Insert
INTO ReceiptLines. It's working for me but I can't
update the ReceiptNo from the Header into the Lines
table - I need to do this so I can use for other
functions and to create the link between Header and Lines
tbls for form (I do it now with a qry but I am now
limited in updates to other tables because of this).
My problem: a PO could have more then one receipt. If it
does then when I try to update the receipts lines tables
with the receipt no from header it duplicates rows in the
lines table. Please anyone who can help....
My code is as follows:
Dim SQLReceipt As String
Dim SQLReceiptLine As String
SQLReceipt = "INSERT INTO tblReceipts ( SupplierID,
PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber " & _
"FROM tblPOHeader " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber,
[tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= '" & Me!PONumber & "')
AND (([tblPOHeader]![Post])<>True))"
DoCmd.RunSQL SQLReceipt
'SQLReceiptLine = "INSERT INTO tblReceiptsLine ( ItemNo,
PONumber, ItemDesc, OnOrder ) " & _
'"SELECT tblPOLine.Description, tblPOLine.PONumber,
tblItemMaster.Description, tblPOLine.QtyOrdered " & _
'"FROM tblPOLine INNER JOIN tblItemMaster ON
tblPOLine.Description = tblItemMaster.ItemNo " & _
'"WHERE (((tblPOLine.PONumber)= '" & Me!PONumber & "'))"
DoCmd.RunSQL SQLReceiptLine
(The above is remarked out because I've been trying
different things on trying to get the receiptno updated
based ont he new receiptno created in the sql statement
above - this is where I run into difficulty - I can't do
it without creating duplicate records in receiptlines)
HELP!!!