M
Monkeykins via AccessMonster.com
Hi all
I'll begin by saying I am still new to Access, with less than a year since I
was told to pick it up and learn as I went, and have only been digging into
the SQL for about a month. What I would like to know is if I am on the right
track for a project I am working on.
I am working with a huge data set (44 columns and over 700,000 rows) and what
I've been asked to do is create a way for the table to be searched by people
with little computer knowledge (thus the combo boxes- by limiting the fields
there is less chance of error and an empty result) but it also needs
functionality for high-end users.
What the form needs to do is dynamically populate the combo boxes from the
table and according to the selections of the user. Once the user has selected
as few or as much of the modifications, create a report or table with the
requested information. So all the combo boxes can be blank, all can be
selected, or anywhere in between.
The source of the combo boxes, unless I am mistaken, should be group- by
queries and not the main table, since a combo box with 7 hundred thousand
options would be huge.
To restrict the values of the 2 combo boxes I have so far, I've got an after-
update on one combo setting the source of the second based on a query with
the first combo as criteria, and vice-versa, with their default sources being
the initial grouping query so you can select them in any order.
SQL-
Private Sub cmbRegion_AfterUpdate()
'Set GroupNo Combo Box Based on Region
Select Case cmbRegion.Value
Case cmbRegion.Value
cmbGroupNo.RowSource = "101 GroupNo"
End Select
This works fine with two combo boxes, but to include the number of combo
boxes I will need means making huge numbers of queries.
Is this what will have to be done, or is there another way to approach this?
I'll begin by saying I am still new to Access, with less than a year since I
was told to pick it up and learn as I went, and have only been digging into
the SQL for about a month. What I would like to know is if I am on the right
track for a project I am working on.
I am working with a huge data set (44 columns and over 700,000 rows) and what
I've been asked to do is create a way for the table to be searched by people
with little computer knowledge (thus the combo boxes- by limiting the fields
there is less chance of error and an empty result) but it also needs
functionality for high-end users.
What the form needs to do is dynamically populate the combo boxes from the
table and according to the selections of the user. Once the user has selected
as few or as much of the modifications, create a report or table with the
requested information. So all the combo boxes can be blank, all can be
selected, or anywhere in between.
The source of the combo boxes, unless I am mistaken, should be group- by
queries and not the main table, since a combo box with 7 hundred thousand
options would be huge.
To restrict the values of the 2 combo boxes I have so far, I've got an after-
update on one combo setting the source of the second based on a query with
the first combo as criteria, and vice-versa, with their default sources being
the initial grouping query so you can select them in any order.
SQL-
Private Sub cmbRegion_AfterUpdate()
'Set GroupNo Combo Box Based on Region
Select Case cmbRegion.Value
Case cmbRegion.Value
cmbGroupNo.RowSource = "101 GroupNo"
End Select
This works fine with two combo boxes, but to include the number of combo
boxes I will need means making huge numbers of queries.
Is this what will have to be done, or is there another way to approach this?