Hi,
Background
In my database, I have two fields "ManagerID" and "ExecID". These are both combo boxes using lookups to two separate tables. They work fine.
I have another combo called "ReviewDateChangedBy" and I want this list to be populated by the values chosen in both fields above. So if the user chooses "Bob" from ManagerID and "Fred" from ExecID, then the values "Bob" and "Fred" should be available in this third combo.
My approach was to create two queries: qryAuthorised1 which gets the Manager, qryAuthorised2 which gets the Exec and then a third union query, qryAuthorised1+2 which pulls both those values together. Maybe this is clunky, but this is about the level of my expertise! Anyway, all three queries work OK.
I then set the Row Source of the ReviewDateChangedBy field to
SELECT [qryAuthorisedPerson1+2].Person FROM [qryAuthorisedPerson1+2] ORDER BY [qryAuthorisedPerson1+2].Person;
Next I added an AfterUpdate event to both the "ManagerID" and "ExecID" fields as follows:
... (and the same for ExecID)
Problem
When I try to enter some new data, the ReviewDateChangedBy combo box has no values. But if I exit the form and then come back, the correct values appear.
And when I AMEND either the Manager or the Exec. the values in the ReviewDateChangedBy do not update. And again, if I exit the form or go to Design view and then come back, THEN the values available in the combo have updated.
So it looks to me as if the data source for the combo is right, but it's the updating part that's not working.
I wondered if the record needed to be saved fIrst, so in the AfterUpdate events of the first two combos, I added the line DoCmd.Save, before the Requery line. But that's had no effect.
Someone from another forum suggested that I need to press the F9 key after updating one or both of the first two combos. I tried that and it does indeed work, but there must be a way to "programatically" press F9.
Does anyone know how I should do this? I'd be really grateful for some pointers.
Pat.
Background
In my database, I have two fields "ManagerID" and "ExecID". These are both combo boxes using lookups to two separate tables. They work fine.
I have another combo called "ReviewDateChangedBy" and I want this list to be populated by the values chosen in both fields above. So if the user chooses "Bob" from ManagerID and "Fred" from ExecID, then the values "Bob" and "Fred" should be available in this third combo.
My approach was to create two queries: qryAuthorised1 which gets the Manager, qryAuthorised2 which gets the Exec and then a third union query, qryAuthorised1+2 which pulls both those values together. Maybe this is clunky, but this is about the level of my expertise! Anyway, all three queries work OK.
I then set the Row Source of the ReviewDateChangedBy field to
SELECT [qryAuthorisedPerson1+2].Person FROM [qryAuthorisedPerson1+2] ORDER BY [qryAuthorisedPerson1+2].Person;
Next I added an AfterUpdate event to both the "ManagerID" and "ExecID" fields as follows:
Code:
Private Sub ManagerID_AfterUpdate()
'if the owner changes update the list of people authorised to amend the review date
Me.ReviewDateChangedBy.Requery
End Sub
Problem
When I try to enter some new data, the ReviewDateChangedBy combo box has no values. But if I exit the form and then come back, the correct values appear.
And when I AMEND either the Manager or the Exec. the values in the ReviewDateChangedBy do not update. And again, if I exit the form or go to Design view and then come back, THEN the values available in the combo have updated.
So it looks to me as if the data source for the combo is right, but it's the updating part that's not working.
I wondered if the record needed to be saved fIrst, so in the AfterUpdate events of the first two combos, I added the line DoCmd.Save, before the Requery line. But that's had no effect.
Someone from another forum suggested that I need to press the F9 key after updating one or both of the first two combos. I tried that and it does indeed work, but there must be a way to "programatically" press F9.
Does anyone know how I should do this? I'd be really grateful for some pointers.
Pat.