Else If - Problem

[BBBryan]

Hi,
Can Someone help me.
I have a form where on-load I want to have a code to check and see if there are Items in 3 querys. It should Bypass and no msgbox if none, but open a msgbox to ask whether to view these or not if there is one or more. The problem I am having is If the first query has one or more the msgbox will open and if I say yes the code stops at this query if I say no it runs to the next query. What I want is the code run to the next 2 querys whether I say yes or no if there is more than none open Msgbox Bypass if none.
I don't know I may be using the wrong procedure or need add some more code in this one?
Here is the code I have now.
Private Sub Form_Load()
Dim intStoreW As Integer
Dim intStoreG As Integer
Dim intStoreT As Integer
intStoreW = DCount("[TicketType]", "[Qy WelderQualificationReminders]", "[ReleaseDate] Is Null And [ExpiryDate] < Now() Or [ExpiryDate] Between Now() And Now()+30")
intStoreG = DCount("[GaugeNo]", "[Qy GaugeCalibrationReminders]", "[CalibrationExpiry] < Now() Or [CalibrationExpiry] Between Now() And Now()+60")
intStoreT = DCount("[TorqueWrenchNo]", "[Qy TorqWCalibrationReminders]", "[TCalibrationExpiry] < Now() Or [TCalibrationExpiry] Between Now() And Now()+60")
If intStoreW = 0 Then
Exit Sub
Else
If MsgBox("There is " & intStoreW & " Welder Qualification Tickets Either Expired or to be within 30 days" & _
vbCrLf & vbCrLf & "Open To View", _
vbYesNo, "Welder Quals to Expire within 30 days...") = vbYes Then
DoCmd.OpenQuery "Qy WelderQualificationReminders"
Else
If intStoreG = 0 Then
Exit Sub
Else
If MsgBox("There is " & intStoreG & " Gauges Either Expired or to be within 60 days" & _
vbCrLf & vbCrLf & "Open To View", _
vbYesNo, "Gauges to Expire within 60 days...") = vbYes Then
DoCmd.OpenQuery "Qy GaugeCalibrationReminders"
Else
If intStoreT = 0 Then
Exit Sub
Else
If MsgBox("There is " & intStoreT & " TorqueWreches Either Expired or to be within 60 days" & _
vbCrLf & vbCrLf & "Open To View", _
vbYesNo, " TorqueWreches to Expire within 60 days...") = vbYes The
DoCmd.OpenQuery "Qy TorqWCalibrationReminders"
Exit Sub
Exit Sub
Exit Sub
Exit Sub
Exit Sub
Exit Sub
End If
End If
End If
End If
End If
End If
End Sub

Thanks BBryan

 

[pr2-eugin]

A proper horizontal intend would help read your code properly.. here is it.. so..

Private Sub Form_Load()
    Dim intStoreW As Integer
    Dim intStoreG As Integer
    Dim intStoreT As Integer
    intStoreW = DCount("[TicketType]", "[Qy WelderQualificationReminders]", "[ReleaseDate] Is Null And [ExpiryDate] < Now() Or [ExpiryDate] Between Now() And Now()+30")
    intStoreG = DCount("[GaugeNo]", "[Qy GaugeCalibrationReminders]", "[CalibrationExpiry] < Now() Or [CalibrationExpiry] Between Now() And Now()+60")
    intStoreT = DCount("[TorqueWrenchNo]", "[Qy TorqWCalibrationReminders]", "[TCalibrationExpiry] < Now() Or [TCalibrationExpiry] Between Now() And Now()+60")
    If intStoreW = 0 Then
        Exit Sub
    Else
        If MsgBox("There is " & intStoreW & " Welder Qualification Tickets Either Expired or to be within 30 days" & _
                    vbCrLf & vbCrLf & "Open To View", _
                    vbYesNo, "Welder Quals to Expire within 30 days...") = vbYes Then
            DoCmd.OpenQuery "Qy WelderQualificationReminders"
        Else
            If intStoreG = 0 Then
                Exit Sub
            Else
                If MsgBox("There is " & intStoreG & " Gauges Either Expired or to be within 60 days" & _
                            vbCrLf & vbCrLf & "Open To View", _
                            vbYesNo, "Gauges to Expire within 60 days...") = vbYes Then
                    DoCmd.OpenQuery "Qy GaugeCalibrationReminders"
                Else
                    If intStoreT = 0 Then
                        Exit Sub
                    Else
                        If MsgBox("There is " & intStoreT & " TorqueWreches Either Expired or to be within 60 days" & _
                                    vbCrLf & vbCrLf & "Open To View", _
                                    vbYesNo, " TorqueWreches to Expire within 60 days...") = vbYes Then
                                DoCmd.OpenQuery "Qy TorqWCalibrationReminders"
                                Exit Sub
                                Exit Sub
                                Exit Sub
                                Exit Sub
                                Exit Sub
                                Exit Sub
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

see anything interesting? I will look into the code as well..

 

[BBBryan]

Hi Paul,
Sorry about I should have changed change it.
I do have the code like that in my database. When I copied it and pasted it into the thread it went all jumbed.

I still am not sure what to do to make the code run threw and check all 3 querys.
As you see after it executes somthing it stops the code from continuing to run to the end.
Thanks BBryan

 

[pr2-eugin]

Why do you have Else If then?? You need is three If statements right? Something like?

Private Sub Form_Load()
    Dim intStoreW As Integer
    Dim intStoreG As Integer
    Dim intStoreT As Integer
    
    intStoreW = DCount("[TicketType]", "[Qy WelderQualificationReminders]", "[ReleaseDate] Is Null And [ExpiryDate] < Now() Or [ExpiryDate] Between Now() And Now()+30")
    intStoreG = DCount("[GaugeNo]", "[Qy GaugeCalibrationReminders]", "[CalibrationExpiry] < Now() Or [CalibrationExpiry] Between Now() And Now()+60")
    intStoreT = DCount("[TorqueWrenchNo]", "[Qy TorqWCalibrationReminders]", "[TCalibrationExpiry] < Now() Or [TCalibrationExpiry] Between Now() And Now()+60")
    
    If intStoreW = 0 And intStoreG = 0 And intStoreT = 0 Then
        Exit Sub
    Else
        If intStoreW <> 0 Then
            If MsgBox("There is " & intStoreW & " Welder Qualification Tickets Either Expired or to be within 30 days" & _
                        vbCrLf & vbCrLf & "Open To View", _
                        vbYesNo, "Welder Quals to Expire within 30 days...") = vbYes Then
                DoCmd.OpenQuery "Qy WelderQualificationReminders"
            End If
        End If
        
        If intStoreG <> 0 Then
            If MsgBox("There is " & intStoreG & " Gauges Either Expired or to be within 60 days" & _
                        vbCrLf & vbCrLf & "Open To View", _
                        vbYesNo, "Gauges to Expire within 60 days...") = vbYes Then
                DoCmd.OpenQuery "Qy GaugeCalibrationReminders"
            End If
        End If
                
        If intStoreT <> 0 Then
            If MsgBox("There is " & intStoreT & " TorqueWreches Either Expired or to be within 60 days" & _
                            vbCrLf & vbCrLf & "Open To View", _
                            vbYesNo, " TorqueWreches to Expire within 60 days...") = vbYes Then
                DoCmd.OpenQuery "Qy TorqWCalibrationReminders"
            End If
        End If
    End If
End Sub

 

[BBBryan]

Thanks Paul that works perfect.
I should have known that. I guess I was thinking it was harder that it should have been. Still trying to Learn....
Thanks for your Help!

BBryan

 

[boblarson]

Hi Paul,
Sorry about I should have changed change it.
I do have the code like that in my database. When I copied it and pasted it into the thread it went all jumbed.

If you use Code Tags before hitting the Submit key on the forum, it should retain the formatting (indents, etc.). I have found sometimes you have to go back and put in blank lines to break it up a bit but that is minor. Also, sometimes it doesn't paste right so I paste into NOTEPAD and then copy from there and paste here.

 

[pr2-eugin]

You are most welcome

 

[BBBryan]

Thanks I will try these tags next time

Thanks BBryan