Enabling subform

S

Stephanie

Hi. I have a form that has 2 subforms, and based on values in the form I'd
like one subform activated and the other grayed-out. The main form,
Individuals, is a tabbed form, if that matters. I always hate to show my
code, as it is usually very bad, but here goes:

Private Sub Form_Current()
If MemberCategoryID = 1 Then
Me.sfrmBucksByFamily.Enabled = Nz((Me.PrimaryFamilyMember = "Yes"),
False)
Else
Me.sfrmITABucksSum.Enabled = Nz((Me.PrimaryFamilyMember = "No"), True)
End If

End Sub

In which, I managed to always gray-out the sfrmBucksByFamily, and not
accomplish anything. I'd appreciate any suggestions. If possible, I'd like
the subforms to sit on top of each other, and just show the appropriate one.

I also want to make sure that I don't have difficulties with entering New
Records- some of my attempts at Enable make it so I get errors before I've
even done anything when entering a new record (I think Nz is suppose to help
with that).

I appreciate the help!
 
O

Ofer

Try this

If MemberCategoryID = 1 Then
If Me.PrimaryFamilyMember = "Yes" Then
Me.sfrmBucksByFamily.Enabled = True
Me.sfrmITABucksSum.Enabled = False
else
if Me.PrimaryFamilyMember ="No" And not isnull(Me.SignificantOtherID)
Then
Me.sfrmBucksByFamily.Enabled = False
Me.sfrmITABucksSum.Enabled = False
Else
Me.sfrmITABucksSum.Enabled = True
Me.sfrmBucksByFamily.Enabled = False
end if
End If
End If
 
S

Stephanie

Hmm.

In my first record, the person has the correct memberCategoryID (which I
told you incorrectly- I'm looking for MemberCategoryID = 2) and is the
PrimaryFamilyMember (is "yes" the same as "-1"?), so I would expect:
sfrmBucksByFamily to be enabled and
sfrmITABucksSum to be grayed-out.
Yet both subforms are visible and when using the step-through code:
Me.sfrmBucksByFamily.Enabled = True
Me.sfrmITABucksSum.Enabled = True (when it should be False).

Also, the second record for which neither PrimaryFamilyMember=
"Yes" nor MemberCategoryID=2 is true, skips all of the code straight to End
Sub and still shows sfrmBucksByFamily as enabled, where I'd expect only to
see sfrmITABucksSum enabled. Any idea why it skips the code? I'm thinking
maybe the records need to be requeried? Where/how would I do that?

My third record has PrimaryFamilyMember = "no" but Me.SignificantOtherID =
95, so I would expect neither subform to show. However, in the second
section of code (the part I wanted to add)

Me.sfrmBucksByFamily.Enabled = False
Me.sfrmITABucksSum.Enabled = False

is completely skipped over, with the code going straight to the 3rd section
of code so that I am seeing sfrmITABucksSum (I want this grayed-out).

No wonder I'm not good at this- it's difficult. And I appreciate your help!
 
O

Ofer

I'm not sure what you are trying to do but try this and change the case to
suit your needs

If MemberCategoryID = 1 Then
If Me.PrimaryFamilyMember = "Yes" Then
Me.sfrmBucksByFamily.Enabled = True
Me.sfrmITABucksSum.Enabled = False
Else
Me.sfrmITABucksSum.Enabled = True
Me.sfrmBucksByFamily.Enabled = False

End If
End If

End Sub
 
S

Stephanie

Thanks, that's working pretty well!
Now a twist if you will, embedded Else statements?:

If MemberCategoryID = 1 Then
If Me.PrimaryFamilyMember = "Yes" Then
Me.sfrmBucksByFamily.Enabled = True
Me.sfrmITABucksSum.Enabled = False

here's what I'd like to add:
if Me.PrimaryFamilyMember ="No" And Me.SignificantOtherID Is Not Null Then
Me.sfrmBucksByFamily.Enabled = False
Me.sfrmITABucksSum.Enabled = False

(Then the last case)
Else
Me.sfrmITABucksSum.Enabled = True
Me.sfrmBucksByFamily.Enabled = False

End If
End If

Thanks for the help!
 
O

Ofer

What is the type of the field Me.PrimaryFamilyMember, if its true/False, you
need to write
if Me.PrimaryFamilyMember = True then

And not "yes"
 
S

Stephanie

Thanks for the reply.
Me.PrimaryFamilyMember is a yes/no field. When I scroll through the code
where I do have a PrimaryFamilyMember, the code shows as
Me.PrimaryFamilyMember= -1.

The part that I'm not understanding is where the code states (in the first
section):
Me.sfrmBucksByFamily.Enabled = True
Me.sfrmITABucksSum.Enabled = False

I thought that this code is telling the form to show sfrmBucksByFamily, but
don't show sfrmITABucksSum.

However, when I scroll through these two lines of code, both show as True
meaning that I see both subforms. Why is that happening?

Thanks.
 
S

Stephanie

Thanks for the offer.

The only case that sort of works is Case 2, but that only works if the
record that matches Case 2 is not after one that matches Case 1 (even though
Case 1 doesn't work). I think I must need to requery the subforms at some
point?

If MemberCategoryID = 2 Then
If Me.PrimaryFamilyMember = "-1" Then
'CASE1: show sfrmBucksByFamily but not sfrmITABucksSum
Me.sfrmBucksByFamily.Enabled = True
Me.sfrmITABucksSum.Enabled = False
Else
If Me.PrimaryFamilyMember = "0" And Not IsNull(Me.SignificantOtherID)
Then
'CASE 2:show neither subform
Me.sfrmBucksByFamily.Enabled = False
Me.sfrmITABucksSum.Enabled = False
Else
'CASE 3:MemberCategoryID <>2, so show sfrmITABucksSum but not
sfrmBucksByFamily
Me.sfrmITABucksSum.Enabled = True
Me.sfrmBucksByFamily.Enabled = False
End If
End If
End If
End Sub

Thanks.
 
O

Ofer

To check a yes/no field type it should be
If Me.PrimaryFamilyMember = yes then
Or
If Me.PrimaryFamilyMember = True then
Or
If Me.PrimaryFamilyMember = -1 then
Or
If Me.PrimaryFamilyMember = 0 then
Or
If Me.PrimaryFamilyMember = False then

You need to lose the double quote, try this first and then will continue,
because of this it exit the code without doing anything
 
S

Stephanie

Thanks for the syntax lesson. I've used -1 and 0 with no quotes.

Case 1 shows both subforms (should only show sfrmBucksByFamily)

Case 2 works on all records as appropriate, except where the previous record
meets Case 1 (if I click through the records and then come back to the
original Case 2 following a Case 1, then Case 2 works, which leads me to
believe that I must requery the subforms somewhere before each record).

Case 3 doesn't work (it shows sfrmITABucksSum but should show neither).

Thanks.
 
O

Ofer

It's very hard when I can't see the field type, and the form.
While in code, put a code break in the first IF line (press F9), run the
form, the code will stop in this line, step the code line by line (press F8)
in each line check the values of the fields, either by moving the mouse over
it, or using the immidiate windows (press Ctrl + G), write
?Me.MemberCategoryID
and press enter, the value of it should show on the buttom.
Check how the code runs, where it goes and why.
 
S

Stephanie

Thanks for the tips.


Ofer said:
It's very hard when I can't see the field type, and the form.
While in code, put a code break in the first IF line (press F9), run the
form, the code will stop in this line, step the code line by line (press F8)
in each line check the values of the fields, either by moving the mouse over
it, or using the immidiate windows (press Ctrl + G), write
?Me.MemberCategoryID
and press enter, the value of it should show on the buttom.
Check how the code runs, where it goes and why.
Click to expand...
 
Top