Solved

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

Posted on 2006-06-12
9
624 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:mtcuser01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:BPeb
ID: 16890002
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
 
LVL 58

Expert Comment

by:harfang
ID: 16890015
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16891822
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:mtcuser01
ID: 16895282
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
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
ID: 16899017
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
 

Author Comment

by:mtcuser01
ID: 16905846
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
 
LVL 58

Expert Comment

by:harfang
ID: 16907977
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
 

Author Comment

by:mtcuser01
ID: 16955611
Having the sample database really straightened me out. Even for one who is VB challenged. Thanks!
0
 
LVL 58

Expert Comment

by:harfang
ID: 16955722
I'm glad this was useful. Success with your challenge!
(°v°)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question