Object variable or With block variable not set

[bluethunder]

Good day,

I have a program that the user wants the record be filtered by cut-off date. After I pressed the command button cmdloaddata. I encountered an error message of "Object variable or With block variable not set". Can anyone help me to fix this stuff. Thank you...

Private Sub Form_Load()
Dim rSConn As ADODB.Connection

Set rSConn = New ADODB.Connection
rSConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Payroll System\Payroll\DBPayroll.accdb;Persist Security Info=False"
rSConn.Open
End Sub

Private Sub cmdLoadData_Click()
Dim rs As New ADODB.Recordset

If rS.State = adStateOpen Then
    rS.Close
End If
rS.CursorLocation = adUseClient
rS.Open "Select * from pr_dtl where cut_off_date_frm ='" & dtFrom & "' and cut_off_date_to='" & dtTo & "'", rSConn, adOpenDynamic, adLockOptimistic, adCmdText
rS.Filter = "cut_off_date_frm ='" & dtFrom & "'" And "cut_off_date_to='" & dtTo & "'"
If rS.RecordCount > 0 Then
'display the filtered results in the grid
    Call DataLoad
End If
End Sub

[Stewart Ross]

The recordset variable rs you declare in your on click routine is not set to any value, as you have no rs.Open statement in your routine - hence the error message. With ADO recordsets you need to open a recordset associated with a particular connection, but this step is simply missed out in your case.

Although you have a connection opened on form load there is no relationship between local variable rs in the on-click routine and local variable rsConn in your form's On Load event. As you have defined it locally the connection variable rsConn is only in scope for the duration of the Form Load event, so it cannot be accessed from your On Click event at present.

The simplest way to remedy this is to make rsConn a global variable declared at the top of your form's code module, in which case it will be accessible to all functions and subs within the module. If you want to populate your recordset variable from data available from that connection you will need to specify the SQL for the query concerned, or provide the name of the table or the like that you are opening from that connection.

-Stewart

[bluethunder]

Private Sub Form_Load()
On Error GoTo LocalError
Set rSConn = New ADODB.Connection
rSConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Payroll System\Payroll\DBPayroll.accdb;Persist Security Info=False"
rSConn.Open
Call AllData

dtFrom = Format(Now, Date)
dtTo = Format(Now, Date)

LogUser = frmLogin.sTrLogUser
Call CheckAccess

Exit Sub
LocalError:
    MsgBox Err.Number & " - " & Err.Description
End Sub

Private Sub OpenRecordSet()
Set rS = New ADODB.Recordset
rS.CursorLocation = adUseClient
rS.Open rSSQL, rSConn, adOpenStatic, , adCmdText

End Sub

Private Sub AllData()
rSSQL = "select * from pr_dtl"
Call OpenRecordSet
Call DataLoad

End Sub

Private Sub cmdLoadData_Click()
On Error GoTo LocalError
Dim rs As New ADODB.Recordset

If rS.State = adStateOpen Then
    rS.Close
End If
rSSQL = "select * from pr_dtl where cut_off_date_frm = ? and cut_off_date_to = ?"
rS.Filter = "cut_off_date_frm ='" & dtFrom.Value & "'" And "cut_off_date_to='" & dtTo.Value & "'"
If rS.RecordCount > 0 Then
'display the filtered results in the grid
    Call AllData
    Call DataLoad
End If

Exit Sub
LocalError:
     MsgBox Err.Number & " - " & Err.Description
End Sub

Thank you for the reply, I eliminated the error message "Object variable or With block variable not set", but I encountered a message error of "type mismatch" after pressing the cmdLoadData. I already check the declaration of the fields that I'm going to filter of but still the error occur.

[Stewart Ross]

Youe SQL statement in line 8 has a WHERE clause with invald syntax. The WHERE also conflicts with line 9, application of a date filter. I would remove the WHERE component of your SQL string in line 8.

I also suspect that you will have to replace the single quotes you are using to delimit your date values in line 9 with pound signs (#).

-Stewart