Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-06-12
9
Medium Priority
?
634 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
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

886 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