Updating Unbound Combo Box based on pop up form

Topic

New Reply

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.

Reply | Quote

Replies

me.combo9.requery will get it to update. But where do you put it?
You could use the Activate event.

Reply | Quote

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??

Reply | Quote

The form has an On Activate event.

29044-Onactivate

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote