Combobox & VBA & selected item

[zozew]

Hi Guys,

Ive done a lot of searching and not quite got the answer im looking for...

Ive got a form with a combobox thats empty (to start with) bound to a query. then i have a button on the form opening a small form for adding an item in the combobox.

All works fine apart from something seemingly simple...

The combobox's function is filtering the form among a few other things. What im trying to do is, when i have added a new item to the combobox from the small form i would like set the new item as selected and make the afterupdate event trigger just as if the user has selected it.

thats it

 

[Cronk]

You need to set the value of the combo to the ID representing the combo item's ID.

So if your combo's rowsource is "Select ThingID, Thing from yourQuery" and the combo is bound to column 1, then if you add "yourNewThing" and it has a ThingID of 27,
me.combo = 27
will have yourNewThing highlighted.

 

[zozew]

ok i think i understand your example. So from the small form after i have added the item to the "Table" i make a query for the "ID" of the item that matches the value for the bound column i jys added and then set it as you mention. great, i will try and post back.

One thing though...will this method trigger the afterupdate event? i have read that it doesnt?

 

[arnelgp]

set the value and call your combo's after event proc:

Me.combo.value = id
Call combo_AfterUpdate

 

[zozew]

set the value and call your combo's after event proc:

Me.combo.value = id
Call combo_AfterUpdate

i tried calling the combo_afterupdate event...but it doesnt seem to work if its called from another form. I saw that solution in another forum and tried it..

 

[MarkK]

To call . . .

[COLOR="Red"]Private[/COLOR] Sub Combo_AfterUpdate()

. . . from another object, change it to Public . . .

[COLOR="Blue"]Public[/COLOR] Sub Combo_AfterUpate()

. . . and then you can do . . .

Forms("OtherForm").Combo_AfterUpdate

 

[arnelgp]

is your combobox in main form, and you want to call the after_update event of your subform, is this correct?

 

[zozew]

is your combobox in main form, and you want to call the after_update event of your subform, is this correct?

I want to trigger the combo_afterUpdate event from a small popup form. Basically im adding a name in the popup that later goes in the combobox as an item. So when i click an add button in the popup it adds an item in the combo(adds to the table the combos record source) and selects it and fires the event...well thats how i want it to work.

 

[zozew]

To call . . .

[COLOR=Red]Private[/COLOR] Sub Combo_AfterUpdate()

. . . from another object, change it to Public . . .

[COLOR=Blue]Public[/COLOR] Sub Combo_AfterUpate()

. . . and then you can do . . .

Forms("OtherForm").Combo_AfterUpdate

i tested, and i could make public functions in the form an call it from another form but making the combos sub public gave me an error

runtome error 2465
Application-defined or object-defined errorstill testing

 

[arnelgp]

what is the code behind your combo_afterupdate event?
should work as Makk suggested.

 

[zozew]

what is the code behind your combo_afterupdate event?
should work as Makk suggested.

attaching a simplified DB just the two forms...

first form opens, click add new request, enter a name, click add, the popup is closed and the new request is in the combobox.

i would like it to be selected after the small popup closes, and that the afterUpdate event is triggered just as if you select it manually

there is a bit of test data that works if you select items in the combobox

 

[arnelgp]

you may try this

 

[zozew]

you may try this

Im getting promped "cant find project or library" when i click the add button in the popup, im using access 2010...and the date field in the popup comes up with #name?

 

[arnelgp]

go to VBE and on tools, reference, remove those with Missing reference.

 

[zozew]

go to VBE and on tools, reference, remove those with Missing reference.

im not sure this is appropriate....but....i love you !!!!! works like a charm now!

 

[zozew]

go to VBE and on tools, reference, remove those with Missing reference.

For my own sanity can you explain the syntax for calling the combos afterUpdate event.. i tested so many combinations and im pretty sure i tested the one that is working in your example and i got something like method or object not bla bla...

thx Again

 

[arnelgp]

hehe, muwah!

 

[Uncle Gizmo]

It's not considered good practice to call a controls event directly from somewhere else. All you need to do is put the code into their own subroutine and call that from both the combo box and the other location you need to call it from. One small, extra step and now you are following the conventional approach.

 

[zozew]

It's not considered good practice to call a controls event directly from somewhere else. All you need to do is put the code into their own subroutine and call that from both the combo box and the other location you need to call it from. One small, extra step and now you are following the conventional approach.

I actually did just that, made a function and the only thing the afterupdate of the combo does was calling that function. But because I was using a value in a hidden column of the combo to set the right selected item. I needed the combo box's event to trigger and update it self and so on... Hope this makes sense... But It's solved now thanks to arnelgp