Access 2002: force combo box to requery or refresh after adding new data

I have a primary form with a combo box that uses a query to select from a list of names. All tables are located within the same Access file. If the needed name does not exist the user can open a subform and add the name to the table from which the combo box query selects. When a new name is entered and the subform is closed, this new name does not appear in the combo box until I close out of the primary form and reopen it. I found this link on the MS site which would seem to address this issue: http://support.microsoft.com/kb/281870 
but it works inconsistently. What I did was add the following code:

Private Sub Combo4_Enter()
Me.Combo4.RowSource = "SELECT TOP 100 PERCENT setlname+', '+setfname FROM msetdesign ORDER BY setlname"
End Sub

for the OnEnter propery for the combo box. Again, sometimes it works but most of the time new values do not appear in the drop down list

Any thoughts? I am not a developer so I might need to be walked though this a bit. thanks
mtcuser01Asked:
Who is Participating?
 
harfangCommented:
mtcuser01

I guess we will need to see how and where you manage your popups. Normally, the focus remains on the combo box, so that the Enter event is never called again. Basically, Enter is probably not the best moment to requery.

The normal method woul be to use the "On Not In List" event. This allows to automatically requery the combo when a new value is added. Another would be to monitor the BeforeUpate and AfterUpdate events.

You can find an example of both methods in the EE question {http:/Q_21832368.html#16571217}, with a downloadable demonstration database.

(°v°)
0
 
BPebCommented:
Shouldn't be too hard.  If you've tried adding me.Combo4.Requery to the above code and that didn't work you'll probably need to add event code to the close of your subform (dialog box) that refreshes the combo box that way.

Here's a good resource on how to reference forms and reports from other objects:  http://www.mvps.org/access/forms/frm0031.htm

What you'll probably need in your dialog box close (or accept button) is something like:

     Forms![YourForm].Combo4.Requery

0
 
harfangCommented:
Hello mtcuser01

From your description, I understand that the "subform" (probably a "popup form") is closed by the user, hence the record is saved. Then each time you enter the combo box, it is automatically requeried. This would be simpler, btw:

Private Sub Combo4_Enter()
    Me.Combo4.Requery
End Sub

The question is then: are you certain to reenter the combo box after the popup form is closed? To observe this, use this little trick to trace events:

Private Sub Combo4_Enter()
    Me.Combo4.Requery
    Me.Caption = "Entered combo at " & Time()
End Sub

(°v°)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rockiroadsCommented:
When you mean subform, u are talking about a form within your own form or a new one?

If you open a new form, do you open it as modal? I mean it stays on top? You could at that point, always requery Combo4 or reset the rowsource for Combo4 straight after the call to the form
e.g.
DoCmd.OpenForm "myNewNameForm", , , , acFormAdd, acDialog
Me.Combo4.Requery
** or
Me.Combo4.RowSource = "SELECT TOP 100 PERCENT setlname+', '+setfname FROM msetdesign ORDER BY setlname"



If subform, u can use the form_afterupdate and/or form_afterinsert events to tell its parent (your primary form) to requery or reset the the rowsource for Combo4.
These events are called when you make an update to existing or create a new record.
e.g.
Me.Parent.Combo4.Requery
** or
Me.Parent.Combo4.RowSource = "SELECT TOP 100 PERCENT setlname+', '+setfname FROM msetdesign ORDER BY setlname"




0
 
mtcuser01Author Commented:
I think that I need to be more specific. In the main form there are combo boxes where a single value can be selected. A pop up form is available when the needed value is not in the drop down list. When I add

Private Sub Combo4_Enter()
    Me.Combo4.Requery
End Sub

for the OnEnter propery for the combo box it now works properly (thanks!). Where I am still having problems are the subforms that are in the main form that allow the user to enter in multiple values. They are in a datasheet view but the combo box works the same way. When a value is not in the list a similar pop up form is available to enter in the needed value. Adding

Private Sub Combo4_Enter()
    Me.Combo4.Requery
End Sub

for the OnEnter propery for the combo box in the subform is not working. There are Save and Close buttons on the pop up form that I can use to try and requery my subform but I'm unsure how. Is this where I would use the form_afterupdate and/or form_afterinsert events? Thanks again.
0
 
mtcuser01Author Commented:
I looked through the sample database and it is a really nice design. I'm confused (apologies in advance for my lack of VB skills) about  how does the selecting of (new Supplier) in the drop down lauch the fmodNewSupplier form. Thanks
0
 
harfangCommented:
mtcuser01

As you might have seen, the VB for the "not in list" event (typing a new supplier name in the combo) is pretty simple, and most of it can be derived from the examples in VB help on that event.

To add the "(new supplier)" marker requires three steps:

1) The combo box must show this message along with all available suppliers, and return a special value it that has been selected. This is done with a union query as the row source:

    SELECT SupplierID, CompanyName, 2
    FROM Suppliers
    UNION ALL
    SELECT TOP 1 -1, ' ( new supplier ) ', 1
    FROM Suppliers
    ORDER BY 3, 2;

The first query in the UNION is a simple select query, with an additional sort column. The second simply creates one single row, with a non-existant ID of -1.

2) In the combo's BeforeUpdate, the special value -1 triggers the opening of the popup form. If the user cancels it, the selection is simply rejected (using the Undo method). If not, the value -1 is momentarily accepted as input, but...

3) The AfterUpdate event catches that and replaces the -1 by the ID number of the newly created supplier. This cannot be done in the previous event because you cannot update a field in the Beforeupdate event. This event also takes care of the combo's requery, or the new supplier name would not appear.

Cheers!

(°v°)
0
 
mtcuser01Author Commented:
Having the sample database really straightened me out. Even for one who is VB challenged. Thanks!
0
 
harfangCommented:
I'm glad this was useful. Success with your challenge!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.