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
Solved

Refresh Drop Down

Posted on 2009-04-01
10
302 Views
Last Modified: 2013-11-28
Hello,

I have a form where I want to run a report that is based off of the 2 drop downs. I want the user to be able to click the territory drop down and choose a territory. Then in the Market drop down only choose those markets that fall into the territory that was chosen in the drop down above.

I set the criteria of the drop down for Market to point to the Territory drop down, and it works fine. But if I change my mind and pick a different territory, the market drop down doesnt requery and show the new markets for the new territory picked. It just shows the markets for the last territory. If I close the form and reopen it will work fine for the first pick of territory. I have to close the form and re-open to get it to work again.

I have tried to put a refresh after update on territory and it says refresh is not available at this time. I tried to requery after update on territory and nothing happens.

Does anyone have any idea on how to get this to work consistently?

Thanks for your thoughts on this,
Jenn~
0
Comment
Question by:Jennerator
  • 4
  • 3
  • 3
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 24041406
Try this:

Private Sub cboMarket_AfterUpdate()

     Me.cboTerritory = Null
     Me.cboTerritory.Requery

End Sub

What is the Row Source SQL for your Territory combo box?

mx
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 250 total points
ID: 24041478
Hi mx, me trhinks the territory drives the market rather than the market driving the territory.

Private Sub cboTerritory_AfterUpdate()

     Me.cboMarket = Null
     Me.cboMarket.Requery

End Sub

The Market drop down's ROWSOURCE must be linked to the territory dropdown such as
    SELECT ID, MarketName FROM tblMarkets WHERE Territory = Forms!MyFormName!cboTerritory

Cheers, Andrew
0
 

Author Comment

by:Jennerator
ID: 24041500
Here is the SQL for Territory combo Box

SELECT DDRegional.[Territory#], DDRegional.TerritoryName
FROM DDRegional
GROUP BY DDRegional.[Territory#], DDRegional.TerritoryName
ORDER BY DDRegional.[Territory#];


Here is the SQL for Market Combo Box
SELECT DDRegional.[Market#], DDRegional.[Territory#], DDRegional.TerritoryName
FROM DDRegional
WHERE (((DDRegional.[Territory#])=[Forms]![Print Badges Form]![Territory]));

I did your suggestions and put it in the Market after update and It made no difference except to remove teh Territory ID
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 75
ID: 24041516
oh well ... and I read the Q twice, lol!

mx
0
 

Author Comment

by:Jennerator
ID: 24041552
That got it! Andrew! I am going to split the points because Andrew built on mx. Thanks to you both!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24041555
You have already taken into account the selection criteria so it should be just a case of setting the code in the AfterUpdate event of Territory combobox.
Cheers, Andrew
0
 
LVL 75
ID: 24041572
I guess this is where I was confused:

"I set the criteria of the drop down for Market to point to the Territory drop down, and it works fine."

?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24041575
"I am going to split the points because Andrew built on mx" Absolutely no problem with that.
Cheers, Andrew
0
 
LVL 75
ID: 24041580
Love that user name 'Jennerator' !!

mx
0
 

Author Comment

by:Jennerator
ID: 24041669
Thanks mx you say that every time you help me (which is allot!) I struggled with this for an hour and a half. I was close on what you guys came up with, but not quite there. I don't know why I don't just post the question in the first 15 min?

THanks again!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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