Assigning a table name to a variable

[dave b via AccessMonster.com]

I have a form where I collect varibles to run a report. One of the variables
is a list of product codes; they are stored in a table that is created when
the user opens the form. At first, the name of the table of product codes
was constant. I decided to create the table with a name using a timestamp
(tblProduct<hhmmss>), so that everytime the user opens the report form, they
are going to be using a new table. The table is deleted when the user exits
the table.

The report uses a simple query. At first I used a filter that was built
based on the variables selected. The code to build the filter is in a
separate module that is assigned when the report is opened. Since then, I've
read some of the threads on this site and realized that the where condition
would be a better choice. And I am also thinking of building the
filter/where in the form. What I need is to be able to build the filter or
where using a table name that will be a variable, and I'm assuming part of
the condition will be something like:

qryFillingReport!ProductID = tblProduct102536!ProductID

but where tblProduct102536 would be replaced by a varible that has the name
of the product code table assigned to it. Sample code would be helpful.

A second problem I'm having is that while testing, I used a hardcoded product
code and assigned it to the where condition of the DoCmd.OpenReport in the
form:

strReportWhere = "queries!qryFillingDetailReport!ProductID = 'AMP'"
DoCmd.OpenReport "rptFillingDetail", acViewPreview, , strReportWhere

What I'm getting is all records, when I expect to get just the record with
product codes of AMP.

 

[Jeff Boyce]

Dave

Another approach might be to skip creating a temp table and just use a query
to get your list of product codes...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[dave b via AccessMonster.com]

I'm populating the product code table using a drop down list. How would
that work using a query?

Dave

Another approach might be to skip creating a temp table and just use a query
to get your list of product codes...

I have a form where I collect varibles to run a report. One of the variables
is a list of product codes; they are stored in a table that is created when

[quoted text clipped - 27 lines]

What I'm getting is all records, when I expect to get just the record with
product codes of AMP.

 

[Jeff Boyce]

Dave

I guess I don't have the "big picture"...

Among my questions:
? why change the name of the table each time, when you'll just delete it
later?
? are you saying you use the form to select multiple product codes to
include (or select by) in your query/report?
? why use a temp table when you could use a permanent table and simply
empty it out before starting to fill it?
? have you inserted a breakpoint in the construction of your SQL/WHERE
clause and inspected what is actually being used when you try the ='AMP'?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

I'm populating the product code table using a drop down list. How would
that work using a query?

Dave

Another approach might be to skip creating a temp table and just use a query
to get your list of product codes...

I have a form where I collect varibles to run a report. One of the variables
is a list of product codes; they are stored in a table that is created

when
[quoted text clipped - 27 lines]

What I'm getting is all records, when I expect to get just the record with
product codes of AMP.

 

[dave b via AccessMonster.com]

The table holds the values selected by the user to run the report.
Initially, I did use a permanent table, and empty it. However, should
multiple users run the same report at the same time, problems would arise by
using the same table. I decided on creating a table each time the report is
run, with a primary key to ensure no dups. Then deleting it.

Also, the table may or may not be used. The user has the option of selecting
a list (use table), a range, or all (don't use table for latter two).

I did use breakpoints, but still could not determine why the where argument
was not being used. I used a variable to create the where; then out of
frustration, I hardcoded the value in the DoCmd.

At first, I had the report call a module, which created a filter. It worked
fine. But then, I was reading threads on this site, and everybody was saying
it was better to use the where.

My solution to the problem was to create the recordsource value in the Open
event of the report. There I could start with the main query, then add on
JOINs and WHEREs as needed, depending on the parameters selected by the user
(taken from the form). Complicated, but it seems to work.

If there is a simple and elegant solution, I wouldn't mind hearing it. But I
don't using ugly to get the job done.

Dave

I guess I don't have the "big picture"...

Among my questions:
? why change the name of the table each time, when you'll just delete it
later?
? are you saying you use the form to select multiple product codes to
include (or select by) in your query/report?
? why use a temp table when you could use a permanent table and simply
empty it out before starting to fill it?
? have you inserted a breakpoint in the construction of your SQL/WHERE
clause and inspected what is actually being used when you try the ='AMP'?

I'm populating the product code table using a drop down list. How would
that work using a query?

[quoted text clipped - 9 lines]

 

[Jeff Boyce]

I'm not there, and this seems more complex than I can grasp via this
mechanism. You may want to see if there are any folks local to you who
could take a look, or you may decide to hire some consulting time to review
and recommend.

I would point out that the solution to your "potentially multiple users
ordering the report simultaneously" may be simply to add a userID field to
the permanent table. Having the same table name each time will obviate at
least one of your issues.

--
Best of luck on your project

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

The table holds the values selected by the user to run the report.
Initially, I did use a permanent table, and empty it. However, should
multiple users run the same report at the same time, problems would arise by
using the same table. I decided on creating a table each time the report is
run, with a primary key to ensure no dups. Then deleting it.

Also, the table may or may not be used. The user has the option of selecting
a list (use table), a range, or all (don't use table for latter two).

I did use breakpoints, but still could not determine why the where argument
was not being used. I used a variable to create the where; then out of
frustration, I hardcoded the value in the DoCmd.

At first, I had the report call a module, which created a filter. It worked
fine. But then, I was reading threads on this site, and everybody was saying
it was better to use the where.

My solution to the problem was to create the recordsource value in the Open
event of the report. There I could start with the main query, then add on
JOINs and WHEREs as needed, depending on the parameters selected by the user
(taken from the form). Complicated, but it seems to work.

If there is a simple and elegant solution, I wouldn't mind hearing it. But I
don't using ugly to get the job done.

Dave

I guess I don't have the "big picture"...

Among my questions:
? why change the name of the table each time, when you'll just delete it
later?
? are you saying you use the form to select multiple product codes to
include (or select by) in your query/report?
? why use a temp table when you could use a permanent table and simply
empty it out before starting to fill it?
? have you inserted a breakpoint in the construction of your SQL/WHERE
clause and inspected what is actually being used when you try the ='AMP'?

I'm populating the product code table using a drop down list. How would
that work using a query?

[quoted text clipped - 9 lines]

What I'm getting is all records, when I expect to get just the record with
product codes of AMP.

 

[dave b via AccessMonster.com]

Thanks for the suggestion; however, I don't need to hire anybody to do my
work.

As for the table, as I said, I figured out a solution. Of which, I'm sure,
there is a multitude.

I'm not there, and this seems more complex than I can grasp via this
mechanism. You may want to see if there are any folks local to you who
could take a look, or you may decide to hire some consulting time to review
and recommend.

I would point out that the solution to your "potentially multiple users
ordering the report simultaneously" may be simply to add a userID field to
the permanent table. Having the same table name each time will obviate at
least one of your issues.

The table holds the values selected by the user to run the report.
Initially, I did use a permanent table, and empty it. However, should

[quoted text clipped - 40 lines]