Solved

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

Posted on 2006-06-12
9
612 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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