Opening and populating a premade Report with a DAO recordset.

[chibi_vb]

I can access the fields I need from the SQL query, but I am having trouble
opening up the premade report and populating it.

Note: sSQL is a functioning SQL query

dim db As DAO.Database
dim rSet As DAO.Recordset
set db As = CurrentDB
set rSet = db.OpenRecordset(sSQL, dbOpenSnapShot)

If rSet.RecordCount = 0 Then
MsgBox "There are 0 records", vbExclamation, "No Records"
Else
' What to do here??

End If



I can exact the specific data from the recordset for a specifc field. I
need to be able to set the following:
-text fields
-checkboxes

anybody out there that can help with this problem?

Also, need to know how to a printing/no printing option for the report for
when it opens or close (either or..).

 

[Klatuu]

You do not need to create a recordset when you want to open a report.
You query should be the record source of the report.
For your text boxes and check boxes, you bind them to fields in the report's
record source.

Reports have a No Data event that fires if there are no matching records.
You can present your error message in that event. But, be aware that since
it cancels the report, the procedure where the report is opened will raise an
error 2501, so you need to use error handling to ignore the error at that
point.

As to actuall opening the report, look in VBA Help for the OpenReport
method. It is explained very well there.

 

[chibi_vb]

Problem, the info was originally extracted from another form. Cannot do this
using the basic open report scheme.

The recordset is mandatory.

 

[chibi_vb]

Error is 2451, it cannot find the customized report that exists in the same
Access DB.

 

[Klatuu]

Forms do not contain any data. Only tables contain data. Forms are for
entering, modifying, and manipulating data manually.

I think you have some incorrect notions about how the various objects in
Access work.

 

[Klatuu]

Then either it is not there or you have misspelled the name, just as the
error indicates.

 

[chibi_vb]

Okay then, from the customized query that extracted some user input, how
would I approach binding the fields properly in the premade report (say with
a name of "Customized Report")? Example code would be helpful..

 

[Klatuu]

There is no code required.
Open the report in design view
Open the properties dialog and select the data tab
Click on Record Source and select your query from the drop down.

 

[chibi_vb]

The query is not made with any query builder, the query is made from input
data on another form.

 

[Klatuu]

Is the form that builds the SQL query the same form that opens the report?
Why is it necessary to build the query dynamically?

 

[chibi_vb]

Yes, the input form builds the SQL query, and then tries to relay the query
to a report whether a recordset and report or just to the report.

 

[Klatuu]

The normal way to do this is to use a stored query as the report's record
source.
Then in the form, create a string varialbe to filter the report. It should
look just like a Where clause without the word Where. Then you use the Where
argument of the OpenReport method to do your filtering.

I suggest you do some reading in VBA Help on the OpenReport method.

 

[chibi_vb]

I have a few controls on the input form that make mini-calls to populate a
few comboboxes. How would I go about bypassing the 3211 error where a lock
is required on the associated table so I can open the report properly?

 

[Klatuu]

You can't bypass the error, you need to determine why you are getting the
error and correct the problem.

 

[chibi_vb]

I know what the problem likely is, but how to deal with it? As I mentioned
before, the input form has some comboboxes that are populated by mini-queries
by using the Record Source in each combobox. This causes the interference to
lock on the associated table that I am trying to query.

 

[Klatuu]

Make your combo row sources queries rather than directly on tables.

 

[chibi_vb]

Thanks for the tips.

Make your combo row sources queries rather than directly on tables.