Cacading Combobox record source

[Nitesh9999]

Cascading Combobox record source

Hi, I need help on cascading combo boxes, in my form


In my example DB i have 3 tables(when i find a soloution i will implement it on a larger scale database which will have around 15 tables). I need to create reports of these tables. The problem was that i need to filter the data before i create the report by 2 fields. If i was to do this the straight forward way i would have 3 tbls, 3 qrys and 3 reports (which on my final database would mean 15 of everything.)

So i've decided to use a form driven system to reduce the number of forms and querys i have in the DB. Using a form based system i should have 3 Tables (called 1, 2 and 3), 1 Query (called query2), 1 Form (called withselect) and 1 report (called rptquery1).

Now to the system.

The form should have 3 combo boxes. 1 at the top to select the table i want to query. Then 2 others to select the fields by which to filter. the form should also have 2 buttons, 1 torun the report one to return reults in a table to edit the data if needed.

This is the current problem i am having

In the forms 1st combo box i have managed to list all my 3 tables (thanks to FancyPrairie) using the following code:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (Left$([Name],4)<>"MSys") AND (Left$([Name],4)<>"USys") and (Left$([Name],1)<>"~") and (MSysObjects.Type=1)
ORDER BY MSysObjects.Name;

But i cant select any thing in the following 2 comboboxes. At the moment im tryin to use this code in the record source of the 2nd and 3rd combo boxes

SELECT * FROM YourTable WHERE (YourTable.TableName=[Forms]![withselect]![Combo6]);


Attached is the latest version of the DB.

Im getting the error:

The record source 'SELECT * FROM YourTable WHERE (YourTable.TableName=[Forms]![withselect]![Combo6]);' specified on this form or report does not exist.

Hopefully someone can help.

 

[Uncle Gizmo]

In your combo box, combo6, the one you select the table name in, add the following code to its after update event.

Private Sub Combo6_AfterUpdate()
cboMonth.RowSource = "SELECT * FROM " & Combo6.Value
End Sub

 

[Nitesh9999]

Hi Uncle Gizmo

Even with your code i get the same error. It says Access can't find the macro. I'm not even using macros??

I also read in another forum i shoudnt use the name month as access uses this for something so i have changed month to FinMonth

 

[Uncle Gizmo]

If you open the zip file you have attached to this thread, click on the properties of Combo6 go to events, click on the after update event, and you should see this:

Private Sub Combo6_AfterUpdate()

End Sub

Now fill the gap in between:

With:

cboMonth.RowSource = "SELECT * FROM " & Combo6.Value

So you end up with:::::::::::::

Private Sub Combo6_AfterUpdate()
cboMonth.RowSource = "SELECT * FROM " & Combo6.Value
End Sub

 

[Nitesh9999]

It still says Access cant find the macro.

Stupid Question - Am i actually using a macro of some kind?

 

[Uncle Gizmo]

Right, I appear to be having a lot of trouble communicating today!

Open up the sample you posted in this forum "Web Along.mdb" Make sure you open up this sample, and not a database and stored on your PC.

Select the form "Withselect" Open this form in design mode, and right click on combo6. You will see the property sheet for this combo box.

Select the tab "events" You will see an event called "after update" Click on the ellipsiss is at the end of the row (...) and this will bring up a form where you can choose one of three options:

Expression builder
Macro builder
Code builder

Select code builder this will take you to the class module that belongs to this form. It will also list in the class module the following code for you:

Private Sub Combo6_AfterUpdate()

End Sub

Place between this code the following :

cboMonth.RowSource = "SELECT * FROM " & Combo6.Value

you should now have the following :

Private Sub Combo6_AfterUpdate()
cboMonth.RowSource = "SELECT * FROM " & Combo6.Value
End Sub

Save and close the form, then click on the combo6, the top one on the form "Withselect" select either 1, 2 or 3 (these are the names of your tables) now click in the combo box below "cboMonth" And this combo box will show different values depending on which table you have selected.

 

[Nitesh9999]

Hey that works well. Thanks.

Now the next problem...

In the second combo box cboMonth (that displays the data according to the table select box "combo6") it dosnt select the months fields contents, it selects the contents of the first field (patient number).

I think this code needs changing in the cboMonth box (but what do I know??):

SELECT * FROM YourTable WHERE (YourTable.TableName=Forms!withselect!Combo6);

rather than Select * should it be along the lines of select month??

More help is much appreciated. The latest attachment is with your above code added.