Need help initializing object

[OD]

Hi
I've been stuck on this for a week now. Someone was nice enough to point me
in the right direction but I can't figure it out. In this code my recordset
keeps returning nothing from the class object even though there is
information in the object.

Private Sub Form_Load()
On Error GoTo HandleError


Set objPurchase = New clsPurchase
objPurchase.ClearObject

Set rsPurchase = New ADODB.Recordset

blnAllRecords = True
Call LoadRecords
Exit 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


Private Sub Class_Initialize()
PRNum = vbNullString
PRID = 0
PONum = vbNullString
BFID = vbNullString
Requestor = vbNullString
Ext = vbNullString
Bldg_Floor = vbNullString
ReqDate = "1/1/1900"
Client_Name = vbNullString
Business_Line = vbNullString
RCNumber = vbNullString
Approval1 = vbNullString
Approval2 = vbNullString

End Sub


Sub ClearObject()
On Error GoTo HandleError

Me.PRNum = " "
Me.ReqDate = Now()
Me.Requestor = " "
Me.Ext = " "
Me.RCNumber = " "
Me.Client_Name = " "
Me.Business_Line = " "
Me.Approval1 = " "
Me.Approval2 = " "

Exit Sub

Let me know if you need or wish to see more. Any help is greatly
appreciated. Thank you

 

[Stuart McCall]

Hi
I've been stuck on this for a week now. Someone was nice enough to point
me
in the right direction but I can't figure it out. In this code my
recordset
keeps returning nothing from the class object even though there is
information in the object.

Private Sub Form_Load()
On Error GoTo HandleError


Set objPurchase = New clsPurchase
objPurchase.ClearObject

Set rsPurchase = New ADODB.Recordset

blnAllRecords = True
Call LoadRecords
Exit 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


Private Sub Class_Initialize()
PRNum = vbNullString
PRID = 0
PONum = vbNullString
BFID = vbNullString
Requestor = vbNullString
Ext = vbNullString
Bldg_Floor = vbNullString
ReqDate = "1/1/1900"
Client_Name = vbNullString
Business_Line = vbNullString
RCNumber = vbNullString
Approval1 = vbNullString
Approval2 = vbNullString

End Sub


Sub ClearObject()
On Error GoTo HandleError

Me.PRNum = " "
Me.ReqDate = Now()
Me.Requestor = " "
Me.Ext = " "
Me.RCNumber = " "
Me.Client_Name = " "
Me.Business_Line = " "
Me.Approval1 = " "
Me.Approval2 = " "

Exit Sub

Let me know if you need or wish to see more. Any help is greatly
appreciated. Thank you

Sub Form_Load(), Sub LoadRecords() and Sub ClearObject() all are missing an
'End Sub'

'Exit Sub' is not the same thing. You use that _inside_ the procedure to
exit prematurely (for whatever reason), ie it must appear between Sub...End
Sub pairs.

I'm surprised Access compiled this module. In the VBA editor, select
Debug -> Compile <ProjectName>.

 

[OD]

Hi

Thanks for replying. The end sub is there I didn't feel it was necessary to
copy that in with the amount of data that I was already putting there. That
is not what is causing the object not to assign back to the table. It is
bombing out on the line

"If rsPurchase.BOF And rsPurchase.EOF Then"

and not even getting to the end of the procedure. The rsPurchase is showing
as "Nothing" in the watch window. I can't figure why it is working all the
way but wont transfer the information back to the recordset

Thank you though

Any other thoughts?
Thanks

 

[Stuart McCall]

Hi

Thanks for replying. The end sub is there I didn't feel it was necessary
to
copy that in with the amount of data that I was already putting there.
That
is not what is causing the object not to assign back to the table. It is
bombing out on the line

"If rsPurchase.BOF And rsPurchase.EOF Then"

and not even getting to the end of the procedure. The rsPurchase is
showing
as "Nothing" in the watch window. I can't figure why it is working all the
way but wont transfer the information back to the recordset

<snip>

I don't see the declaration for blnAllRecords anywhere. It should be
declared at the module level in order to be visible to both the Form_Load
and LoadRecords procedures (or you could Dim it in Form_Load and pass it as
a parameter to LoadRecords).

If the method RetrievePurchase is working ok, I can't see anything else
amiss, although I never use ADO myself. Are you sure that rsPurchase.BOF And
rsPurchase.EOF can both be True?

 

[OD]

Hi

good catch. It is blnAllRecords is defined in the global area of the
database. From looking at the trace, yes, they both can be true at the same
time but this is because the recordset is not receiveing the information back
from the objPurchase.RetrievePurchase. This is where I'm hung up. The
information is populating correctly until that point and then nothing returns
back to rsPurchase.

 

[Stuart McCall]

Hi

good catch. It is blnAllRecords is defined in the global area of the
database. From looking at the trace, yes, they both can be true at the
same
time but this is because the recordset is not receiveing the information
back
from the objPurchase.RetrievePurchase. This is where I'm hung up. The
information is populating correctly until that point and then nothing
returns
back to rsPurchase.

<snip>

If the code isn't too humongous, could you post the RetrievePurchase
procedure from your clsPurchase class?

 

[OD]

Thanks
Here is the Retrieve function

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 PRNumber"
End If

BuildSQLSelectPurchase = strSQLretrieve
Exit Function


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

End Function

and the process recordset 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


Thank you!

 

[OD]

Hi

Thanks it is up there now

Post the code for RetrievePurchase - though it appears to be working, I would
think your problem has to be related to that function.

 

[OD]

Thank you!!

I'll give that a shot
Thans again

In the RetrievePurchase function you have:

Set rsPurch = ProcessRecordset(strSQLStatement)

you need:

Set RetrievePurchase = ProcessRecordset(strSQLStatement)

Thanks
Here is the Retrieve function

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 PRNumber"
End If

BuildSQLSelectPurchase = strSQLretrieve
Exit Function


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

End Function

and the process recordset 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

Thank you!

Hi

[quoted text clipped - 11 lines]

If the code isn't too humongous, could you post the RetrievePurchase
procedure from your clsPurchase class?