Report - run time error 91

[Cowper]

Hi,

I want to create a report that will list out a supplier's information based on a supplier's ID. In other words, the report's Recordsource should change according to supplier ID (I think it should be a SQL thing). However, everytime I ran the codes (listsed below), the error message popped up. It says run-tim error '91' Object variable or with block variable not set. I looked Help, no good!

I have two questions:

1. How can I fix the run-time error?
2. Is it the right way to set a report's Recordsouce under Report_Activate()? Is there a better way to do it?

Thanks

Cowper


Here is my code:


Private Sub Report_Activate()
Dim cnn As ADODB.Connection
Dim rsSupplier As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "Select * from tblSupplier where Supplier_ID = '" & Me.txtSupplierID & "';"

rsSupplier.Open strSQL, cnn, adOpenStatic, adLockOptimistic

Me.Report.RecordSource = strSQL


rsSupplier.Close
cnn.Close
End Sub

 

[cmmrfrds]

Put it in the onopen event. this is all you need.

Dim strSQL As String
strSQL = "Select * from tblSupplier where Supplier_ID = '" & Me.txtSupplierID & "';"
Me.Report.RecordSource = strSQL

If supplier_ID is a numeric field, do NOT put quotes around it. If it is a text field then quotes are necessary.

 

[danvlas]

As cmmrfrdrs said, you can change/set the report recordsource only in the Open event. Although it should work as you made it, you could clean the code a little:

Private Sub Report_Open(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rsSupplier As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "Select * from tblSupplier where Supplier_ID = '" & Me("txtSupplierID&quot & "';"

rsSupplier.Open strSQL, cnn, adOpenStatic, adLockOptimistic

RecordSource = strSQL

rsSupplier.Close
Set rsSupplier = Nothing
cnn.Close
Set cnn = Nothing
End Sub

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[cmmrfrds]

Me.Report.RecordSource = strSQL
should be
Me.RecordSource = strSQL
or
RecordSource = strSQL

 

[Cowper]

Hi,

Thanks for that. However, I still cannot make it work. I tried what you suggested to put the code in the report's Open event. Everytime when I tried to open the report, it will show up a message saying "You entered an experssion that has no value." I think the problem is that I created a text box called txtSupplierID, which carries the Supplier's ID number from another form. How can I carry the Supplier ID from a form (Purchase Order Form) to this report?

Also, could you explain to me why I don't need to create a recordset to run the SQL in this case?

Thanks

Cowper

 

[danvlas]

ooooooops...quite right...guess it's too early in the morning and I haven't had my coffee yet

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String
strSQL = "Select * from tblSupplier where Supplier_ID = '" & Forms("Purchase Order Form&quot("txtSupplierID&quot & "';"
RecordSource = strSQL
End Sub

If SupplierID is numeric:
strSQL = "Select * from tblSupplier where Supplier_ID = " & Forms("Purchase Order Form&quot("txtSupplierID&quot & ";"

You don't need a recordset because the RecordSource of the report is building it by executing the strSQL...

Get rid of spaces in object names...It will save you a lot of trouble in the future.

Alternative: Place a command button on "Purchase Order Form"

Sub CommandButton_Click()
DoCmd.OpenReport ("YourReportName&quot, acViewPreview, , "Supplier_ID = '" & Me("txtSupplierID&quot & "';"
End Sub


If SupplierID is numeric:
DoCmd.OpenReport ("YourReportName&quot, acViewPreview, , "Supplier_ID = " & Me("txtSupplierID&quot

HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com