Changing row source with select case from combo

[d9pierce1]

Good morning all,
I wanted to run this by you all to see if I am on the right track before I type it all up. I have a combo and depending on what is selected, I want to run a select case to
change the fields on form as well as this code below to change the record source of several CBO's. Not sure if I have all the syntax correct? Maybe a better method?
Here is the code sample, this is just a sample, Where's will all change depending on what i select!

Private Sub CboTransType_AfterUpdate()


If Not IsNull(Me.CboTransType) Then
Select Case Me.CboTransType
    Case 1 ' Receive Funds
    If Me.CboTransType = 1 Then
	Me.CboReceivedFromID.RowSource = SELECT SystemDataID, SystemDataTypeID, SystemDataValue FROM tblSystemData WHERE SystemDataTypeID=20 ORDER BY SystemDataValue; 
	Me.CboFromAccount.RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=31 ORDER BY [AccountDescription];
 	Me.CboToAccount.RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=28 ORDER BY [AccountDescription]; 
	Me.CboDescription.RowSource = SELECT SystemDataID, SystemDataTypeID, SystemDataValue FROM tblSystemData WHERE SystemDataTypeID=17 ORDER BY SystemDataValue;
	Me.CboSelectAccount.RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=31 ORDER BY [AccountDescription];
    End If
    Case 2 ' Deposit Funds
    If Me.CboTransType = 2 Then
	Me.CboReceivedFromID.RowSource = SELECT SystemDataID, SystemDataTypeID, SystemDataValue FROM tblSystemData WHERE SystemDataTypeID=19 ORDER BY SystemDataValue; 
	Me.CboFromAccount.RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=28 OR [AccountTypeID]=29 ORDER BY [AccountDescription];
 	Me.CboToAccount.RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=28 OR [AccountTypeID]=31 ORDER BY [AccountDescription]; 
	Me.CboDescription.RowSource = SELECT SystemDataID, SystemDataTypeID, SystemDataValue FROM tblSystemData WHERE SystemDataTypeID=18 ORDER BY SystemDataValue;
	Me.CboSelectAccount.RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=29 ORDER BY [AccountDescription];
    End If
    Case 3 ' and so on
    End Select


    End Sub

Thank you all
Dave

[CJ_London]

Looks like a more complex version of cascading combo’s

you will probably want to clear any values in the target combos as well otherwise the old value will remain until
the user selects one from the new list

Your method will only work in a single form not for a continuous or datasheet form since all rows will be affected

[Micron]

For the sake of others, this looks like the same issue as in this thread
https://www.accessforums.net/showthread.php?t=89779

I still think that's a messy way of doing things. Select Case could set n to whatever number you need, then outside of the Case you'd have only one sql line for each control and concatenate n into it.

[d9pierce1]

Hi Micron,
I was unsure how to incorporate that previous code into this and would love to! It was very clean to say the least
I wasnt sure how to do that with having two different senarios,,, some being from tbl and some being from qry?
dave

[d9pierce1]

Hi CJ London,
Thank you and yes it is a single form. In my previous db I had 8 forms and 8 tables which had for the most part the same info in them so I am reestructuring this into one form, one table which can hold all the info...
And using Case to show and hide the fields on the form. I wanted to incorporate the code above with my case code so that I can change the rowsources of other combos depending on my selection. I was working with micron on
a different post but couldnt figure out how to make it work with different combos coming from different tables/queries.... So I went with the above to see if that would even work? But it would be messy so thats why I posted it
Dave

[Micron]

My suggestion was based on post 4 in that other thread where you used the same sources in each case. Now it seems you have 5 sources, so not worth the effort for that approach, I think. Based on what you've now shown I suggested something more like

    Case 1
        i = 20
        n = 31
        x = 18
        y = 17
     Case 2
        i = 19
        and so on
     Case ...
End Select

Me.CboReceivedFromID.RowSource = SELECT SystemDataID, SystemDataTypeID, SystemDataValue FROM tblSystemData WHERE SystemDataTypeID=" & i & "ORDER BY SystemDataValue;"
and so on

If you're going to have issues with this and continuous forms you probably should set the form recordsource instead and bind the controls to that. However, given that these controls get their rowsource from different places is suspicious. I'm guessing the form would have to be based on a query of several different tables, which would likely mean the form would be uneditable. I don't think there's enough info regarding what's going on behind this thread to provide accurate solutions.

[d9pierce1]

Thanks Micron,
I will see if I can incorporate this into my form. Yes, is single form and based off 1 table.
Dave

[Welshgasman]

Why are you case'ing Me.CboTransType then checking it with an IF ?
If the Case is not true, yiou are never going to be able to use the IF

[Micron]

As long as the combo value matches one Case, NP. If you add a list item and don't Case for it, then problem when that list item is chosen - unless you have a Case Else.
I agree though, the If is redundant. If Case is 1, then why check if the combo value is 1 when that's how you're setting the Select?

[d9pierce1]

Hi Welshgasman,
Lack of knowledge maybe?
If its null, its nothing, if selected 1 of 8 then if 1 then do this, if 2 then do this.... and there are only 8 options with the combo so nothing else to do if not selected....
Most likely a better way but I have not been taught it as of yet
Thanks
dave

[d9pierce1]

Hi Micron
That does make a lot of sense!
I never really thought about it as that how I was taught to do it so never ventured out.
I guess I could get rid of the If's, there is a lot I do not know about access and programing...but getting better I think
As far as the combo, its a value list and set so you cant add to it, it is either null, 1,2,3,...8 and thats all it will ever be
but that does make sense the the case would be enough, Thank you for pointing that out! I will practice that in future!
dave

[CJ_London]

You could add a number of columns to your value list then no need for a case statement- for example

Me.CboReceivedFromID.RowSource = SELECT SystemDataID, SystemDataTypeID, SystemDataValue FROM tblSystemData WHERE SystemDataTypeID=“ & cbotrantype.column(2) & “ ORDER BY SystemDataValue”