Filtering data from Form Controls

[eireanacht]

I am using SQL Server 2000 with a Microsoft Access 2002 front end interface. I have a form where one combo box control directly uses fields from a table to display information (no filtering). I have a second combo box control on the same form where I would like to filter the data based on the selection in the first combo box.

I have tried the following methods without any success:

1. Create a view where one of the fields is used to filter the data based on the value on the form - myfield = Forms!MyForm!mycomboBox

Result is it does not want to recognize the form and gives an error indicating it thinks the reference to the form is actually a reference to a non-existant table.

2. Create a SQL statement in the Row Source property box of the combo box, again basing the results on the value of the first combo box.

Result is an error much like the first. I have also tried putting the SQL statement in the Row Source Type property, but no help there either.

3. Create a function to return a recordset based on the value of the first combo box control.

Result again is no data.

Can anyone think of a "SIMPLE" way to get this done?

 

[donutman]

That's a function of the programming language. It should be coded in the Change Event of the 1st control. I believe some controls have the capability of automatically filtering and refreshing the 2nd control, but you could write a SP to retrieve the correct rowset.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

a question better asked inteh access forms forum. But what I do is create a dynamic SQL statement in the afterupdate event of the first combo box and then set the rowsource to that for the second combo box programmically.

Or you can create a stored procedure with an input value. The create an execute statement dynamically in the code which sets the input variable to the value of the first combo box. Then execute that stored procedure code in the On GotFocus Event of the second combo box.

No somple method, I'm afraid.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief