Update existing detail records upon change in master field

[hthagood]

I am fairly new to access (using 2003 with a 2000 format project). I
have created a budget control module that tracks a line item budget and
any updates, and can then assign a purchase order to that line.

I have a main form that has the budget line item number, description,
and contract number. Then I have a subform that track actual and
forecast payments. The child/master fields are the Budget line number
and the contract number.

To begin with I set up all the line items with a generic contract
number "Not Bid" that is just a zero amount entry in my purchase order
table. I also set up each line item with all the monthly periods set
to zero (this allows me to forecast cost even without a purchase
order). Once I award a purchase order I change the "Not Bid" contract
number to the actual contract number, for instance, "01011.23"

My problem is that once I do that the detail payment records that were
created with a contract number of "Not Bid" are no longer associated
with that master record.

I need to write code that AfterUpdate of the contract number it updates
all the detail payment records contract number to match the master
contract number.

I hope I explained that well enough. Thanks for the time.

 

[Guest]

Have you looked at setting up enforced referential integrity between key
fields in the tables? That way, when you update the parent table, the child
table field is automatically updated.

Bruce

 

[Jeff L]

Is this what you are looking for?

Docmd.RunSQL "Update tblDetail Set ContractNumber = " &
Me.ContractNumber & " Where ContractNumber = 'Not Bid'"

Hope that helps!

 

[hthagood]

Is this what you are looking for?

Docmd.RunSQL "Update tblDetail Set ContractNumber = " &
Me.ContractNumber & " Where ContractNumber = 'Not Bid'"

Hope that helps!

First off, I have tried to enforce referential integrity, and
automatically update, but that doesn't seem to work...

I think that code may work, but I am simply ignorant as to the actual
translation. Here is what I am dealing with specifically:

If I update "tblEstimateLineItems.ScopePackageNo" (master field), I
want it to update each detail field in "tblAllPayments.ScopePackageID"
where the "tblEstimateLineItems.LineItem equals
tblAllPayments.EstimateLineItem" (the other Master Field). I want it
to be dynamic based on "tblEstimateLineItems.ScopePackageNo" and not
only for the Not Bid value...

 

[Jeff L]

This would go in the After Update Event Procedure of your
ScopePackageNo field.

Docmd.RunSQL "Update tblAllPayments " & _
"Set ScopePackageID = " & Me.ScopePackageNo &
_
" Where EstimateLineItem = " & Me.LineItem

 

[hthagood]

I get the following error:

Run-time error '3075':

Syntax error (missing operator) in query expression '01011.17Where
EstimateLineItem = B00300'.

Here is the exact code I put in:

Private Sub EstimateContractAssignment_AfterUpdate()
DoCmd.RunSQL "Update tblAllPayments " & _
"Set ScopePackageID = " & Me.ScopePackageNo & _
"Where EstimateLineItem = " & Me.LineItem
End Sub

 

[Todd H]

I found the syntax error, but now when I update the ScopePackageNo, it
prompts me for the EstimateLineItem (with a dialog with the LineItem
number displayed with a blank input box)...if I manually input the
matching number then it asks if I want to modify the rows (SO IT DOES
FIND THE CORRECT RECORDSET TO UPDATE)...when I click yes, I get another
error stating that it cannot update the rows because of Key Violations.

I have the subform source set to a select query, and not the table
directly; mainly so that the detail records would sort a certain way in
the form display.

I'm appreciate all the help thus far.

I would also like to surpress the dialog prompts so that all this
updating happens completely in the background and the user is not
aware.

 

[Todd H]

The Key Violations were a relationship I had doubled up, and simply had
to delete it. I have gotten everything to work with the following
code:

Private Sub EstimateContractAssignment_AfterUpdate()
DoCmd.RunSQL "Update tblAllPayments " & _
"Set ScopePackageID = " & Me.ScopePackageNo & _
" Where EstimateLineItem = '" & Me.LineItem &
"'"
End Sub

But it doesn't literally transfer the values. I basically have three
types of values that "ScopePackageID" can be; "Not Bid", "N/A" or a
number such as "01011.99" When update the combo box to number, it
updates the correct recordset, but drops the leading zero. When I
change to "Not Bid" or "N/A" I get a text dialog with either "Bid" and
an input blank, or "N" with an input blank, followed by "A" with an
input blank. All the field formats are the same (Text).

Is there some qualifier I can put in the code to literally transfer the
values during the update procedure?

 

[Jeff L]

Again, you need single quotes around a text value:

"Set ScopePackageID = '" & Me.ScopePackageNo & "' " & _

Hope that helps!