Subform - Open based on selection

[nybaseball22]

Hello. I have an idea, but I am not sure if it is possible. I want
to know if I can create 3 or 4 subforms and have one of them open on a
form based on a selection I make on that form. For instance, I have a
Form "ExpensesMain" - I want to create subforms Expense1, Expense2,
etc. When I make a selection in a field called "ExpenseType", I could
insert an AfterUpdate code that would open one of my subforms. I
guess I should start with the question of - is this possible? And if
so, I can create my subforms and see how to start on the code.

Thank you.

 

[Arvin Meyer [MVP]]

It is possible, and in fact, is a better solution than opening several
subforms when you only need the data from 1.

Try something like this in your Expense Type combo box (or list box) to
change the subform's SourceObject property. This is just aircode typed in
this news posting:

Sub cboExpenseType_AfterUpdate()

Select Case Me.cboExpenseType
Case "Expense 1"
Me.NameOfSubformControl.SourceObject = "Expense1"
Case "Expense 2"
Me.NameOfSubformControl.SourceObject = "Expense2"
' etc
End Select

End Sub

If all the field were the same, you could also create just 1 subform and use
code similar to above to chance the Recordsource property

Me.NameOfSubformControl.Recordsource = "Select * From tblExpense Where
ExpenseType = "'" & Me.cboExpenseType & "'""

 

[nybaseball22]

That works perfect. Thank you very much. Exactly what I wanted.

But, now that I have that, can I ask to take it a bit further? When I
open the main form to a new record or I make a selection that is not
one of my cases, I want the subform control to open with a generic
subform. I have the subform created, and I inserted this case:

Case Is = Null (because it would not let me do Case Is Null)

I also want to say if it does not equal any of the cases, it will open
this generic subform. In this case I could usually use <>, but I hate
to have to list all the cases with <> in the code. So, my question
is, can I have it default to a form if the selection does not equal
one of my cases?

NOTE: I created this as a Private Sub and use a Call Code in the
form's OnOpen and OnCurrent because I have other code used there as
well.

Thank you again for everything.

 

[nybaseball22]

Sorry, I think I have figured out one part. I will post my code in
case anyone wants to use this or in case I did something wrong that
you might point out. This covers the opening of the form to a new
record when the ExpenseType field is blank. I did not figure out how
to code it to say - if my choice in ExpenseType is not Expense1 or
Expense2, open subExpense3. I wanted to do this without <> to all the
different cases, but I'm not sure if that is possible.

I changed the code to (which I use a "Call DetermineSubForm code):

Private Sub DetermineSubForm()
If IsNull(Me.[ExpenseType]) Then
Me.SubMainChild.SourceObject = "subExpense3"
Else
Select Case Me.ExpenseType
Case "Expense1"
Me.SubMainChild.SourceObject = "subExpense1"
Case "Expense2"
Me.SubMainChild.SourceObject = "SubExpense2"
End Select
End If
End Sub

Thanks again.

 

[Douglas J. Steele]

I believe you need to check for Null outside of the Case statement:

If IsNull(MyValue) Then
'
Else
Select Case MyValue
Case
Case
End Select
End If

 

[nybaseball22]

Thank you Doug. I think I did this in my new code, didn't I? I am
not very experienced with this and changing code around, so you might
see something I don't. I tested it a little and it seems to be
working. I was trying to figure out how to have it default to or
change to a generic form if none of the cases match my selection. I
figure I could use <>, but then I would have to list all the previous
cases, wouldn't I? Is there a better way?

 

[Douglas J. Steele]

Yes, looks like that. However, that wasn't the note to which I was
replying...

The SELECT CASE statement allows the inclusion of CASE ELSE at the end to
handle any cases that weren't explicitly caught.

 

[nybaseball22]

Excellent, and sorry for the confusion. Thanks again Doug.