ADODB Question

[OD]

Hi
I hope I got all the necessary procedures. I am getting the following error
when opening the database. Everything looks good until almost the end and for
some reason it looks like the db is opening up in add mode (the table is
coming back as empty and there is information in it),

An error has occurred in the application
Error Number 91
Error Description Object variable or With block variable not set
Module Source frmPurchaseEntryScreen3
procedure Source LoadRecords

Here are the processes leading to this

Private Sub Form_Load()
On Error GoTo HandleError


Set objPurchase = New clsPurchase
Set rsPurchase = New ADODB.Recordset

blnAllRecords = True
Call LoadRecords
Exit Sub

HandleError:
GeneralErrorHandler Err.Number, Err.Description, PURCHASE_FORM,
"Form_Load"
Exit Sub
End Sub

Sub LoadRecords()
On Error GoTo HandleError

intCurPurchRecord = 0
blnAddMode = False
Set rsPurchase = objPurchase.RetrievePurchase(blnAllRecords)
If rsPurchase.BOF And rsPurchase.EOF Then
MsgBox "There are no records in the database"
Exit Sub
Else
objPurchase.PopulatePropertiesFromRecordset rsPurchase
Call MoveToFirstRecord(intCurPurchRecord, rsPurchase, objPurchase,
blnAddMode)
Call PopulatePurchaseControls
End If

Exit Sub

HandleError:
GeneralErrorHandler Err.Number, Err.Description, PURCHASE_FORM,
"LoadRecords"
Exit Sub
End Sub

Function RetrievePurchase(blnAllRecords As Boolean) As ADODB.Recordset
On Error GoTo HandleError

Dim strSQLStatement As String
Dim rsPurch As New ADODB.Recordset

strSQLStatement = BuildSQLSelectPurchase(blnAllRecords)

Set rsPurch = ProcessRecordset(strSQLStatement)

Exit Function

HandleError:
GeneralErrorHandler Err.Number, Err.Description, CLS_PURCHASE,
"RetrievePurchase"
Exit Function
End Function

Function BuildSQLSelectPurchase(blnAllRecords As Boolean) As String
On Error GoTo HandleError

Dim strSQLretrieve As String
If intPurchLookup = 0 Then
strSQLretrieve = "SELECT * FROM tblPurchReq ORDER BY PRNum"
End If

BuildSQLSelectPurchase = strSQLretrieve
Exit Function


HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC,
"BuildSQLRetrievePurchase"
Exit Function

End Function


Function ProcessRecordset(strSQLStatement As String) As ADODB.Recordset
On Error GoTo HandleError

Call OpenDBConnection
Dim rsPurch As New ADODB.Recordset

With rsPurch
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open strSQLStatement, cnConn
.ActiveConnection = Nothing
End With

Call CloseDBConnection
Set ProcessRecordset = rsPurch

Exit Function


HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC,
"ProcessRecordset"
Exit Function
End Function

 

[vanderghast]

Your object objPurchase is probably not initialized properly. It is not
Nothing, since you use


Set objPurchase = New clsPurchase


but unless you define an initilizer in your class Purchase,

Private Sub Class_Initialize()
...
End Sub


all its local variables (attributes) will be set to default, 0 for
numerical, empty string for strings variables, and its RetrievePurchase
method won't be able to do much, at the line:

Set rsPurchase = objPurchase.RetrievePurchase(blnAllRecords)


That is a very weak point of VBA, to not be able to Initialize the object
with arguments, so you would probably need some extra method to do it:

Set objPurchase = New clsPurchase
objPurchase.InitializeConnection( .... parameters here ... ) '
NEW LINE OF CODE


Note that proceeding this way is very prone to lead to memory leak, with
VBA, though, if your initialization create/activate other 'objects' .



Vanderghast, Access MVP