End with without block with ERROR 1

[sanders720]

Everything works until I get to the if then statement, when I get an "end with without block if" error. Can you tell me why?

Private Sub Command4_Click()
Dim sql As String
Dim rs As Recordset
Dim rscheck As Boolean

sql = "SELECT InAssy_Yes FROM tblSubAssyListing WHERE JobNo = " & Combo8.Value & " AND SubAssy = '" & Combo22.Value & "'"

Set rs = CurrentDb.OpenRecordset(sql)
Set rscheck = rs("InAssy_Yes&quot
rs.Close
Set rs = Nothing

If rscheck = True Then
MsgBox ("You must do an ECO to ADD NEW, MODIFY, REMAKE or QUANTITY CHANGE Parts on this Sub&quot
Else
OpenReport "rptManufactured Parts List", True
End If

End Sub

Thank You

 

[fullkeel]

One line that has to go is
Set rscheck = rs("InAssy_Yes&quot

You have dimensioned rscheck as Boolean and now you are trying to 'Set' it to a recordset? This confuses me.

Bruce Gregory

 

[sanders720]

Manually, rs("InAssy_Yes&quot has a value of True. Isn't that boolean? rs is a recordset, but the combined statement produces a boolean value. Do I need to use a variable for this, or is there another problem?

Thanks again, in advance for the help.

 

[jebry]

Hi!

In your else statement you use OpenReport. In A2k you would need DoCmd.OpenReport(etc.). Is this a user defined function? If it is, then Access may be having problems because have given it the same name as one of its actions and you should probably rename it. If not, then you can try adding the DoCmd.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[fullkeel]

You don't even have to go as far as using a variable.

If rs("InAssy_Yes&quot then
.....
....
end if

Bruce Gregory

 

[Craig0201]

Sanders,

The code is pretty unreadable......

rs is normally used as a prefix for recordsets themselves, not values of a field within a recordset. Hence the code would read much better as....

Dim strSQL As String
Dim rs As Recordset
Dim blnCheck As Boolean

strSQL = "SELECT InAssy_Yes FROM tblSubAssyListing WHERE JobNo = " & Combo8.Value & " AND SubAssy = '" & Combo22.Value & "'"

Set rs = CurrentDb.OpenRecordset(strSQL)
blnCheck = rs("InAssy_Yes&quot
rs.Close
Set rs = Nothing

If blnCheck = True Then
MsgBox ("You must do an ECO to ADD NEW, MODIFY, REMAKE or QUANTITY CHANGE Parts on this Sub&quot
Else
DoCmd.OpenReport "rptManufactured Parts List", True
End If

What I have done is prefixed the variables according to their type. This improves the readability and hence eases debugging.

Craig