Mulitple IF Statements within an IF statement

[gold007eye]

How do I use IF statements within an Existing IF statement to accomplish different outcomes? I have been trying different code for a while now and can't seem to get it to do what I want it to.

Here is what I want the code to do.

1st - if the field "Provider Number" conists of 6 or 8 characters then start the
1st paragraph of code for that criteria.... afterwhich if the "extension"
field is still NULL then run the If IsNull code. Otherwise if
the "extension" field is NOT NULL after the 1st paragraph of code has
been run for this criteria; then run the 1st ElseIf code for this criteria.

2nd - if the field "Provider Number" conists of 7 or 9 characters then start
the 1st paragraph of code for that criteria.... afterwhich if
the "extension" field is still NULL then run the If IsNull code. Otherwise
if the "extension" field is NOT NULL after the 1st paragraph of code has
been run for this criteria; then run the 1st ElseIf code for this criteria.

Can someone tell me what I am doing wrong or how I should go about this?

Private Sub Add_Reassignment_Click()
On Error GoTo Add_Reassignment_Click_Err
DoCmd.GoToRecord , , acNewRec

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    
    If rs.EOF Or Not Me.NewRecord Then
        ' don't do anything if there's no records or it is not a new record
    Else
        With rs
            
            .MoveLast
                       
    If Len([Forms]![Existing SSN]![Provider Number]) = 7 Or 9 Then
            Me![BaseID] = .Fields("BaseID")
            Me![SSN] = .Fields("SSN")
            Me![Extension] = .Fields("Extension")
            Me![SFDate] = Now()
            Forms![Existing SSN]![TimeStamp] = Time()
            Forms![Existing SSN]![TimeStamp].Requery
                        
        If IsNull([Extension]) Then
            Me![Extension] = [Forms]![Existing SSN]![Starting Extension]
            Me![Provider Number] = Format([BaseID], "0000000") & Format([Extension], "00")
            DoCmd.RunCommand acCmdSaveRecord
        ElseIf Len([Forms]![Existing SSN]![Provider Number]) = 7 Or 9 And _
            Me![Extension] = Not Null Then
            Me![Extension] = Format(Nz(DMax("[Extension]", "[Provider Number Information]", "[Starting Extension]" = Forms![Existing SSN]![Starting Extension]), [Extension]) + 1)
            Me![Provider Number] = Format([BaseID], "0000000") & Format([Extension], "00")
            DoCmd.RunCommand acCmdSaveRecord
        End If
    ElseIf Len([Forms]![Existing SSN]![Provider Number]) = 6 Or 8 Then
            Me![SSN] = .Fields("SSN")
            Me![Extension] = .Fields("Extension")
            Me![SFDate] = Now()
            Forms![Existing SSN]![TimeStamp] = Time()
            Forms![Existing SSN]![TimeStamp].Requery
                        
        If IsNull([Extension]) Then
            Me![Extension] = [Forms]![Existing SSN]![Starting Extension]
            Me![Provider Number] = Forms![Existing SSN]![Provider Number] & Format([Extension], "00")
            DoCmd.RunCommand acCmdSaveRecord
        ElseIf Len([Forms]![Existing SSN]![Provider Number]) = 6 Or 8 And _
            Me![Extension] = Not Null Then
            Me![Extension] = Format(Nz(DMax("[Extension]", "[Provider Number Information]", "[Starting Extension]" = Forms![Existing SSN]![Starting Extension]), [Extension]) + 1)
            Me![Provider Number] = Forms![Existing SSN]![Provider Number] & Format([Extension], "00")
            DoCmd.RunCommand acCmdSaveRecord
        End If
    End If
            Forms![Existing SSN]![Provider Number] = [Provider Number]
            Forms![Existing SSN]![Provider Number].Requery
            Me![Dummy].SetFocus
        
        End With

    End If
    
Add_Reassignment_Click_Exit:
    Exit Sub
    
Add_Reassignment_Click_Err:
    If Err = 2113 Then 'Add Reassignment Button was clicked too fast
        DoCmd.RunCommand acCmdUndo
        Exit Sub
    ElseIf Err = 2105 Then 'Can't go to specified record
        DoCmd.RunCommand acCmdUndo
        Exit Sub
    ElseIf Err = 3163 Then 'Record Extension 99 has been reached
        MsgBox "This provider number can't have anymore extensions..." & vbCrLf & vbCrLf & "99 is the maximum number of allowable reassignments!", vbExclamation, "99 Reassignments Reached..."
        DoCmd.RunCommand acCmdUndo
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Add_Reassignment_Click_Exit
    End If
End Sub

 

[wazz]

you've got "DoCmd.GoToRecord , , acNewRec" under your "On Error" line which is going to cancel everything because you've got "If rs.EOF Or Not Me.NewRecord Then".

 

[Steve R.]

My response, based on an incomplete read, is to use the Case Statement. The case statement would be your "first" cut based on the number of characters, then use the IF statements to further focus the results. (The case statement can also be nested.) The case statement in some situations makes it a lot easier to follow the logic.

 

[gold007eye]

Could you give me an example of how to use Case statements for this situation? I had thought about that after reading someone else saying Case statements are good when nesting is needed, but not sure exactly how to go about it.

 

[The_Doc_Man]

At my first reading, this is NOT a CASE candidate because the criiteria are not all of the same flavor because of the added ISNULL tests. This is where nested IF/THEN/ELSEIF.../END IF statements are going to be required.

You might use a case like this if you had a scratch integer handy...

I = LEN( ...)

SELECT I
  CASE 7,9
    do something
  CASE 6, 8
    do something different
  CASE ELSE
    throw a hissy-fit
END SELECT

If you need subsequent tests after characterizing the length fields, that could go inside where you "do something" or "do something else" or "throw a hissy fit."

 

[oldozziesubby]

Have you tried using the IIF statement. ie IIF( test,true,false)

 

[gold007eye]

@The Doc Man
I might be able to work with the Case statement. I hadn't used it in that sense before. I will have to try it out.

@oldozziesubby
I am not very familiar with using the IIF statement; for some reason I thought that was used for criteria in queries.

 

[oldozziesubby]

I am not very familiar with using the IIF statement; for some reason I thought that was used for criteria in queries.

The 'Case Select' statement is the best, it's faster and more versatile that the 'If Then ElseIf Endif'. However I believe there is a case for using the IIF function in VBA, especially if you only need two or three results.

 

[The_Doc_Man]

IIF works but is a function. Its overhead is higher than a compiled If/Then/Else combination.

 

[oldozziesubby]

I agree, but for a simple IIF(IsNull(field),do this,do that) it is far easier than
If Isnull(field) then
do this
Else
do that
endif
don't you think

 

[gold007eye]

So IIF statements are more or less 1 line statements without the need for EndIf / ElseIf / etc.?

 

[Steve R.]

See sample below of a nested "IIF" Statement. The code below displays the completion status ("Yes" or "No") of those projects that have a code (type) of less than 5.

=IIf([Type]<5,IIf([status]=11,"No","Yes")," ")

 

[gold007eye]

Any Suggestions on how to accomplish this?

Here i go again. lol. I think I am making things more complicated than they need to be. How would I go about conquering this to do what I want it to?

I am trying to get it to say Within the "IF DCount" statement IF [SSNLookup]=Null Then Do MsgBox (EIN Info) ElseIF [EINLookup]=Null Then Do MsgBox (SSN Info).

I have been trying to use the Select Case statement to get this to work to no avail. What am I doing wrong?

Private Sub MProvider_Number_AfterUpdate()
On Error GoTo Provider_Number_AfterUpdate_Err
'*********************************
'Code sample courtesy of srfreeman
'*********************************

Dim Provider_Number As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

MProvider_Number = Me.MProvider_Number.Value
stLinkCriteria = "[Provider Number]=" & "'" & [MProvider Number] & "'"

    'Check 'Provider Information' table for duplicate Provider Number
    If DCount("[Provider Number]", "[Provider Number Information]", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        'Message box warning of duplication
        Dim X As Variant
        Dim Y As Variant
        
        X = DLookup("[SSN]", "[Provider Number Information]", "[Provider Number]=[MProvider Number]")
        
        Me![SSNLookup] = X
        Me![SSNLookup].Requery
        
        Y = DLookup("[EIN]", "[Provider Number Information]", "[Provider Number]=[MProvider Number]")
        
        Me![EINLookup] = Y
        Me![EINLookup].Requery
              
        Select Case Y
        Case Null
        MsgBox "Warning! Provider Number " _
        & Format([MProvider Number], ">") & " is already in the system associated with SSN: " _
        & Format([SSNLookup], "000-00-0000") & vbCr & vbCr & "Please verify that the SSN you entered above & the Provider Number you entered are correct.", vbExclamation _
        , "Duplicate Provider Number Found..."
        End Select
        
        Select Case X
        Case Null
        MsgBox "Warning! Provider Number " _
        & Format([MProvider Number], ">") & " is already in the system associated with EIN: " _
        & Format([EINLookup], "00-0000000") & vbCr & vbCr & "Please verify that the EIN you entered above & the Provider Number you entered are correct.", vbExclamation _
        , "Duplicate Provider Number Found..."
        End Select
        
        Me![PNReset].SetFocus
    End If
Set rsc = Nothing

Provider_Number_AfterUpdate_Exit:
    Exit Sub

Provider_Number_AfterUpdate_Err:
    If Err = 2110 Then 'Can't move focus to control
        Exit Sub
    ElseIf Err = 94 Then 'Invalid Use of Null
        Exit Sub
    ElseIf Err = 2115 Then 'Can't save data at this time
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Provider_Number_AfterUpdate_Exit
    End If
End Sub

 

[Moniker]

You're not getting when to use Select Case statements. You use them to avoid nested If/ElseIf type constructs, not when there's only one thing you're checking for (a NULL in your case). There is also no reason for the X and Y variables that I can see, and same goes with the strLinkCriteria. Just refer to the data directly without assigning it unless you have to use it multiple times. The construct you had was the equivalent of going to a gas station, putting the gas into a gas container, and then pouring it out of the container and in your car. Why use the container when there's no need for it?

Look at the code below, especially at the error handling at the bottom, which is where I placed in the proper usage of a Select Case statement.

On Error GoTo Provider_Number_AfterUpdate_Err

    Dim Provider_Number As String
    Dim Provider_Number As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    Provider_Number = Me.MProvider_Number.Value

    'Check 'Provider Information' table for duplicate Provider Number
    If DCount("[Provider Number]", "[Provider Number Information]", "[Provider Number]=" & "'" & [MProvider Number] & "'") > 0 Then
        'Undo duplicate entry
        'Message box warning of duplication
    End If
        
    If IsNull(DLookup("[SSN]", "[Provider Number Information]", "[Provider Number]=[MProvider Number]")) Then
        MsgBox "Warning! Provider Number " _
        & Format([MProvider Number], ">") & " is already in the system associated with SSN: " _
        & Format([SSNLookup], "000-00-0000") & vbCr & vbCr & "Please verify that the SSN you entered above & the Provider Number you entered are correct.", vbExclamation _
        , "Duplicate Provider Number Found..."
    End If
    
    If IsNull(DLookup("[EIN]", "[Provider Number Information]", "[Provider Number]=[MProvider Number]")) Then
        MsgBox "Warning! Provider Number " _
        & Format([MProvider Number], ">") & " is already in the system associated with EIN: " _
        & Format([EINLookup], "00-0000000") & vbCr & vbCr & "Please verify that the EIN you entered above & the Provider Number you entered are correct.", vbExclamation _
        , "Duplicate Provider Number Found..."
    End If
        
    Me![PNReset].SetFocus

    Set rsc = Nothing

Provider_Number_AfterUpdate_Exit:
    Exit Sub

Provider_Number_AfterUpdate_Err:
    Select Case Err
	Case 2110
            'Can't move focus to control
            Exit Sub
        Case 94
            'Invalid Use of Null
            Exit Sub
        Case 2115 Then 
            'Can't save data at this time
            Exit Sub
        Case Else
            MsgBox Err.Number & " - " & Err.Description
            Resume Provider_Number_AfterUpdate_Exit
    End Select

End Sub

 

[gold007eye]

Moniker

Thanks for the Case usage for error handling that works good. I guess I should explain better what I was trying to get the code to do. I tried using your code and everytime I enter a number (regardless of if it is in the system or not) it keeps throwing the error message based on SSN / EIN. If the number isn't in the system it shouldn't throw any message it should just proceed as normal.

What I was trying to accomplish was once someone put a Provider number in the field (IF) the system found the number is currently in the system (using the DCount) then to lookup the SSN & EIN fields in associated with that duplicate number (from the table) and then put that data in the SSNLookup / EINLookup fields on the form. Then if the DCount was >0 AND the SSN field IsNull then throw the EIN Error message (which should also display the value of the EINLookup field in the error message)

Or if DCount >0 AND the EIN field IsNull then throw the SSN Error message.

If DCount <= 0 Then no error message should display.

Does that make a little more sense?

 

[gold007eye]

Got it working:

Ok. well for some reason any time I removed the stLinkCriteria code an error would get thrown everytime a provider number was entered into the field even if it the count was 0 so here is the code I used and was able to get everything working like I needed.

On Error GoTo Provider_Number_AfterUpdate_Err

Dim Provider_Number As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

MProvider_Number = Me.MProvider_Number.Value
stLinkCriteria = "[Provider Number]=" & "'" & [MProvider Number] & "'"
        
[SSNLookup] = DLookup("[SSN]", "[Provider Number Information]", "[Provider Number]=[MProvider Number]")
[EINLookup] = DLookup("[EIN]", "[Provider Number Information]", "[Provider Number]=[MProvider Number]")

'Check 'Provider Information' table for duplicate Provider Number
If DCount("[Provider Number]", "[Provider Number Information]", stLinkCriteria) > 0 And _
   IsNull([EINLookup]) Then
        MsgBox "Warning! Provider Number " _
        & Format([MProvider Number], ">") & " is already in the system associated with SSN: " _
        & Format([SSNLookup], "000-00-0000") & vbCr & vbCr & "Please verify that the SSN you entered above & the Provider Number you entered are correct.", vbExclamation _
        , "Duplicate Provider Number Found..."
        Me![PNReset].SetFocus

ElseIf DCount("[Provider Number]", "[Provider Number Information]", stLinkCriteria) > 0 And _
   IsNull([SSNLookup]) Then
        MsgBox "Warning! Provider Number " _
        & Format([MProvider Number], ">") & " is already in the system associated with EIN: " _
        & Format([EINLookup], "00-0000000") & vbCr & vbCr & "Please verify that the EIN you entered above & the Provider Number you entered are correct.", vbExclamation _
        , "Duplicate Provider Number Found..."
        Me![PNReset].SetFocus
    End If
Set rsc = Nothing

Provider_Number_AfterUpdate_Exit:
    Exit Sub

Provider_Number_AfterUpdate_Err:
Select Case Err
    Case 2110 'Can't move focus to control
        Exit Sub
    Case 94 'Invalid Use of Null
        Exit Sub
    Case 2115 'Can't save data at this time
        Exit Sub
    Case Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Provider_Number_AfterUpdate_Exit
    End Select