MS Access: Update combo box values

[Big Pat]

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:

Private Sub ManagerID_AfterUpdate()
'if the owner changes update the list of people authorised to amend the review date
Me.ReviewDateChangedBy.Requery
End Sub

... (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.

 

[OBP]

Pat, 10 out 10 for for effort and you are very close.
Can you explain what the 3rd Combo does, does it put a value in another Field/table or just display the previous selections?
I am not clear what you are going to select in the 3rd Combo.

 

[Big Pat]

Hi,

This is for a risk management database, where each risk will be assigned to a manager and overseen by an executive. Those are the first two combos.

There are various dates associated with risks, one of which is the ReviewDate, by which date there should be some progress to report.

But sometimes the ReviewDate will change (i.e. deadlines will be postponed) and the user will need to record who authorised that change, which should only be the manager or Exec associated with that risk. So my table has a field called ReviewDateChangedBy to store this. I haven't set any lookups or relationships at the table level, because the values in this combo will need to change depending on the values selected in the previous two. Examples:

Risk1 Manager BOB Exec FRED ReviewDateChangedBy should list BOB and FRED
Risk2 Manager JANE Exec FRED ReviewDateChangedBy should list JANE and FRED
Risk3 Manager JANE Exec BILL ReviewDateChangedBy should list JANE and BILL

So when the user selects a value from this third combo, it should be stored in the ReviewDateChangedBy field, which is just a standard text field.

Do you understand what I'm trying to do?

Thanks for your prompt reply, it would be great if you knew how to make this work.

Pat

 

[OBP]

Pat, are the manager and exec Ids in the same field or seperate fields?
If they are in seperate fields you can't Select just one of them.

 

[Big Pat]

They're in separate fields, when they're input, but that union query I mentioned pulls them together into a single source for the third combo.

And it DOES work, if you close the form and reopen or if you go to desgn view and then back to form view or if you press F9. In fact that will work most of the time because the idea is that the ReviewDateChangedBy field should only be completed if the EXISTING ReviewDate is being changed.

But things being what they are, there will be occasions where this data is being input retrospectively and the user will need to use all three combos at one sitting, which is why I need the third combo's entries to update based on the values of the first two.

Another thought has occurred to me, which is to have a combo with the values "Manager" and "Executive", with an AfterUpdate along the lines of

If me.newcombo.vale = "Manager" then
me.ReviewDateChangedBy.value = me.ManagerID
else
If me.newcombo.vale = "Executive" then
me.ReviewDateChangedBy.value = me.ExecID
else
me.ReviewDateChangedBy.value = NULL
End if

Would that work? I might give it a go.

Pat.

 

[OBP]

Thta looks good.