Updating Similar Records Based on ID in the Same Table

[Mlkobefan]

I have a table in Access with the following stucture:



Each activity ID can only have ONE responsible supervisor, so I am trying to make a query that will automatically update all records with identical activity ID's when the responsible supervisor for one of them is changed.



For example, if I were to change Smith, John to Calvin, Mike in the first record I would like the second record to update to this as well because the Activity ID is the same.

The idea I am going for is to join the table with itself based on ActivityID and update rows to have the same Responsible Supervisor if the Activity ID is the same.

I have tried the following code, but to no avail since it just seems to call on itself.

UPDATE tblHOURS
SET tblHOURS![ResponsibleSupervisor] = [tblHOURS]![ResponsibleSupervisor]
WHERE (([tblHOURS]![ActivityID]=[tblHOURS]![ActivityID]));

Thanks in advance!

[June7]

So code behind form in textbox or combobox AfterUpdate event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Execute "UPDATE tblHours SET [ResponsibleSupervisor] = '" & Me.textbox.ResponsibleSupervisor & "' WHERE [ActivityID]=" & Me.ActivityID
Me.Refresh

[Mlkobefan]

So code behind form in textbox or combobox AfterUpdate event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Execute "UPDATE tblHours SET [ResponsibleSupervisor] = '" & Me.textbox.ResponsibleSupervisor & "' WHERE [ActivityID]=" & Me.ActivityID
Me.Refresh

That seems like the way to go with this, but I am getting a compile error in the last part of the code though. Access tells me the data member Me.ActivityID is not found.

My table is tblHours
The combobox where the user selects the responsible supervisor is cmbRS
The combobox where the user selects the activity Id is cmbAID

I have the code set behind AfterUpdate as follows:

Private Sub cmbRS_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Execute "UPDATE tblHours SET [ResponsibleSupervisor] = '" & Me.cmbRS.[ResponsibleSupervisor] & "' WHERE [ActivityID]=" & Me.ActivityID
Me.Refresh
End Sub

Any idea on what is wrong?

[June7]

Is this form bound to table? Are these bound comboboxes?

Why is there a combobox for the ActivityID?

If these controls are bound, selecting value will change data in record. Do you really want to change the ActivityID?

DoCmd.Execute "UPDATE tblHours SET [ResponsibleSupervisor] = '" & Me.cmbRS & "' WHERE [ActivityID]=" & Me.cmbAID

[Mlkobefan]

Is this form bound to table? Are these bound comboboxes?

Why is there a combobox for the ActivityID?

If these controls are bound, selecting value will change data in record. Do you really want to change the ActivityID?

DoCmd.Execute "UPDATE tblHours SET [ResponsibleSupervisor] = '" & Me.cmbRS & "' WHERE [ActivityID]=" & Me.cmbAID

Yes, the form is bound to a table and I would like the records with identical Activity ID's in the table to reflect a change in the responsible supervisor. Going through one by one would be very tedious.

There is a combobox for the ActivityID so that the user can pick an ActivityID and add a new record with it. The combobox also has code that filters all records by the ActivityID selected.

There is a datasheet view in the form where the user can make these modifications.

I attempted to change the code to what you have above, but I am still getting the same error

[June7]

So the ActivityID combobox is not bound to field?

What is the datasheet view - a subform?

Where is this code - main form or subform?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Mlkobefan]

So the ActivityID combobox is not bound to field?

What is the datasheet view - a subform?

Where is this code - main form or subform?

If you want to provide db for analysis, follow instructions at bottom of my post.

Hi June7,

It is not a subform, but just a split form with datasheet view at the bottom . The user can use combobox cmbAID to filter the datasheet view by Activity ID, but other than that all data is inputed using drop-down lists in the datasheet view itself. I have the code on the form itself, as there is no subform.

This is all of the code running on the form:

Private Sub cmbAID_AfterUpdate()
Me.Filter = "(ActivityID) = '" & Me.ActivityID & "'"
Me.FilterOn = True
End Sub

Private Sub Command1713_Click()
DoCmd.RunCommand acCmdUnhideColumns
End Sub

Private Sub cmbRS_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Execute "UPDATE tblHours SET [Responsible_Supervisor] = '" & Me.cmbRS & "' WHERE [ActivityID]=" & Me.ActivityID
Me.Refresh
End Sub

So when I attempt to modify the Responsible Supervisor in one of the records, the code runs but then I get the compile error: method or data member not found. I followed your steps for debugging and with the code stopped at the breakpoint I checked the variable name of Me.ActivityID and it does return the ID of the record I am modifying. I was modifying the name Smith, John as shown in the table in my original post, and the Activity ID that is returned is EA100 which is correct. I am not sure why this is not working, but then again I am a fairly novice Access user!

[June7]

Post 4 shows using the combobox name instead of field name.

If the cmbAID AfterUpdate works using reference to ActivityID (it shouldn't) then the UPDATE sql should also work. So I am confused.

Controls used to enter search/filter parameter must be UNBOUND and code should reference the control name, not a field.

The () in the filter code are not needed.

[Mlkobefan]

Post 4 shows using the combobox name instead of field name.

If the cmbAID AfterUpdate works using reference to ActivityID (it shouldn't) then the UPDATE sql should also work. So I am confused.

Controls used to enter search/filter parameter must be UNBOUND and code should reference the control name, not a field.

The () in the filter code are not needed.

Sorry there was a typo in my last post. The code I am running is with Me.cmbAID as follows:

Private Sub cmbRS_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Execute "UPDATE tblHOURS SET [Responsible_Supervisor] = '" & Me.cmbRS & "' WHERE [ActivityID] =" & Me.cmbAID
Me.Refresh
End Sub

The filter works perfectly, so like you said it is weird that cmbRS doesn't work on After Update. I have checked the spelling of all fields/combo boxes and everything looks correct so I am not sure why I am getting the compile error.

EDIT: I modified the code to change Me to frmHOURS and now the error is highlighting the DoCmd.Execute portion of the text

[June7]

Sorry I did not catch that earlier - my bad! Should be:

CurrentDb.Execute ...

[Mlkobefan]

Sorry I did not catch that earlier - my bad! Should be:

CurrentDb.Execute ...

Hmm so that fixed the compile error, but now I am getting a runtime error 3061: Too few parameters. Expected 2.

When debugging and placing the mouse over the code, the code shows that it does assign the correct responsible supervisor to Me.cmbRS and the correct Activity ID to Me.cmbAID.

[June7]

I don't see anything wrong with that syntax. ActivityID is a number type field? That is correct spelling - no spaces?

[Mlkobefan]

I don't see anything wrong with that syntax. ActivityID is a number type field? That is correct spelling - no spaces?

Yes, the spelling is correct. I have the datatype as text, since the ID is a mix of letters and numbers (EA100 for example)

[June7]

Then need apostrophe delimiters, just as you have for cmbRS.

But why are you saving name instead of ID for responsible supervisor?

[Mlkobefan]

Then need apostrophe delimiters, just as you have for cmbRS.

But why are you saving name instead of ID for responsible supervisor?

Not sure I understand what you mean, I want the responsible supervisor name selected to populate all of the other records that contain the same Activity ID.

Being the Access newbie that I am, I am not sure how to put delimiters around cmbAID correctly. Would you be able to show me how to do this?

Edit: Figured it out and it works!! I truly appreciate you going thorugh this with me June 7. For reference to other forum users, the code I ended up using was:

Private Sub cmbRS_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "UPDATE tblHOURS SET [Responsible_Supervisor] = '" & Me.cmbRS & "' WHERE [ActivityID] ='" & Me.cmbAID & "'"
Me.Refresh
End Sub