Update Query in Vba format

[JMan2Be]

Can someone please, please help!

I'm trying to update records that are marked 'new' with an SQL update query on the subform (ONLY!); however I don't know how to limit the query to the subform only. It update everything. Here's what I've got so far.

DoCmd.RunSQL "UPDATE tPEPFARAllotments " & _
"SET FormFinalCompleted = 'final complete' " & _
WHERE Forms!fPEPFARAllotFm1!DocumentNumber= fPEPFARAllotFm2!DocumentNumber AND fPEPFARAllotFm2!FormFinalCompleted = 'new' ", -1

Anyone's help would be greatly appreciated. Thanks!

 

[MajP]

This where statement at a minimum makes no sense.
"Where somefield = " & someValue & "AND someotherField = " & someOthervalue"

even if it resolves the form values which it does not since they are in the quotes. It would still be something like
WHERE 123 = 123 AND 'new' = 'new'

WHERE Forms!fPEPFARAllotFm1!DocumentNumber= fPEPFARAllotFm2!DocumentNumber AND fPEPFARAllotFm2!FormFinalCompleted = 'new' ",

 

[JMan2Be]

MajP,

Thank you for the response, however I'm a bit new at this (I mistakenly select 'Programmer' when should have selected 'TechnicalUser'), and don't know exactly how to resolve the issue. I simply need to update values within a subform, and limit the update to the subform alone. If there's a better way to do this I'm all ears. Thanks in advance.

v/r,
Josh

 

[MajP]

You have to think of this a little differently. A form and subform is only a window into the underlying data. So you are updating the records in the underlying tables not in the form/subform. You have to update only those records in the table that are being filtered by the subform.

Can you answer these
1)Name of mainform
2)Name of subform control
3)Name of all fields that get updated "Name of the final completed field"
4)Name of the field that holds the doc number
5) Name of linked fields between main form and subform.