Cascading Lists using a Query

[SusanK4305]

I have looked around and can't find any thing on this.
This is what I have

Table A (Actions)
Approved
Pending
ect...

Table B (Reasons)
N/A
Denied
Waiting paperwork
Corrections
Bad Credit
ect...


I then put Table A and B in to a Query
Qur A
Field1= Field2 (both dropdowns from Table A and B using Lookup wizard)
Denied=Bad Credit
Approved= N/A
Pending= Waiting Paperwork
Pending= Corrections
Ect...

I have another Table listed as All Data
Fields inclued Date,Action,Reason,remarks, ect.. As you can see Action and Reason show up again based on Qur A. I then created a form from the tabl All Data using wizard.
I put in the fallowing code under Action After update.

Private Sub Action_AfterUpdate()
   On Error Resume Next
   Reason.RowSource = "Select [Qur A].Reason " & _
            "FROM [Qur A] " & _
            "WHERE [Qur A].Action_Type = '" & Action_Type.Value & "' " & _
            "ORDER BY [Qur A].Reason;"
End Sub

Row Source: SELECT DISTINCT [qurAction Type vs Reason].[Action Type] FROM [qurAction Type vs Reason] ORDER BY [qurAction Type vs Reason].[Action Type]; 

Control Source based on All Data table= Action
But when I click on Action all I get is numbers.

[SusanK4305]

ok I took the All Data table and changed them to Look up from Table A and Table B. that got the words back.
removed and readded the up date field to the form words still there. :)

Action row source reads: SELECT [tblAction Types Choices].[ID], [tblAction Types Choices].[Action Type] FROM [tblAction Types Choices];

After update same code (just testing) Got an After update Error message.
then I changed the Code to

Private Sub Action_Type_AfterUpdate()
   On Error Resume Next
   Reason.RowSource = "Select [All Data].Reason " & _
            "FROM [All Data] " & _
            "WHERE [All Data].Action_Type = '" & Action_Type.Value & "' " & _
            "ORDER BY [All Data].Reason;"
End Sub

But get the same Error Message when I click Action on my form.

[SusanK4305]

Ok no one has answers me on this yet. Y?

[dsatino]

Because this is not a customer service site. This is a collaborative site of shared knowledge where people answer if they:
1. can
2. are willing
3. have the time

[SusanK4305]

I am sorry I guess that could have been read as disrespectful I didn't mean to be. Some times I run around like a chicken w/ it's head cut off I end w quick replies.

[SusanK4305]

So I am guessing that no one knows how to make a Cascading Lists useing a Query

[MMcCarthy]

Susan check out this article in the insights section on cascading combo/list boxes.

The problem here is not that we don't know the solution but rather that we don't understand the problem. If this article doesn't help solve your problem can you create a sample file with the offending form, queries and tables (sample data only) and then zip it up and attach it to your next post.

It may help clear up some of the confusion.

[SusanK4305]

Yes I tried that and all I got were numbers. Maybe I should just redo it from scratch. I must have messed w/ it to the point that I have no clue what I did. :)

[slenish]

SusanK

Check out this link. You might find it helpful :D

http://www.databasedev.co.uk/filter_combo_boxes.html

[SusanK4305]

I get that part but here is were mine and this one diff.
My Reasons is a list all its own (I think this is called a string) as well as the Actions. The reason for this is so I can use the same reason for diff Actions ...like below

Qur 1
ID...Action......Reason
1....Pending..... Bad Credit
2....Pending.....Waiting Paperwork
3....Denied......Bad Credit
4....Approved.....N/A
5....Pending....Other
6....Pending....Corrections
7....Denied.....Waiting Paperwork

See how the Reasons are used more than 1 time as well as the Actions?
that is why I put it in a Query useing dropdown boxes.

[MMcCarthy]

OK Susan this is getting totally confusing. I get that Table A and Table B are lookup tables. However, I don't get the following...

• How do Table A and Table B relate to each other?
• What is the sql code to qur 1 and/or qur A - not sure if these are the same or different queries?
• Is tblAction Types Choices a join table?
• What is the sql code for the qurAction Type vs Reason query assuming it is different to the above queries?

I think you can see why it's so confusing. Can you start from scratch and post the details of the relevant tables and queries. It might help to use their proper names to avoid any more confusion.

[SusanK4305]

I have changed it up a bit I cleared it all and started from scratch. This is what I have now.

Table A (tblAction Type) is just a normal data table
Table B (tblReason) is just a normal data table
Table C (tblAction vs Reason)has ID (auto),Action type (dropdown), and Reason (dropdown)
C is the query (Qur A)


SQL is as follows:
SELECT [tblAction vs Reason].ID, [tblAction Type vs Reason].[Action Type], [tblAction Type vs Reason].Reason
FROM [tblAction Type vs Reason]
ORDER BY [tblAction Type vs Reason].[Action Type], [tblAction Type vs Reason].Reason;

Then I created an All Data table
ID,SSN,Action Type,Action Date, Reason, Notes

Try 1: In Table C...I have tried having the Action Type as a lookup field based off tblAction Type and the Reason as a lookup field based on [Qur A].Reason. In the form created by the wiz based off of Table C....added the After Update code in to field "Action Type"(yes I re-named is field in the form to cboAction Type.As well as the cboReason.)

After Update Code:
Private Sub cboAction_Type_AfterUpdate()
On Error Resume Next
Reason.RowSource = "Select [Qur A].Reason " & _
"FROM [Qur A] " & _
"WHERE [Qur A].Action_Type = '" & Action_Type.Value & "' " & _
"ORDER BY [Qur A].Reason;"
End Sub
Result: cboReason doesn't filter/cascad and cboReason shows all meaning it will show the same reason one than 1 time because it matches this each action type.

Try 2: In Table C...I have tried having the Action Type as a lookup field based off [Qur A] and the Reason as a lookup field based on [Qur A].Reason. In the form created by the wiz based off of Table C....added the After Update code in to field "Action Type"(yes I re-named is field in the form to cboAction Type.)

After Update Code:
Private Sub cboAction_Type_AfterUpdate()
On Error Resume Next
Reason.RowSource = "Select [Qur A].Reason " & _
"FROM [Qur A] " & _
"WHERE [Qur A].Action_Type = '" & Action_Type.Value & "' " & _
"ORDER BY [Qur A].Reason;"
End Sub
Result: cboAction Type shows all meaning it will show the same action one than 1 time because it matches this each reason. same with the cboReason.

Try 3: Same as try 2 but I added an unbound field that stored the value in the Action Type field. This is look up is based off tblAction Type and same results as well.

I think that is everything that has been done so far and every bit of data.

[MMcCarthy]

OK try changing your code as follows:

Private Sub cboAction_Type_AfterUpdate() 
On Error Resume Next 
 
    Me!cboReason.RowSource = "Select [Qur A].Reason " & _ 
        "FROM [Qur A] " & _ 
        "WHERE [Qur A].Action_Type = '" & Me!cboAction_Type & "' " & _ 
        "ORDER BY [Qur A].Reason;" 
    Me!cboReason.Requery
 
End Sub