[MS_AccessPros] Re: Filter combo box based off another combo box

 

Bill,

These tables are generated from an external program and each of these tables are created.  Reviewers review the data for each measure, then once done, the data is extracted back into SAS for further work.  Each table is unique and has very different fields.  One table might have 5 fields while another has 15 and they are unique to that measure so I can't normalize anything.  Plus, it would be more difficult for the SAS programmers to grab the data back.

 

I never thought of a union query, that sounds easy enough and so does grabbing the distinct reviewer.

 

Thanks,

 

John F.

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

John

Let's start with the part about getting only one record for each reviewer. For that, you use SELECT DISTINCT Reviewer FROM...

Getting the table can be done either with a combo box, list box or frame. Seeing how you have 11 tables I'd use a combo box to save real estate. Use the table combo's AfterUpdate Sub to set the RowSource for the reviewer combo.

Private Sub cboTable_AfterUpdate()

    Dim strSQL As String

    

    strSQL = "SELECT DISTINCT Reviewer " _

        & "FROM " & Me.cboTable & " "

    Me.cboReviewer.RowSource = strSQL

    Me.cboReviewer.Requery

        

End Sub

Bill Mosca

---In MS_Access_Professionals@yahoogroups.com, <jfakes@...> wrote :

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: jfakes@rocketmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___