Accessing a Varibly Named Table value in a Module called from a Form

[dave b via AccessMonster.com]

I have a form which collects parameters to run a report. One of the
parameters collects a list that populates a table. I wanted to make the list
table unique, so I create it when the user enters the form and delete it when
the user exits the form. I name the table "tblProductSelectFilling" & Hour
(Time) & Minute(Time) & Second(Time).

When the report is run a filter is built using the selected parameters. The
report works fine if the list table is using a stagnant name; however, I want
the query to have access to the timestamped list table whenever the user runs
the report.

I can pass the name of the table to the module that creates the filter, but
how do I use that varible to reference the value in the table?

 

[Douglas J Steele]

It would probably be a little easier to name the table
"tblProductSelectFilling" & Format(Time(), "hhnnss")

That having been said, you can change the RowSource for your form to use the
table name you pass to it.

 

[dave b via AccessMonster.com]

I've decided to create the filter in the form, rather than a separate module.
After reading the threads, it's apparent that it's better to use the Where
condition in DoCmd.OpenReport, rather than the Filter.

The problem I'm having now is that even hardcoding the where, the report
doesn't filter for the desired results.
strReportWhere = "qryFillingDetailReport!ProductID = 'AMP'"
DoCmd.OpenReport "rptFillingDetail", acViewPreview, , strReportWhere

All product codes are reported.

And, yes, there are records where ProductID = 'AMP'.

Eventually, I want to replace the hardcoded AMP with the table and ProductID
the user selected.

Any help would be appreciated. Thanks.

It would probably be a little easier to name the table
"tblProductSelectFilling" & Format(Time(), "hhnnss")

That having been said, you can change the RowSource for your form to use the
table name you pass to it.

I have a form which collects parameters to run a report. One of the
parameters collects a list that populates a table. I wanted to make the list

[quoted text clipped - 9 lines]

I can pass the name of the table to the module that creates the filter, but
how do I use that varible to reference the value in the table?

 

[Douglas J. Steele]

You don't need to qualify the field name with qryFillingDetailReport, since
the report's recordsource cannot have the same field name twice. Don't know
whether that's the cause of the error, though.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've decided to create the filter in the form, rather than a separate
module.
After reading the threads, it's apparent that it's better to use the Where
condition in DoCmd.OpenReport, rather than the Filter.

The problem I'm having now is that even hardcoding the where, the report
doesn't filter for the desired results.
strReportWhere = "qryFillingDetailReport!ProductID = 'AMP'"
DoCmd.OpenReport "rptFillingDetail", acViewPreview, , strReportWhere

All product codes are reported.

And, yes, there are records where ProductID = 'AMP'.

Eventually, I want to replace the hardcoded AMP with the table and
ProductID
the user selected.

Any help would be appreciated. Thanks.

It would probably be a little easier to name the table
"tblProductSelectFilling" & Format(Time(), "hhnnss")

That having been said, you can change the RowSource for your form to use
the
table name you pass to it.

I have a form which collects parameters to run a report. One of the
parameters collects a list that populates a table. I wanted to make the
list

[quoted text clipped - 9 lines]

I can pass the name of the table to the module that creates the filter,
but
how do I use that varible to reference the value in the table?