Combo/Subform

[EasyOnly]

Hi All,

I am not sure from where to start. But I would like to create a combobox that the records in this combobox are the different queries that I created. I want when I select a query from the combo, it reflect in the subform. The subform will be a graph.

I know I asked a big few questions in one, but I would appriciate any direction.

Thank you.

 

[kjv1611]

Ok, I think I know what you're getting at - I hope.

If I'm correct on your thinking, then here are the basics:
[ol][li]Create Your Queries - and I'd suggest to maintain as much consistency between them as possible, as far as number of columns/fields.[/li]
[li]Create a table which lists all the names of the queries you're wanting to include.[/li]
[li]Create your subform that will show the data on your main form... though I'd almost think you could just use your main form as a continuous form, and put your combo box in the form header, data in the form detail.[/li]
[li]Create your combo box, and in the OnUpdate event, have an event which changes the record source for the subform or main form if you use the optional suggestion I made in the last item. In your event (VBA) code, you would change the recordsource name to the Combo Box's value.[/li]
[/ol]

That's just a rough outline, no detail. But using this should give you a form where you change the selection in the combo box, and that will automatically change the form recordsourcce... if that doesn't actually change the display, then in your event code, you can also add a Form.Requery line after changing the record source, and that should update the form immediately.

--

"If to err is human, then I must be some kind of human!" -Me

 

[BTSTeam]

As a possible alternative to keeping the query names in a table you could use some code such as this:

Function ListQueries()
Dim PickQry As Object
Set dbsCurrent = Application.CurrentData
For Each PickQry In dbsCurrent.AllQueries
If Len(Query_Finder.RowSource) > 2 Then
Query_Finder.RowSource = Query_Finder.RowSource & ";" & PickQry.Name
Else
Query_Finder.RowSource = PickQry.Name
End If
Next PickQry
End Function

"Query_Finder" is a List Box on your form. This has the advantage that you don't need to maintain a table of query names.

Cheers,
Andy

 

[kjv1611]

That's a good point, BISTeam. If there is any chance your queries will change, a dynamic method will indeed be best.

--

"If to err is human, then I must be some kind of human!" -Me

 

[EasyOnly]

Thank you for all the suggestions. I will work on it this afternoon. god willing.

I am specificaly working on a dashboard. the form will contain 5 subform/charts.....

 

[EasyOnly]

Hi All,

I created the table, the query, the combobox linked to the table with all the queries.

Now, for the subform that is connected the combobox, what will the record source will be.


.......................

BISTeam, If I would like to implement your suggestion, where will I use the code you gave.

 

[EasyOnly]

Hi All,

Any idea what will the after update event would look like that will update the subform.
Thanks

 

[BTSTeam]

"BISTeam, If I would like to implement your suggestion, where will I use the code you gave?"

Include the code as a function in your form code and call it at the Form Open event.


Andy