Recordset as recordsource

[Guest]

Hi,

I am puzzled about using recordset as the source for forms and report. I
created a form and report for general input and printing out all records. I
then created a form for User Interface for enquiry. I would like to use the
same form/report format for viewing/printing.

I use Openrecordset method to select the recordset. When I read the
postings, most of people suggest to declare the recordset in a public
statement, then using the Open event method to put the selected recordset as
recordsource.

If that is the case, should I create more than one version of form/report?
One for general input and the other for enquiry. It seem to me it is quite
redundant. There must be a better method that I can use the select recordset
to display in a form/report directly.

Can I define the recordsource of a form and report in a public statement?
Please help and thank you very much for your advice.

BL

 

[Tim Ferguson]

I use Openrecordset method to select the recordset. When I read the
postings, most of people suggest to declare the recordset in a public
statement, then using the Open event method to put the selected
recordset as recordsource.

This seems to be not quite the way to go about things.

A form or a report is based on a recordsource, not a recordset. A
recorsource is a table, a querydef, or (probably most often) a SQL SELECT
statement. You can of course generate a recordset from any of these
things too; but it's not the recordset you need for the form.

You can put code in the Open or Load event of the form or report to look
for its OpenArgs argument, and decide if it's going to open on its
default recordsource (whatever you put in at design time), or get itself
a new one. Like this:

Private Sub Form_Open (Cancel As Integer)

dim strSQL as string

if isnull(me.openargs) then
' use default; do nothing

else
' obviously you need to error trap this!
strSQL = "SELECT * FROM MyTable " & _
"WHERE MyID = " & me.OpenArgs

me.recordsource = strSQL
me.Requery

End if

End Sub

There is obviously an extremely limited variety of recordsource you can
use for a given form, because the fields have to synchronise with the
controlsources and so on. I use this method to open each form on a single
record, to minimise network bandwidth, to avoid record locking and most
of all to prevent the user accessing more than one record at a time.

Hope the helps


Tim F

 

[Guest]

Thanks, Tim, for your useful advice. However, I have an idea to clarify with
you. The StrSQL below will create a recordset which is used as recordsource
in the form/report. It seems that everything is at Form/Report Level and I
need to do all the programming at Form/Report Level.

Can I create different recordsets using Public Statement and use the same
Form/Report to view/print them out?

Thank you.

Regards,

BL

 

[Tim Ferguson]

However, I have an idea to
clarify with you. The StrSQL below will create a recordset which is
used as recordsource in the form/ report. It seems that everything is
at Form/ Report Level and I need to do all the programming at
Form/ Report Level.

No: the strSQL _is_ the recordsource. The form manages its own recordset
all by itself without any further hindrance from the user, although you
can get at it if you need via the .Recordset and .Recordsource
properties. Think of it as:

Recordsource = description of what data to get from where
Recordset = set of records

In Access, nearly everything happens as a result of a user interacting
with a form -- whether that is a typical recordsource-based form or a
custom dialog. Other starting points are menu commands, and the Autoexec
macro. Most of the code you write will be connected to Form Events of one
type or another. In my experience, I don't get to use much coding in
Reports, but that may be because my interest is in real-time systems
where the complexity is the UI, and reporting is pretty straightforward.
Other people's MMV.

When I do use General Modules, it's for re-usable code like GetPatientID
or RelinkToBackEndDatabase and so on.

Pure process stuff I tend to put into a VBS script and just call it
without using Access at all -- except to create and debug it, that is!

Can I create different recordsets using Public Statement and use the
same Form/Report to view/print them out?

Don't really understand this. If you are going to print stuff, use a
Report; if you want to do selections, then use a custom dialog referenced
by a parameterised query (or querydef); and to do a projection I go for a
quick-and-dirty OpenQuery and let the user look after him- or herself.


Hope that helps


Tim F