Solved

Refresh Drop Down

Posted on 2009-04-01
10
293 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 - Access MVP) 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

777 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