• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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~
0
Jennerator
Asked:
Jennerator
  • 4
  • 3
  • 3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)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
 
TextReportCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now