John,
I would create a normalizing union query [quniTablesUsers] of distinct tables and users like:
SELECT DISTINCT "tblABA" as TheTable, [UserField]
FROM tblABA
UNION ALL
SELECT DISTINCT "tblCBP", [UserField]
FROM tblCBP
UNION ALL
SELECT DISTINCT "tblCIS", [UserField]
FROM tblCIS
--- etc ---
You can then create a row source for the first combo box using a Row Source of:
SELECT DISTINCT TheTable
FROM quniTablesUsers
ORDER BY 1;
I would set the Row Source of the second combobox in the after update event of the first combo box
Me.cboUser.RowSource = "SELECT DISTINCT [UserField] FROM [quniTablesUsers] WHERE TheTable = '" & Me.cboTheTable & "' ORDER BY 1;"
BTW: it appears you might have multiple similar tables which is often considered un-normalized. I assume you understand your schema better than I do.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 28 Oct 2014 12:48:17 -0700
Subject: [MS_AccessPros] Filter combo box based off another combo box
It seems like I have seen a few comments on how to do this, however, I can't find the thread at this time.
I need to configure a combo box so that users can select the reviewer from a list of tables.
I have 11 tables (tblABA, tblCBP, tblCIS, etc).
1. The user wants a form where you select the measure table (for example tblABA).
2. Once the user selects the table, then the user would select the user that is going to complete the review (for example: John Fakes).
3. Currently there are around 40 reviewers and each measure table can use different reviewers so I can't go off a tblReviewers for example.
4. A reviewer might review as many as 200 records in a table so I only want to see each reviewer once.
I was orginally thinking of creating a combo box with a list of the tables, then after the user selected the measure, I was going to use a macro to then select the query for that measure (qryABA, qryCBP, etc) which would display the reviewers.
Hopefully this makes sense, I'm just trying to figure out the best way to create this.
John F.
I would create a normalizing union query [quniTablesUsers] of distinct tables and users like:
SELECT DISTINCT "tblABA" as TheTable, [UserField]
FROM tblABA
UNION ALL
SELECT DISTINCT "tblCBP", [UserField]
FROM tblCBP
UNION ALL
SELECT DISTINCT "tblCIS", [UserField]
FROM tblCIS
--- etc ---
You can then create a row source for the first combo box using a Row Source of:
SELECT DISTINCT TheTable
FROM quniTablesUsers
ORDER BY 1;
I would set the Row Source of the second combobox in the after update event of the first combo box
Me.cboUser.RowSource = "SELECT DISTINCT [UserField] FROM [quniTablesUsers] WHERE TheTable = '" & Me.cboTheTable & "' ORDER BY 1;"
BTW: it appears you might have multiple similar tables which is often considered un-normalized. I assume you understand your schema better than I do.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 28 Oct 2014 12:48:17 -0700
Subject: [MS_AccessPros] Filter combo box based off another combo box
It seems like I have seen a few comments on how to do this, however, I can't find the thread at this time.
I need to configure a combo box so that users can select the reviewer from a list of tables.
I have 11 tables (tblABA, tblCBP, tblCIS, etc).
1. The user wants a form where you select the measure table (for example tblABA).
2. Once the user selects the table, then the user would select the user that is going to complete the review (for example: John Fakes).
3. Currently there are around 40 reviewers and each measure table can use different reviewers so I can't go off a tblReviewers for example.
4. A reviewer might review as many as 200 records in a table so I only want to see each reviewer once.
I was orginally thinking of creating a combo box with a list of the tables, then after the user selected the measure, I was going to use a macro to then select the query for that measure (qryABA, qryCBP, etc) which would display the reviewers.
Hopefully this makes sense, I'm just trying to figure out the best way to create this.
John F.
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar