Solved

Refresh Drop Down

Posted on 2009-04-01
10
285 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

920 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

14 Experts available now in Live!

Get 1:1 Help Now