End If without Block If

[Jen]

I'm trying to run a not in list event that will take input from an input box
and insert this as a new record in the corresponding table. I've used similar
code in the past, but this one isn't working. I'm getting an "End If Without
Block If" error, but for the life of me I can't find where the problem is.
The only thing that's different from the way I usually use similar code is
the Mark1 thing - I don't usually use markers like this, so maybe it's
causing the problem.

Here is the code:

Private Sub Agency_NotInList(NewData As String, Response As Integer)
Dim Response2, Response3 As Integer
Dim GetAgency As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Response = MsgBox("This agency is not in the list. Would you like to add
it?", vbYesNo, "Add Agency?")
If Response = vbNo Then
Response2 = MsgBox("Please select an agency from the list.",
vbOKOnly + vbInformation, "Select From List")
Exit Sub
Else
Mark1:
GetAgency = InputBox("Enter the agency name:", "Enter Agency Name")
If IsNull(GetAgency) Then Response3 = MsgBox("You must enter a
name for the agency.", vbOKOnly + vbExclamation, "Agency Name is Mandatory")
GoTo Mark1
End If

Set db = CurrentDb
Set rs = db.OpenRecordset("Agencies", dbOpenDynaset)
rs.AddNew
rs!AgencyName = GetAgency
rs.Update
End If
Me.Requery

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Thanks in advance for any suggestions.

 

[Dirk Goldgar]

In

I'm trying to run a not in list event that will take input from an
input box and insert this as a new record in the corresponding table.
I've used similar code in the past, but this one isn't working. I'm
getting an "End If Without Block If" error, but for the life of me I
can't find where the problem is. The only thing that's different from
the way I usually use similar code is the Mark1 thing - I don't
usually use markers like this, so maybe it's causing the problem.

Here is the code:

Private Sub Agency_NotInList(NewData As String, Response As Integer)
Dim Response2, Response3 As Integer
Dim GetAgency As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Response = MsgBox("This agency is not in the list. Would you like
to add it?", vbYesNo, "Add Agency?")
If Response = vbNo Then
Response2 = MsgBox("Please select an agency from the list.",
vbOKOnly + vbInformation, "Select From List")
Exit Sub
Else
Mark1:
GetAgency = InputBox("Enter the agency name:", "Enter Agency
Name")
If IsNull(GetAgency) Then Response3 = MsgBox("You
must enter a
name for the agency.", vbOKOnly + vbExclamation, "Agency Name is
Mandatory") GoTo Mark1
End If

Set db = CurrentDb
Set rs = db.OpenRecordset("Agencies", dbOpenDynaset)
rs.AddNew
rs!AgencyName = GetAgency
rs.Update
End If
Me.Requery

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Thanks in advance for any suggestions.

This If statement:

If IsNull(GetAgency) Then Response3 = MsgBox("You
must enter a
name for the agency.", vbOKOnly + vbExclamation, "Agency Name is
Mandatory")
GoTo Mark1
End If

.... is a single-line If, so the following End If statement is considered
to apply to the enclosing If. I think this is what you wanted:

If IsNull(GetAgency) Then
Response3 = MsgBox( _
"You must enter a name for the agency.", _
vbOKOnly + vbExclamation, _
"Agency Name is Mandatory")
GoTo Mark1
End If

That should fix your immediate problem, but I would recommend rewriting
your code to avoid using the GoTo statement.

 

[Douglas J. Steele]

If IsNull(GetAgency) Then Response3 = MsgBox("You must enter a name for
the agency.", vbOKOnly + vbExclamation, "Agency Name is Mandatory")
GoTo Mark1
End If

should be

If IsNull(GetAgency) Then
Response3 = MsgBox("You must enter a name for the agency.", vbOKOnly +
vbExclamation, "Agency Name is Mandatory")
GoTo Mark1
End If

although I prefer avoiding GoTo statements, and using:

GetAgency = InputBox("Enter the agency name:", "Enter Agency Name")
Do While IsNull(GetAgency)
Response3 = MsgBox("You must enter a name for the agency.", vbOKOnly +
vbExclamation, "Agency Name is Mandatory")
GetAgency = InputBox("Enter the agency name:", "Enter Agency Name")
Loop

 

[Jen]

Thanks Dirk and Doug - this works great. I didn't know how to use the Do
While...Loop but that is definitely a lot cleaner.

Thanks!

 

[missinglinq via AccessMonster.com]

It's been a long time, hasn't it Doug, since Gotos and spaghetti code was the
norm?

 

[Douglas J. Steele]

Even worse was Cobol with its ALTER statements that let you dynamically
change where a GOTO went!