Solved

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

Posted on 2006-06-12
9
576 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
9 Comments
 
LVL 5

Expert Comment

by:BPeb
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mtcuser01
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Having the sample database really straightened me out. Even for one who is VB challenged. Thanks!
0
 
LVL 58

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now