1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access: Update combo box values

Discussion in 'Business Applications' started by Big Pat, Sep 23, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Big Pat

    Big Pat Thread Starter

    Joined:
    Jun 25, 2007
    Messages:
    43
    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:

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

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  3. Big Pat

    Big Pat Thread Starter

    Joined:
    Jun 25, 2007
    Messages:
    43
    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
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  5. Big Pat

    Big Pat Thread Starter

    Joined:
    Jun 25, 2007
    Messages:
    43
    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.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Thta looks good.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752530

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice