Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Unbound Combo Box based on pop up form

    Hi sure this is easy if you know how! I have an unbound combo box (Combo9) on a form Fm_Data_Entry. I use a click button to open a pop up form from fm_data_entry which adds in another value to the source table of the unbound combo box, however, i can't get it to update yhe combo again with the new value without actually closing the fm_data_entry and opening again. How can I get the unbound combo box to update the new table value without closing the form down?? Thanks folks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    me.combo9.requery will get it to update. But where do you put it?
    You could use the Activate event.
    Regards
    John



  3. #3
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks..

    Quote Originally Posted by johnhutchison View Post
    me.combo9.requery will get it to update. But where do you put it?
    You could use the Activate event.

    Not sure I know what the Activate event is - I was going to put it in the onEnter part of the combo box - would that do the trick??

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The form has an On Activate event.

    Onactivate.gif

    Using the On Enter event will work I think, but it is requerying more often than needed.
    Regards
    John



  5. #5
    Lounger
    Join Date
    Nov 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ~Similar issue

    Hi John, thanks so much that's working beautifully now. Can you also help with a similar issue -

    I have a table detailing actions - simply an autonumber and listing possible actions people might wish to do with the db (e.g. open table, open form, run query, run report etc.). I want to attach this table to an unbound combo box on a switchboard form to allow people various options. I've got the combo box set up but not sure how I get the various actions to perform once the combo box listing is selected. think I need some sort of if statement on the AFter update box but VBAs not my strong point. Can you give me some pointers. It would be like

    If Combo2 value = 1, then open form1
    If Combo2 value = 2 then run report1 etc.

    Thanks for your help!

    blitzy

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I prefer to use a list box for that job, so users can see all the options, but a combo would work.

    I would not use the After Update event, however, because they event will fire too easily. People will trigger actions without meaning too.
    I would put a command button button next to the combo, so they need to take a deliberate action. But the choice is up to you..the code is the same wherever you put it.

    In the table you use, I also add an extra field. One field displays the action in plain english, the other has the actual name of the object you want to use: the table, form, report etc. My goal is that I should be able to add new items to the list, and have them work without needing to modify the code.
    So you also need to be able tell what type of action is needed, from something in the table. If you use a consistent naming convention, you could tell the action by the name of the object.
    e.g. table names begin with tbl, so if the first letters of the name are tbl, the action is to open a table. Query names begin with qry so if the first three letters of the object are qry, the action is to run a query.

    If you don't use a consistent naming method, you would need another field to describe the action to be taken.

    So my code would look something like this.


    Code:
    Dim strObject as string
    If not isnull(me.combo2) then
       strObject = me.combo2
       Select case left(strObject,3)
       Case "tbl"
          docmd.opentable strObject
       Case "qry"
         docmd.openquery strObject
       Case "frm"
         docmd.openForm strObject
       Case "rpt"
         docmd.openReport strObject, , acPreview
       Case else
         msgbox "I don't know what todo.",vbInformation
       end select
    else
        msgbox "Select an item from the list", vbInformation
    end if
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •