Update query

[geno]

Hi,
I have an update query which when run will update a stock field. The problem I'm having is only one record is getting updated. Here's my code:
Private Sub cmdReceive_Click()
If Me.PORec.Value = False Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateStockRec", acViewNormal, acEdit
DoCmd.SetWarnings True
Me.PORec.Value = True
Else
MsgBox "This purchase order has already been received. If you need to adjust stock please do so by going to Database Maintenance."

End If

End Sub


The update query is looking at the PartID in a subform. There are 3 records in this subform and only the first record gets updated.
Thanks

 

[stubailey007]

Looks fine, can you post the SQL in your query?

Stu

 

[geno]

Here is the query sql:

UPDATE Parts SET Parts.[In Stock] = [In Stock]+Forms!frmPOOrdersRec!frmPOOrdersRecSubform.Form!Received
WHERE (((Parts.PartID)=[Forms]![frmPOOrdersRec]![frmPOOrdersRecSubform].[Form]![PartID]));

 

[stubailey007]

Is PartID a unique field?

Because '(((Parts.PartID)=[Forms]![frmPOOrdersRec]![frmPOOrdersRecSubform].[Form]![PartID]));' is saying where partid is the part id in the subform. Are you listing multiple parts in the subform and you are finding this is only updating the selected one?

Or is there only one part in the sub form?

Can you explain what the forms contain?

Stu

 

[geno]

Hi,
Thanks for your reply, I think I've figured out where I went wrong in the query. I've added the purchase order table and the details table and have referenced the purchase order ID. Still testing it but looks like it will work now.
Thanks again for your input.
Geno

 

[geno]

Opps thought I was onto the problem, but now the records get updated to the first records changes. Here's what I have for sql in the update query now:

UPDATE Parts INNER JOIN [PO Order Details] ON Parts.PartID = [PO Order Details].PartID SET Parts.[In Stock] = [In Stock]+Forms!frmPOOrdersRec!frmPOOrdersRecSubform.Form!Received, Parts.NetPrice = Forms!frmPOOrdersRec!frmPOOrdersRecSubform.Form!NetPrice, Parts.RetailPrice = Forms!frmPOOrdersRec!frmPOOrdersRecSubform.Form!RetailPrice
WHERE ((([PO Order Details].POID)=[Forms]![frmPOOrdersRec]![frmPOOrdersRecSubform].[Form]![poid]));


Thanks for your help on this...

 

[geno]

To answer your question, yes there are multiple parts in the subform with unique part ID's, and yes all the parts are getting updated with the values of the first part in the subform...this is driving me nuts.

 

[geno]

Hi,
I've got this to work, instead of looking at the forms for the update I referenced the purchase order detail table directly. Thanks for your input...