Multiple IF statement

[mg2rde]

I need to update various text boxes on a form...Fairly new to vba..thanks in advance

The codes update one and not the others...might need a Case Select...need help with setup

Private Sub Command89_Click()
If ((Forms!frmMain.frmSummary!Chronic >= 3 And Forms!frmMain.frmSummary!Chronic <= 4) Or (Forms!frmMain.frmSummary!Med_Prescribed >= 5 And Forms!frmMain.frmSummary!Med_Prescribed <= 7)) Then
Me.[CCS 3-4 andor 5-7 meds] = True
If (Forms!frmMain.frmSummary!CognitiveScore >= 0 And Forms!frmMain.frmSummary!CognitiveScore <= 2) Then
Me.[0-2 cognitive impairment-moderate] = True
If (Forms!frmMain.frmSummary!Functional >= 3 And Forms!frmMain.frmSummary!Functional <= 4) Then
Me.[3-4 moderate functional] = True
If (Forms!frmMain.frmSummary!SelfPerceived >= 6 And Forms!frmMain.frmSummary!SelfPerceived <= 7) Then
Me.[6-7 Fair-poor health status] = True
If (Forms!frmMain.frmSummary!HistoryofHospitalization >= 0 And Forms!frmMain.frmSummary!HistoryofHospitalization >= 1) Then
Me.[1hospitalization-homecare] = True
End If

End Sub

 

[thechazm]

Well first off you didn't say what the issue was. An error? Just trying to clean up your code? Whats the reason for the post.

Second all of the objects it looks like you are refering to are of the same form. So if the button that you are using is on that form then just use me.ctrlName.value to work with the data.

If it is on a different form than the one your button is on use the with statement.

Eg..
With Forms!frmMain.frmSummary
if .Chronic.value >= 3 And .Chronic.value <= 4 Then
' Do Something
elseif .Med_Prescribed.value >= 5 And .Med_Prescribed.value <= 7 Then
Me.[CCS 3-4 andor 5-7 meds] = True
end if
end with

Let us know what you need.

 

[mg2rde]

sorry...

I am trying to update a group of checkboxes using a command button with codes.

I need the codes to loop to the next if statement. I tried your with if and receive the following error:run-time error 438-object support this property or method...thanks again

 

[thechazm]

Well the code I provided was just an example. I did not test it but I have done many things this way. You might want to try replacing the periods with ! like you had before and maybe drop the .value off of the end of the names.

 

[VilaRestal]

Important thing to point out is that every if needs an end if unless it's a single line version

e.g.
If condition = True Then DoStuff
is fine

If condition = True Then
DoStuff

won't work (it needs an end if)

If condition1 Then
DoStuff1
If condition2 Then
DoStuff2
If condition3 Then
DoStuff3
End If

won't work either. It's missing 2 End Ifs

To make it work it would have to be:

If condition1 Then
DoStuff1
End If
If condition2 Then
DoStuff2
End If
If condition3 Then
DoStuff3
End If

or perhaps:

If condition1 Then
DoStuff1
ElseIf condition2 Then
DoStuff2
ElseIf condition3 Then
DoStuff3
End If

 

[VilaRestal]

Secondly you need to ascertain is it frmMain or frmSummary

Forms!frmMain.frmSummary!Chronic is going to be wrong.
It should either be
Forms!frmMain!Chronic
or
Forms!frmSummary!Chronic

As you appear to be constantly referring to fields on the same form you could use a With statement to simplify the code and improve performance as per thechasm's example.

With all that in mind I'm going to guess your code should be:

Private Sub Command89_Click()
    With Forms!frmMain
        If ((!Chronic >= 3 And !Chronic <= 4) Or (!Med_Prescribed >= 5 And !Med_Prescribed <= 7)) Then
            Me.[CCS 3-4 andor 5-7 meds] = True
        End If
        If (!CognitiveScore >= 0 And !CognitiveScore <= 2) Then
            Me.[0-2 cognitive impairment-moderate] = True
        End If
        If (![URL="http://www.access-programmers.co.uk/forums/showthread.php?p=1078063#"]Functional[/URL] >= 3 And !Functional <= 4) Then
            Me.[3-4 moderate functional] = True
        End If
        If (!SelfPerceived >= 6 And !SelfPerceived <= 7) Then
            Me.[6-7 Fair-poor health status] = True
        End If
        If (!HistoryofHospitalization >= 0 And !HistoryofHospitalization >= 1) Then
            Me.[1hospitalization-homecare] = True
        End If
    End With
End Sub

 

[VilaRestal]

That could be rewritten like this:

Private Sub Command89_Click()
    With Forms!frmMain
        Me.[CCS 3-4 andor 5-7 meds] = ((!Chronic >= 3 And !Chronic <= 4) Or (!Med_Prescribed >= 5 And !Med_Prescribed <= 7)) 
        Me.[0-2 cognitive impairment-moderate] = (!CognitiveScore >= 0 And !CognitiveScore <= 2) 
        Me.[3-4 moderate functional] = (![URL="http://www.access-programmers.co.uk/forums/showthread.php?p=1078063#"]Functional[/URL] >= 3 And !Functional <= 4)
        Me.[6-7 Fair-poor health status] = (!SelfPerceived >= 6 And !SelfPerceived <= 7)
        Me.[1hospitalization-homecare] = (!HistoryofHospitalization >= 0 And !HistoryofHospitalization >= 1)
    End With
End Sub

Although there is a slight difference in behavior in that this version will set those fields to false if the conditions aren't true whereas the previous version will leave them untouched.

 

[thechazm]

lol VilaRestal you posted so fast that everytime I read one of them you already had another posted

 

[VilaRestal]

I'll take that as a complement
Thank you

 

[mg2rde]

thanks to all but VilaRestal your solution was dead on....thanks for moving my project forward