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()?

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

 

[pgk]

Hi,
Try the following:


1) Create a new query using the Query Designer and base this query on the table, tblSupplier.

2) Select all the fields that you want to appear in the report. Donot use the * option but select each field individually.

3) In the criteria row under the Column SupplierId, enter the following:

Val([Forms]![Form22].[Text0])

Substitute Form22 with the name of your form and text0 with the name of the text box in which you enter your supplier id.

4) Set the RecordSource of your report to this query.


Hope it helps. Let me know what happens.
With regards,
PGK