Solved

Unbound form control won't requery/refresh

Posted on 2011-02-15
18
928 Views
Last Modified: 2012-05-11
I have been working for an entire day at getting a single unbound control to requery. Refresh. whatever.

A single form.
2 combo boxes.
1 is populated and woolah!
The second one is filtered to the results limited by the 1st combo box's selection.

I've done this type of reference a hundred times but this time I can't get the darn combo box #2 to actually requery itself to the results of the newly changed combo box #1. Both are unbound. Both are on the same form. Both are enabled, visible, unlocked.

Funny, the sql query on the rowsource for combo box #2 works great. The combo box doesn't.

I've tried access macros using actions:
runcommand with refresh, runcommand with run or just fun ol' macro action requery.

I've tried using different vba options:
docmd.requery
me.cmbSPASelect.Requery
[Forms]![MAIN]![cmbSPASelect].Requery

I've tried putting the macros/vba on the after update event of combo #1.
As well as the on enter event, on got focus event, on click event for combo #2.

Problem with the on enter and on got focus events is that, combo #1's action can't set the focus to combo #2 when I use those. So I'm stuck with on click but I can't get it to work.

Please help me. This is my mountain. I must get over it!
0
Comment
Question by:culpees
18 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34903500
0
 
LVL 75
ID: 34903596
"The second one is filtered to the results limited by the 1st combo box's selection."
What is the Row Source SQL for the 2nd combo
What are the names of the Combo boxes?

You have a couple of options
1. After Update event of Combo 1

Private Sub Comb1_AfterUpdate()
    Me.Combo1.Requery
End Sub

2.  On Enter event of Combo 2

Private Sub Combo2_Enter()
    Me.Combo2.Requery
End Sub

If neither of these work, then something unusual is going on, like the Row Source for Combo2 is not correct.

mx
0
 

Author Comment

by:culpees
ID: 34903611
I read this article. I'm keeping it for prosperity. But I don't see how my exact scenario fits in.

I could apply the row souce using cases in vba BUT if we apply more options to the table, then I'd have to manually adjust the vba as well to include the additional case options. This is out of the question.

The row source is defined already and works outside the combo. It actually works in the combo the first time you activate the form. But then once you click on it after updating the combo1 it doesn't reflect the change seen in combo1.

And, bonus.
Combo 2 needs to remain UNFILTERED at all if combo 1 is blank. so my query is seen below:
SELECT PEI_SPA_CODES.NAME_SHORT, PEI_SPA_CODES.NAME, PEI_SPA_CODES.TEAM FROM PEI_SPA_CODES WHERE (((PEI_SPA_CODES.TEAM) Like "*" & [forms]![MAIN]![cmbTeam] & "*")) ORDER BY PEI_SPA_CODES.NAME;

So if combo1 is null, the query returns all records. If combo 1 has a selection, it limits the records.

Why if the query works won't the combo requery?
0
 

Author Comment

by:culpees
ID: 34903627
wow. good timing mx.

combo 1 = cmbTeam
combo 2 = cmbSPASelect

on option 1 you posted:
did you mean literally to requery combo1? not combo2?
0
 
LVL 75
ID: 34903630
typo sorry

Private Sub Comb1_AfterUpdate()
    Me.Combo2.Requery
End Sub
0
 

Author Comment

by:culpees
ID: 34903644
okay. well I tried both. Those were initial efforts too actually. No go.

What's next? Please tell me there's more???
0
 
LVL 75
ID: 34903658
Can you upload the db ... or a one that exhibits the issue ?

mx
0
 

Author Comment

by:culpees
ID: 34903783
Sure thing!

I made a substitute database which is seen attached here. It has the applicable form, tables, macros and queries in it.

It is stripped of proprietary data but there is enough there to test it and see what it is doing. I was able to replicate the problem with this copy.

Steps:
Option 1. Team Rep ("combo 2") needs to be unfiltered. Click on Team Rep to get a full list. (IF the Team Rep has a profile set up as indicated by fields in the underlying table for that control, that selection will then populate the 3 boxes to the right including "combo 1" (Team).)

Option 2. Team Rep ("combo 2") needs to be filtered based on Team ("combo 1") selection.

So basically an "if". If [Team] has a selection in it, then limit "Team Rep", else no limitation.

((You'll get a "REGISTRY" form error when opening the main form. It's because that form is a subform which is not included in this database since it's not affected by my immediate problemo.))

Thank you so much for looking at this!!!!!
db4.mdb
0
 
LVL 75
ID: 34903806
the db/txt file is zero bytes ...!

mx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:culpees
ID: 34903823
Okay. this time it took it. I don't know what happened.
db4.mdb
0
 
LVL 75
ID: 34903854
Ok ... can you tell me the exact steps ....with current results and expected results.  IE ... do this, then that ... specifying what values to select ... and what should be the results.

thx.mx
0
 

Author Comment

by:culpees
ID: 34903886
1 of 2 things should happen.

1:
(Team is null and not populated.)
Click Team Rep and see a full list, unlimited. This is good.
Make a selection (example, SP or GG). If user has a profile set up, then you'll see the 3 fields to the right populate. (The Team populates for all users though the Area and Asset may not be set up for everyone's profile yet.)

So in short. The Team Rep selection from an unrestricted list will populate the Team as well as the Area and Asset.

OR

2.
Let's say Team is populated now, due to a previous search. This is good.
I want to go backwards now.
Click Team Rep, and now this list should be restricted by the Team selection (only because the Team is not null and actually has a value in it.)

# 2 is the part I can't get working. I want to be able to click the Team and then go to the Team Rep and see that list become limited to whatever is selected in Team if it has something selected. Otherwise the Team Rep needs to remain a full, unlimited list if Team is empty.
0
 

Author Comment

by:culpees
ID: 34903897
Currently, for part 2... Team will have a value in it and I click Team Rep to see the limited list but it's not filtering. The Team Rep filter will work 1 time once you activate the form. After that, you can change the Team and then go to the Team Rep and see no change in the list even after the Team has been changed.

Sorry, forgot to mention what it is currently doing.
0
 
LVL 75
ID: 34903918
"Currently, for part 2... Team will have a value in it and I click Team Rep to see the limited list but it's not filtering."

Seems to me that is is filtering - see images.

What am I missing ?
Capture1.gif
Capture2.gif
Capture3.gif
0
 
LVL 39

Expert Comment

by:als315
ID: 34904038
In Afterupdate event for field cmbTeam, add requery for field cmb.SPASelect

Private Sub cmbTeam_AfterUpdate()
    Me.cmbSPASelect.Requery
   
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 34904066
See if this is closer to what you want ...

mx
db4-MX01.zip
0
 

Author Comment

by:culpees
ID: 34907122
MX,

I can see that you switched the code in the after update on combo1. I swear I had that before and it didn't work then! I'm glad it's working now. It appears to be doing exactly what I want it to do now. So I've replicated it to my actual db and we're good to go.

I am so pleased. Thank you for helping me last night. That was fantastic.

0
 
LVL 75
ID: 34908658
You are very welcome.
Thank you for using Experts Exchange.

mx
0

Featured Post

What Security Threats Are You Missing?

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.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

706 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

21 Experts available now in Live!

Get 1:1 Help Now