Refresh Drop Down

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~
JenneratorAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
 
TextReportConnect With a Mentor Commented:
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
 
JenneratorAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
oh well ... and I read the Q twice, lol!

mx
0
 
JenneratorAuthor Commented:
That got it! Andrew! I am going to split the points because Andrew built on mx. Thanks to you both!
0
 
TextReportCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
TextReportCommented:
"I am going to split the points because Andrew built on mx" Absolutely no problem with that.
Cheers, Andrew
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Love that user name 'Jennerator' !!

mx
0
 
JenneratorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.