Cascading Combo Boxes revisited - I really need help!!

[vangogh228]

Based on a working example given to me, I have set up the following, in an attempt to be able to choose a Rep name from a combo box list and then see a list of that rep's customers for selection in the next combo box:

(Object Names are in bold, key fields in italics.)

Rep Table
Rep Number
Rep Name

Customer Table
Customer Number
Customer Name
Rep Code

There are more fields on the Customer Table, but the ones listed are enough for this example. I have a one-to-many relationship between <<Rep Table.Rep Number>> and <<Customer Table.Rep Code>>.

I then created the following Form object with two combo boxes, using the Combo Box Wizard:

Rep-Customer Form
Rep
Customers

On the form, the Rep field has the following Row Source property:
SELECT [Rep Table].[Rep Number], [Rep Table].[Rep Name] FROM [Rep Table];
The Column Count is 2; Column Widths are 0",1"; Bound Column is 1.

The form's Customer field has the following Row Source property:
SELECT [Customer Table].[Customer Number], [Customer Table].[Customer Name], [Customer Table].[Rep Code] FROM [Customer Table];
The Column Count is 3; Column Widths are 0",1",0"; Bound Column is 1.

The form's Rep field has the following After Update property:
Private Sub Rep_AfterUpdate()
Me.Customers.Requery
End Sub

This seems to be exactly how the example I was given operates to give a list of the right customers on the second combo box's dropdown when the first combo box list is chosen from. It works in the file I was given, but not on mine. Am I missing something??

One of my wife's pistachio cakes to whomever helps me get this!! Well... if it won't survive shipping, at least you can have the recipe!

Thanks to all in advance!!!

EDIT: Regarding the ReQuery issue: If I am going to REquery... don't I have to have an original query??? If so, on what do I query???

[This message has been edited by vangogh228 (edited 05-06-2002).]

 

[Elana]

Change the customer combo box to include the words

WHERE [customer table].rep code = forms![frmYourForm]![rep];

Insert this after the FROM clause.

Be sure to change the names to reflect the actual form name and the actual combo box name.

Re: requery - You can use the Requery action to update the data in a specified control on the active object by requerying the source of the control. If no control is specified, this action requeries the source of the object itself. Use this action to ensure that the active object or one of its controls displays the most current data.



[This message has been edited by Elana (edited 05-06-2002).]

 

[vangogh228]

Thanks for the help so far. I need a little syntax help, though, on your example:

WHERE [customer table].rep code = forms![frmYourForm]![rep];

Does "rep code" have to be in brackets?
Can I use "Me![Rep]"??

This is what I actually typed, using my actual field names rather the ones from my example above... and AFTER the previous semicolon:
WHERE [Customer Table].[SRPS Rep Number] = forms![Combo Box Form Test]![Rep];

When I do that, the Customers combo box is always empty.

Thanks again for the help!!!!!

 

[Elana]

You need to place brackets around any control name that has spaces in the name...which is why it's much better to make object names one word (advice for the future!) Since it looks like some of your names are multi-word you need to place brackets around them:

WHERE [Customer table].[SRPS Rep Number] = forms![Combo Box Form Test]![Rep] should work.

Be sure that "rep" is the actual name of the combobox on your form...are you sure you didn't name the combobox 'customers'?

Hope this works; if not, we'll try something else.

 

[vangogh228]

Elana: Yes, the first field I am entering into is named "Rep" and the second one is "Customers". I made the changes. No good. The Customers drop down is still blank. Is the Requery after-update code OK for the Rep field?

I cannot begin to tell you how much I appreciate your help!

 

[Elana]

My experience has been that the longer it takes to figure something out, the easier the solution actually is. My guess is the answer is really simple, but I can't see what you have so I'm working in the dark.

Would it be possible for you to just send me the form in a zipped file so I can look at this?

accessEB@hotmail.com

 

[vangogh228]

Elana:

Thanks. I was in the middle of sending when I realized I should probably strip out some of the other stuff I am doing in that practice file... and then I sent it... oops.

Sorry about the file size. Again... THANKS for your help!

Tom

 

[Elana]

Hotmail might have kicked it out if the file is too big. I sent you an email to your yahoo.com address.

 

[vangogh228]

I'll recreate the file with just the necessary pieces/parts. Thanks!

 

[vangogh228]

Elana: THANK YOU!! It works great! Small syntax error - big problem - quick fix!! thankyouthankyouthankyou!!!

 

[vangogh228]

For all reading this:

The event procedure syntax error was that I had not deleted the semicolon before appending the WHERE statement. The cascade works fine now.