Code Problem

  • Thread starter Thread starter RobVT
  • Start date Start date
R

RobVT

Using Access 2003.
I'm writing code for a couple message boxes that inform the user of the
number of query results before the query actually opens. If there are
no results, the user is asked to go back and revise their input on the
intro forms. If there is one, the message is singular, asking if
they'd like to proceed. And if there are more than one result, the
message is plural, asking if they'd like to proceed.
Here is a look at my code:

Private Sub cmdPCRRNext_Click()
Dim PCRRRes As String
Dim stDocName As String
Dim RRNoRes As String
Dim PCRR As Long

PCRR = DCount("*", "qryPCRoadRecon")

If PCRR = 0 Then
RRNoRes = MsgBox("There are no projects that match your criteria.
Please revise your project information or characteristics and try
again.", vbOKOnly, "Matching Error")
If RRNoRes = vbOK Then GoTo 10

ElseIf PCRR = 1 Then
PCRRRes = MsgBox("There is " & PCRR & " project that matches your
criteria. Would you like to continue?", vbYesNo, "Project
Characteristics Search Results")
If PCRRRes = vbYes Then
stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else: GoTo 10

ElseIf PCRR > 1 Then
PCRRRes = MsgBox("There are " & PCRR & " projects that match your
criteria. Would you like to continue?", vbYesNo, "Project
Characteristics Search Results")
If PCRRRes = vbYes Then
stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else: GoTo 10

10 End If
End Sub

For some reason, I keep getting an "Else without If" error when I try
to run the code (using a command button). I've been studying the code
and I can't figure out why it's doing that. I figured a fresh set of
eyes would help. Any suggestions?

Any help is much appreciated. Thanks and have a great day!
 
You code is kinda confusing. You might try a select statement instead
of using so many nested if statement. I find them much easier to
debug. It would look like this:

Select Case PCRR
Case 0
RRNoRes = MsgBox("There are no projects that match your
criteria.
Please revise your project information or
characteristics and try
again.", vbOKOnly, "Matching Error")

Case 1
PCRRRes = MsgBox("There is " & PCRR & " project that
matches your
criteria. Would you like to continue?", vbYesNo,
"Project
Characteristics Search Results")
If PCRRRes = vbYes Then
stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End IF

Case Else
PCRRRes = MsgBox("There are " & PCRR & " projects that
match your
criteria. Would you like to continue?", vbYesNo,
"Project
Characteristics Search Results")
If PCRRRes = vbYes Then
stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End IF
End Select

I believe you will find this much easier to follow and debug should
there be any problems with it. Hope that helps!
 
You have a couple of block Ifs without End If. It may be that the compiler
is getting confused. Also, there is no need for the GoTo 10 statements.
Since the next part of the If will be false, it will be skipped and you'll
wind up at 10 anyway.
If PCRR = 0 Then
RRNoRes = MsgBox("There are no projects that match your criteria.
Please revise your project information or characteristics and try
again.", vbOKOnly, "Matching Error")
If RRNoRes = vbOK Then GoTo 10

ElseIf PCRR = 1 Then
PCRRRes = MsgBox("There is " & PCRR & " project that matches your
criteria. Would you like to continue?", vbYesNo, "Project
Characteristics Search Results")
If PCRRRes = vbYes Then
stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else: GoTo 10 'Missing End If

ElseIf PCRR > 1 Then
PCRRRes = MsgBox("There are " & PCRR & " projects that match your
criteria. Would you like to continue?", vbYesNo, "Project
Characteristics Search Results")
If PCRRRes = vbYes Then
stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else: GoTo 10 'Missing End If

10 End If
Click to expand...

You may actually find a Select Case statement easier to read here. That will
frequently be the case when you start using ElseIf.

Select Case PCRR
Case 0
RRNoRes = MsgBox("There are no projects that match your criteria.
Please revise your project information or characteristics and try again.",
vbOKOnly, "Matching Error")

Case 1
PCRRRes = MsgBox("There is " & PCRR & " project that matches your
criteria. Would you like to continue?", vbYesNo, "Project Characteristics
Search Results")
If PCRRRes = vbYes Then stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Case Is > 1
PCRRRes = MsgBox("There are " & PCRR & " projects that match your
criteria. Would you like to continue?", vbYesNo, "Project Characteristics
Search Results")
If PCRRRes = vbYes Then stDocName = "qryRoadReconRes"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Case Else
MsgBox "Error in Select Case statement.", vbOkOnly + vbCritical
End Select

Adjust for the newsreader wrapping long lines.
 
Works like a charm. Thanks for your help. I had never heard of this
Select Case but I think I like it a lot better than a bunch of "ifs".
Have a great weekend!
 
Back
Top