Refreshing combo list

[Malcy]

Hi
Need an elegant solution which probably everyone but me knows!
With my combo boxes I limit to list so as to control content. In some cases there is a genuine need to add new content so I put a button alongside and allow entry of new content. This allows me to log and validate the data before it is entered. When you close the entry form you are back at the combo.
However, unless you press F9 you don't see what you have just entered.
I am happy to press F9 but clients are not, so how do I refresh or requery the combo's supporting query to show the new entry using VBA?
I did wonder whether using the form's OnCurrent event might help but suspect this will requery the form's supporting query rather than the combo's supporting query.
Would be most appreciative of any assistance
Thanks

 

[mcirvine]

On the Form_Load procedure I create my combo boxes with the values in them.

When a new record is added I call the Form_Load procedure again and that does the trick for me. Maybe not the best solution but works fine for me. Sometimes I create a Form_Reload procedure if I don't want to refresh everything on the form.

below an example of a combo box in my Form_Load sub

Me.cboCcOrigin.RowSource = "SELECT [Qry_DistKeys_Header].[CC_ORIGIN]" & _
    "FROM Qry_DistKeys_Header GROUP BY [Qry_DistKeys_Header].[CC_ORIGIN]"
        
    Me.cboCcOrigin.Value = Me.cboCcOrigin.ItemData(0)

this resets the combobox to the first value in the list if i call the Form_Load sub

 

[Summerwind]

I tend to use an SQL statement to load the content of my combos, usually fired when the combo gets the focus. I do this as it assists my forms to open quickly.

When in the situation of having a new value added to the list, I use a form as you do, so that the new data can be checked. To get the new item into the combo's visible list I then set its RowSource to "", set its rowsourcew to the original SELECT clause then set its value to the new value entered into the data entry form.

Sounds a little involved but its quite simple really and works!

 

[Malcy]

Hi
Thanks for the replies.
I am trying to get my head round this. I do not close the original form with the combo boxes in since most fields are unbound so if I close I lose the entries (well I could save them as variables I suppose). The idea I work on is the original form stays open and the form for entering new data for the combo opens up over the original form so it is hidden.
Presumably what is being suggested is that I requery the combo from the new form before closing it - so that when back at my original query the combo will include the new entry?
Or is there a better way of tacking this whole scenario??

 

[supercharge]

Requery

The idea here is to just requery the combo box itself.

See attached. It's a very simple sample, created just for you. Try add a new number and see if it've been added to the combo box after you close the new-entry form.

 

[wazz]

...the original form stays open and the form for entering new data for the combo opens up over the original form so it is hidden.

consider opening the 2nd form in dialog mode. if the first form has been moved (if possible) the second form might not cover it. in dialog mode you have to finish working with the 2nd form before you're allowed to click on anything else.

Presumably what is being suggested is that I requery the combo from the new form before closing it - so that when back at my original query the combo will include the new entry?

you'll have to requery the combo after closing the second form because the record won't be saved until it is closed and so requerying the combobox won't produce the new value.

2nd form:

DoCmd.Close acForm, Me.Name
Forms!frmFirstFormName!cboName.Requery

 

[Malcy]

Thanks guys
Appreciate the help and the sample was really easy to follow. Like most things, simple when you can see the way.
I also like the dialog idea so will try to incorporate that too.
Thanks again
Best wishes