rs.eof/rs.bof not returning a value

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP Pro.

I have code using the rs.eof to check if a query is empty. But it's not
returning a value. Mousing over rs.eof in the code gives:
<object variable or with block variable not set>

This apparently functions as TRUE if when it shouldn't.



Here is code:

Private Sub cmd_AssFC_Click()
DoCmd.SetWarnings False
If IsNull(Me.OutServDate.Value) Or Me.InInventory.Value = True Then

Dim mydb As Database, rs As Recordset, tqn As String, qdf As
QueryDef, strSQL As String
tqn = "q_AssignedFuelCards_XRef"
Set mydb = CurrentDb
Set qdf = mydb.QueryDefs(tqn)
strSQL = qdf.SQL


'ASSIGN FUEL CARDS

Me.Refresh
v_formname = Me.Name
On Error GoTo 0
DoCmd.OpenForm "f_SearchPanel"
On Error Resume Next
DoCmd.OpenForm v_formname
Forms!f_SearchPanel.GlobalVIN = Me.VIN.Value
FCNo = AvailableFuelCards.Column(0)
FCProvi = AvailableFuelCards.Column(1)
TodayDate = Now()

strSQL = Replace(strSQL,
"[Forms]![f_SearchPanel]![GlobalFCProvi]", FCProvi)
strSQL = Replace(strSQL, "[Forms]![f_SearchPanel]![GlobalVIN]",
Me.VIN.Value)

Forms!f_SearchPanel.GlobalFCNo = FCNo
Forms!f_SearchPanel.GlobalFCProvi = FCProvi
Forms!f_SearchPanel.GlobalTodayDate = TodayDate

Set rs = mydb.OpenRecordset(strSQL)
rs.MoveFirst

If rs.EOF Or rs.BOF Then
If FCNo <> "" And VIN <> "" Then
DoCmd.OpenQuery "q_update_assign_FCvi"
DoCmd.OpenQuery "q_append_assign_FCHvi"
End If

Me.Refresh
Else

MsgBox "That vehicle alreadys has one of that type fuel card
assigned to it.", vbCritical, "Fuel Card Type Duplicated"
Exit Sub
End If


Else
MsgBox "Vehicle cannot accept fuel card; it has been taken out of
service.", vbCritical, "Out of Service"
End If
Me.Refresh

End Sub

 

[Dirk Goldgar]

Hello,
MS ACCESS 2003 on XP Pro.

I have code using the rs.eof to check if a query is empty. But it's not
returning a value. Mousing over rs.eof in the code gives:
<object variable or with block variable not set>

This apparently functions as TRUE if when it shouldn't.



Here is code:

Private Sub cmd_AssFC_Click()
DoCmd.SetWarnings False
If IsNull(Me.OutServDate.Value) Or Me.InInventory.Value = True Then

Dim mydb As Database, rs As Recordset, tqn As String, qdf As
QueryDef, strSQL As String
tqn = "q_AssignedFuelCards_XRef"
Set mydb = CurrentDb
Set qdf = mydb.QueryDefs(tqn)
strSQL = qdf.SQL


'ASSIGN FUEL CARDS

Me.Refresh
v_formname = Me.Name
On Error GoTo 0
DoCmd.OpenForm "f_SearchPanel"
On Error Resume Next
DoCmd.OpenForm v_formname
Forms!f_SearchPanel.GlobalVIN = Me.VIN.Value
FCNo = AvailableFuelCards.Column(0)
FCProvi = AvailableFuelCards.Column(1)
TodayDate = Now()

strSQL = Replace(strSQL,
"[Forms]![f_SearchPanel]![GlobalFCProvi]", FCProvi)
strSQL = Replace(strSQL, "[Forms]![f_SearchPanel]![GlobalVIN]",
Me.VIN.Value)

Forms!f_SearchPanel.GlobalFCNo = FCNo
Forms!f_SearchPanel.GlobalFCProvi = FCProvi
Forms!f_SearchPanel.GlobalTodayDate = TodayDate

Set rs = mydb.OpenRecordset(strSQL)
rs.MoveFirst

If rs.EOF Or rs.BOF Then
If FCNo <> "" And VIN <> "" Then
DoCmd.OpenQuery "q_update_assign_FCvi"
DoCmd.OpenQuery "q_append_assign_FCHvi"
End If

Me.Refresh
Else

MsgBox "That vehicle alreadys has one of that type fuel card
assigned to it.", vbCritical, "Fuel Card Type Duplicated"
Exit Sub
End If


Else
MsgBox "Vehicle cannot accept fuel card; it has been taken out of
service.", vbCritical, "Out of Service"
End If
Me.Refresh

End Sub

You have "On Error Resume Next" in effect, so you won't know whether an
error occurred when you tried to open your recordset. I'm guessing that one
did. You need to reestablish some sort of error-handling, even if it's just
in-line checking for errors:

Set rs = mydb.OpenRecordset(strSQL)
If Err.Number <> 0 Then
Msgbox "Whoops!"
Exit Sub
End If

(That's not what I'd recommend, but it's a simple example.)

Note, by the way, that this code:

Set rs = mydb.OpenRecordset(strSQL)
rs.MoveFirst

If rs.EOF Or rs.BOF Then

will raise an error if the recordset is empty, even if you do successfully
open the recordset. You can't .MoveFirst if the recordset is empty. There's
no need for the .MoveFirst, anyway, since you just opened the recordset and
it will already be positioned at the first record if there is one.