problems requerying cascading combo boxes

[dayna]

I'm working on a form with two unbound, cascading combo boxes that work perfectly well when the form is first opened. However, if the first combo box is changed, the items in the second combo box are not refreshed unless I close and reopen the form.

As per the generous advice found in this forum, I have tried to requery my second combo box by using Me.[Combo2].Requery as an After Update event on Combo1. No luck.

I have looked high and low throughout this forum and others for a solution, but the only similar threads I've found have either gone unanswered, or were resolved with the After Update requery.

Any ideas on what the problem might be? Thanks!

 

[Ginny2222]

If you requery the form after update on the combo box this should update the second. I had a similar problem earlier today, and it worked for me. Give it a go.
HTH
Ginny

 

[dayna]

Ah, I appreciate your kind suggestion, but I'm still not having any luck. I've tried requerying the form After Update and On Current. I tried typing this into Combo1 After Update event:

Me.Combo2.RowSource = "SELECT [Student ID], [Last Name], [First Name] FROM" & _
" Students WHERE [Class Code] = " & Me.Combo1 & _
" ORDER BY [Last Name]"
Me.Combo2 = Me.Combo2.ItemData(0)

Alas, synchronicity escapes me and my poor little database. Any other thoughts? Thanks again.

 

[pbaldy]

The requery of the combo should work, presuming its row source refers to the first combo (requerying the form would not). The code in your second post should also work. Are you sure the bound column in the first combo is the class code? Is class code a numeric field?

 

[boblarson]

Me.Combo2.RowSource = "SELECT [Student ID], [Last Name], [First Name] FROM" & _
" Students WHERE [Class Code] = " & Me.Combo1 & _
" ORDER BY [Last Name]"
Me.Combo2 = Me.Combo2.ItemData(0)

I don't see the requery which is necessary after setting the rowsource.

[B]Me.Combo2.RowSource = "SELECT [Student ID], [Last Name], [First Name] FROM" & _
   " Students WHERE [Class Code] = " & Me.Combo1 & _
   " ORDER BY [Last Name]"
[color=red]Me.Combo2.Requery[/color]
Me.Combo2 = Me.Combo2.ItemData(0)[/B]

 

[pbaldy]

In my experience, a requery is not necessary when setting the RowSource property.

 

[dayna]

no dice

Thank you, gentlemen, for your kind and expedient replies.

Paul: The Bound Column property for Combo1 is set to 1, but I must admit that I am a bit confused as to how Access displays/stores foreign keys. I believe that Combo1 is referring to the primary key of a table called Classes, which is autonumbered (and thus, numeric). However, I’d like Combo1 to display the (text) field [Class Code], which it does just fine when I use this as my Row Source:

SELECT [Classes].[ID], [Classes].[Class Code] FROM Classes ORDER BY [Class Code];

I then set Column Widths to 0”;1” and Column Count to 2.

For Combo2, I have the most luck when I use this as my Row Source:

SELECT Students.[Student ID], Students.[Last Name], Students.[First Name] FROM Students WHERE (((Students.Active)=Yes) And ((Students.[Class Code])=Forms![Update Student Data]!Combo1)) ORDER BY Students.[Last Name];

It may be worth mentioning that a one-to-many relationship exists between the tables Classes and Students, and the field [Class Code] is formatted as a lookup field (again, numeric) on my Students table.

So, I think I can answer both of your questions in the affirmative. When I first open the form, the cascade functions beautifully. The problem is that I have to close and reopen the form before I can select a student from another class. I don’t know why the requery command doesn’t work.


Bob: I entered your revision in my After Update event for Combo1, but my stubborn little Combo2 still refuses to refresh. She cannot be reasoned with, cajoled, or beaten into submission. She is utterly immune to my requeries. I need a new line of attack…


What are the chances that this could be an Access 2007 issue? Does anyone know a sneaky workaround? Thanks again!

 

[boblarson]

Can you either post your db or email it to me? I am interested in figuring this out but since I am way more visual it would be faster for me if I could just see it and play with it.

 

[dayna]

Thanks, Bob. The form I'm working with is called Update Student Data. As a warning, when the form is opened, the unbound combo boxes that I'm working with are not immediately visible. For some reason, I have to scroll up to the top of the form in order to find them. (I'm not sure why this is, but it seems a small and simple problem by comparrison.)

I should also point out that what I've been calling Combo1 and Combo2 in my posts are actually (and unimaginatively) named Combo76 and Combo78, respectively. (I've screwed things up in the past by renaming controls, so I tend to stick with whatever Access assigns.)

Thanks!

 

[pbaldy]

The code appears to be working fine. Are you running into the 2007 security issue? Is there a warning near the top when you open the db that mentions that certain content has been disabled?

 

[ajetrumpet]

In my experience, a requery is not necessary when setting the RowSource property.

Paul, why not? What do you mean by this? If there is a filter to be applied from the first combo selection, the only way to get it with the second is with the .Requery.

 

[pbaldy]

I mean when you use this method, as described in Bob's post:

Me.Combo2.RowSource = "SELECT..."

 

[dayna]

For some reason, I get that security message every single time I open Access. That's been happening ever since I got Vista, but I'm not sure why. I just choose Enable Content and keep going. Do you think I've got a bigger issue here?

 

[ajetrumpet]

Paul, what do you think of this...??

You need only one or the other of the following, never both...

1) Set a rowsource with a WHERE clause for combo2 in the properties and only use .Requery in the AfterUpdate code.

2) Write the rowsource in Visual Basic itself on the AfterUpdate event. No .Requery needed.

I spelled all this out in my FAQ page. Do you guys disagree with this?

 

[pbaldy]

Dayna, this is a good site on 2007, and this should take you to a specific spot on it describing how to stop that message.

http://allenbrowne.com/Access2007.html#Configuration


Adam, I'd agree with that. I posted a cascading combo sample a while back that demonstrated both methods. As you say, you only need one or the other. Bob's post specified that the Requery was needed when setting the rowsource (both together as you put it), which is why I mentioned it.

 

[ajetrumpet]

I don't see how this issue can be raised so much, it is really quite simple!

What message is this anyway? I don't think I ever saw that with my program. The code works just fine for me too Dayna.

 

[pbaldy]

This is the offending message. If you're seeing it, code is not running:

 

[dayna]

My Hero!!!

Thanks so much, Paul! You saved my day! I guess I'd gotten so accustomed to seeing (and ignoring) that security warning, I never put two and two together. Who knows how many current and potential problems you've just solved! I'm going to go outside and do some cartwheels now. Thanks again!

 

[pbaldy]

No problem dayna; glad to help. Do a couple of cartwheels for me, since I'd fall flat on my face if I tried one.