Unbound controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm making a report with several different queries and each query needs to
populate data in several different controls after being analysed (which may
change frequently). Perhaps I need to do several SubReports. My problem is
though that I can't from VBA populate data into an unbound control. I can
call a function from the control and get the data back but I can't do it
without a call. In forms there's no problem using the RecordsetClone, analys
it and send the data to an unbound control but I can't do it in a Report. I
also have a problem using the recordset as a variable from the Main Function
to the analys function. Also something that's not a problem with the
RecordsetClone in a form. Can I have an unbound Report and keep all queries
as SQL statements in VBA?

I don't want to call the functions from the controls since that means I have
to change every control if something changes instead of just changing one
variable in the VBA code.
 
You don't give much info about the nature of the data you want to return to
populate the controls.

There are a number of ways you can accomplish this, which you use will
depend on your exact needs.

The most obvious is to create sub reports based on your queries.

If that will not work, then you may consider usinging DLookup or other
Domain Aggragate functions to populate your controls. This would be on the
slow side. The impact would depend on the number of controls and the number
of records in the reports record source.

If the data for group of controls could be retrieved with a query, it would
be faster than a bunch of DLookups, but take more coding. This is where you
would get into your functions. You would have to estabish the query as a
recordset and populate the controls in your function. You would probably
want to call that function in the Format event of the section the controls
are in.

Creating queries in VBA in not very efficient. Stored queries are already
optimized by Jet. If you use a query created in code, Jet need the time to
optimize the query. Queries should only be created in VBA when it is not
possible to use a stored query and pass it parameters.

I disagree with your statement regarding making changes. A function would,
if correctly constructed, have the advantage. If you plan it well, the only
change required would be a modification to the query, not the controls.
 
Thanks for your quick response.
I'm making queries that are very stable they won't change. What I'm doing
with them are easy counts so the code is not tough. I'm dividing the rows of
the query into fe 4 sections (this is the thing that can change, up to I
don't now how many sections and every section shall populate one control) and
then search through each section for records that match my criteria (won't
change either) and devide it by the total number of rows from that section. I
will have a few queries doing the same thing but from different tables. So
you see the function itself is not very complicated. You are right that I
don't want the SQL statement in the code that's why I used the RecordsetClone
in the forms. I would like to use the recordset for the report so I don't
have to requery every time. I would however like the VBA do the calculations
without parameters from the controls. Then the code will get a bunch of calls
instead of just one, but that might not be a problem.
 
To be more specific. How do I refer to the Record source in Reports since the
RecordClone doesn't exist and how to populate the controls since you have to
put the control in focus and you can't do that in Reports.

Have I understood this correctly?? I'm so not an access person:)
 
Back
Top