Query Looking At Form's Field - but from VBA

[(PeteCresswell)]

Got a report.

Report's based on a query.

Query looks at a dialog form to get a date.

Date is used by the query in selecting records.


Query works fine when it's just invoked by double clicking.

But when it's the .RecordSource of the report, the report dies - saying it does
not recognize the [Form].[FormName]![txtDateFieldname] referenced by the query.

I've been here before and always gotten around it by having the report's
..RecordSource set to Null and then using the query to create a work table in
Form_Open - instantiating the query and explicitly setting the parm.


But this query is a Pivot Table presentation - i.e. the fields may not be the
same each time it is invoked.

This breaks my little scheme of creating the work table via a .CopyObject of a
model table into a work DB and seems to reduce me to a MakeTable query...


I guess I could do that - but it would require writing code to manage the link -
and that's a little more complicated because I make my apps
Terminal-Server-Friendly. i.e. the work table's location varies depending on
the user's ID and so-forth so the location of the query's target table would
have tb changed dynamically also.


What I'm hoping to hear is that I'm just doing something dumb back at the query
and fixing it will make it run "as-is".

Anybody?

 

[strive4peace]

Hi Pete,

does your form stay open?

what is not recognized? the control itself or the data type? If data
type, try:

DateValue([Form].[FormName]![txtDateFieldname])
(obviously, this throws out the Time component so make sure you use
DateValue in the field cell as well)

"But this query is a Pivot Table presentation - i.e. the fields may not
be the same each time it is invoked."

your report open event will need to change the recordsource of controls
to match the query ...

get Fieldnames from a Query -->

number of fields in query:

CurrentDb.QueryDefs("Queryname").Fields.Count

first fieldname:

CurrentDb.QueryDefs("Queryname").Fields(0).Name

last fieldname:

CurrentDb.QueryDefs("Queryname").Fields(CurrentDb.QueryDefs("Queryname").Fields.Count-1).Name

indexing starts with 0, so the last field is the Count-1

If your fields have spaces or special characters, you will need to
dlimit them with square brackets

[fieldname with a space or #special (character)]


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*