Form --> Query

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

Guest

Hello,

Is it possible to use a form to select which table a Query will utilize.?

I have three different tables: 1)Sales 2)COS 3)Income.

Each table has 3 columns/fields 1)contract 2)amount 3)Date.

I need to run similar reports from each. I would like to run a report from
any of the three reports and then select the date range to run from the
report - using one form. The only way I know how to do this is to create 3
different queries, and then have the user select on the Form which query to
run. Is it possible to have the user select on the form which table to use in
one query?

Thank You
 
You could, but if the field names are the same, why not just base the single
report on a single query and modify the SQL when you click the "Launch
Report" button
 
Thank You for your response.
Could you describe how I can modify the SQL to specify which table to use
when I click the "Launch" command. I am not sure how to go about doing this.
If I need to use VBA code, could you please give an example of the code.
Also, the amount field title is different in each table one for Sales, one
for COS and one for Income.

Thank You,
 
I think your best bet is 3 separate queries / reports if you are unfamilliar
with code and the fields are different.
 
Can you refer me to somewhere that I can look into changing the table name
in an SQL query with VBA code. I am familiar (to some extent, about 4 yr
experience) with using VBA code. I have just never changed SQL code with
variables. I think setting up or declaring the variables to change the table
and field names is giving me the most trouble. Setting up to change the table
name is my biggest problem, I think. I believe I can work around the field
names.

Thank You
 
CurrentDb.QueryDefs("qryTest").SQL=
Replace(CurrentDb.QueryDefs("qryTest").SQL,"tblTest1","tblTest2")
 
Thank You,
This looks like what I need.
How can I determine the name of the first table "tblTest1" --the name of the
table currently used in the query.
This will be the only table in the query.

Do you know of the code to determine the table/tables currently used in the
Query. I think that once this is determined I can declare it as "tblTest1"
and the query will change as you show below.

Thank You,
 
That could get decidedly tricky. I suppose if you're sure that the query
includes only one table you could search the SQL for the text ' FROM ' and
then assume that everything between that text and the next space is the name
of the table - unless you have tables with spaces in their names, when
you'll have to search for the "[" and "]" characters following the ' FROM '
text.

Keep in mind that there's nothing preventing anyone from having a field in a
table with the same name as the table itself, or from including the name as
part of a field name, so even after you've found the name of the table, you
can't go blindly replacing it anywhere it happens to appear in the SQL.

This is a decidedly non-trivial parsing operation you've taken on here. I
can't help wondering why it would ever be necessary - if you don't know what
the name of the table is, how do you know it needs to be changed, let alone
to what it should be changed?
 
I may be going about this in the wrong way.
I have several tables with three fields in them Contract, Amount, Date.
The amounts of each table represent the values of different types of
accounts. I am trying to use a form to allow my users to select which report
they would like to run. A combo box containes each tables name. I only want
to have to use one query. This is why my vba code would need to change the
SQL code within the specified query. Since the user would be running various
reports, the name of the table it is referencing would change each time the
user runs a new report. This is why you would not know the name of the table
that the query is currently using, and would need the vba script to change it
to your desired tablename (the name selected in the forms combobox). The
purpose of this is to essentially create one query which can maneuver to the
users needs rather than creating numerous additional queries for this single
userform.





Brendan Reynolds said:
That could get decidedly tricky. I suppose if you're sure that the query
includes only one table you could search the SQL for the text ' FROM ' and
then assume that everything between that text and the next space is the name
of the table - unless you have tables with spaces in their names, when
you'll have to search for the "[" and "]" characters following the ' FROM '
text.

Keep in mind that there's nothing preventing anyone from having a field in a
table with the same name as the table itself, or from including the name as
part of a field name, so even after you've found the name of the table, you
can't go blindly replacing it anywhere it happens to appear in the SQL.

This is a decidedly non-trivial parsing operation you've taken on here. I
can't help wondering why it would ever be necessary - if you don't know what
the name of the table is, how do you know it needs to be changed, let alone
to what it should be changed?

--
Brendan Reynolds (MVP)

tjh said:
Thank You,
This looks like what I need.
How can I determine the name of the first table "tblTest1" --the name of
the
table currently used in the query.
This will be the only table in the query.

Do you know of the code to determine the table/tables currently used in
the
Query. I think that once this is determined I can declare it as "tblTest1"
and the query will change as you show below.

Thank You,
Click to expand...
Click to expand...
 
From the code below: My problem is occurring between the FROM and the WHERE
clause.
Any suggestions? Thank you,

qryTest.SQL = "SELECT [Profit Center], [Amount], [Date] FROM ([Forms]![Inv
Rel]![AC1AC]) WHERE (((Date)=[forms]![Inv Rel]![AC1YR]& [forms]![Inv
Rel]![AC1MT]));"











tjh said:
I may be going about this in the wrong way.
I have several tables with three fields in them Contract, Amount, Date.
The amounts of each table represent the values of different types of
accounts. I am trying to use a form to allow my users to select which report
they would like to run. A combo box containes each tables name. I only want
to have to use one query. This is why my vba code would need to change the
SQL code within the specified query. Since the user would be running various
reports, the name of the table it is referencing would change each time the
user runs a new report. This is why you would not know the name of the table
that the query is currently using, and would need the vba script to change it
to your desired tablename (the name selected in the forms combobox). The
purpose of this is to essentially create one query which can maneuver to the
users needs rather than creating numerous additional queries for this single
userform.





Brendan Reynolds said:
That could get decidedly tricky. I suppose if you're sure that the query
includes only one table you could search the SQL for the text ' FROM ' and
then assume that everything between that text and the next space is the name
of the table - unless you have tables with spaces in their names, when
you'll have to search for the "[" and "]" characters following the ' FROM '
text.

Keep in mind that there's nothing preventing anyone from having a field in a
table with the same name as the table itself, or from including the name as
part of a field name, so even after you've found the name of the table, you
can't go blindly replacing it anywhere it happens to appear in the SQL.

This is a decidedly non-trivial parsing operation you've taken on here. I
can't help wondering why it would ever be necessary - if you don't know what
the name of the table is, how do you know it needs to be changed, let alone
to what it should be changed?

--
Brendan Reynolds (MVP)

tjh said:
Thank You,
This looks like what I need.
How can I determine the name of the first table "tblTest1" --the name of
the
table currently used in the query.
This will be the only table in the query.

Do you know of the code to determine the table/tables currently used in
the
Query. I think that once this is determined I can declare it as "tblTest1"
and the query will change as you show below.

Thank You,






:

CurrentDb.QueryDefs("qryTest").SQL=
Replace(CurrentDb.QueryDefs("qryTest").SQL,"tblTest1","tblTest2")

--
Brendan Reynolds (MVP)



Can you refer me to somewhere that I can look into changing the table
name
in an SQL query with VBA code. I am familiar (to some extent, about 4
yr
experience) with using VBA code. I have just never changed SQL code
with
variables. I think setting up or declaring the variables to change the
table
and field names is giving me the most trouble. Setting up to change the
table
name is my biggest problem, I think. I believe I can work around the
field
names.

Thank You






:

I think your best bet is 3 separate queries / reports if you are
unfamilliar
with code and the fields are different.

Thank You for your response.
Could you describe how I can modify the SQL to specify which table
to
use
when I click the "Launch" command. I am not sure how to go about
doing
this.
If I need to use VBA code, could you please give an example of the
code.
Also, the amount field title is different in each table one for
Sales,
one
for COS and one for Income.

Thank You,




:

You could, but if the field names are the same, why not just base
the
single
report on a single query and modify the SQL when you click the
"Launch
Report" button



Hello,

Is it possible to use a form to select which table a Query will
utilize.?

I have three different tables: 1)Sales 2)COS 3)Income.

Each table has 3 columns/fields 1)contract 2)amount 3)Date.

I need to run similar reports from each. I would like to run a
report
from
any of the three reports and then select the date range to run
from
the
report - using one form. The only way I know how to do this is to
create 3
different queries, and then have the user select on the Form
which
query
to
run. Is it possible to have the user select on the form which
table
to
use
in
one query?

Thank You
Click to expand...
Click to expand...
Click to expand...
 
The problem is a flawed database design. There should be only one table.

--
Brendan Reynolds (MVP)


tjh said:
I may be going about this in the wrong way.
I have several tables with three fields in them Contract, Amount, Date.
The amounts of each table represent the values of different types of
accounts. I am trying to use a form to allow my users to select which
report
they would like to run. A combo box containes each tables name. I only
want
to have to use one query. This is why my vba code would need to change the
SQL code within the specified query. Since the user would be running
various
reports, the name of the table it is referencing would change each time
the
user runs a new report. This is why you would not know the name of the
table
that the query is currently using, and would need the vba script to change
it
to your desired tablename (the name selected in the forms combobox). The
purpose of this is to essentially create one query which can maneuver to
the
users needs rather than creating numerous additional queries for this
single
userform.





Brendan Reynolds said:
That could get decidedly tricky. I suppose if you're sure that the query
includes only one table you could search the SQL for the text ' FROM '
and
then assume that everything between that text and the next space is the
name
of the table - unless you have tables with spaces in their names, when
you'll have to search for the "[" and "]" characters following the ' FROM
'
text.

Keep in mind that there's nothing preventing anyone from having a field
in a
table with the same name as the table itself, or from including the name
as
part of a field name, so even after you've found the name of the table,
you
can't go blindly replacing it anywhere it happens to appear in the SQL.

This is a decidedly non-trivial parsing operation you've taken on here. I
can't help wondering why it would ever be necessary - if you don't know
what
the name of the table is, how do you know it needs to be changed, let
alone
to what it should be changed?

--
Brendan Reynolds (MVP)

tjh said:
Thank You,
This looks like what I need.
How can I determine the name of the first table "tblTest1" --the name
of
the
table currently used in the query.
This will be the only table in the query.

Do you know of the code to determine the table/tables currently used in
the
Query. I think that once this is determined I can declare it as
"tblTest1"
and the query will change as you show below.

Thank You,






:

CurrentDb.QueryDefs("qryTest").SQL=
Replace(CurrentDb.QueryDefs("qryTest").SQL,"tblTest1","tblTest2")

--
Brendan Reynolds (MVP)



Can you refer me to somewhere that I can look into changing the
table
name
in an SQL query with VBA code. I am familiar (to some extent, about
4
yr
experience) with using VBA code. I have just never changed SQL code
with
variables. I think setting up or declaring the variables to change
the
table
and field names is giving me the most trouble. Setting up to change
the
table
name is my biggest problem, I think. I believe I can work around the
field
names.

Thank You






:

I think your best bet is 3 separate queries / reports if you are
unfamilliar
with code and the fields are different.

Thank You for your response.
Could you describe how I can modify the SQL to specify which
table
to
use
when I click the "Launch" command. I am not sure how to go about
doing
this.
If I need to use VBA code, could you please give an example of
the
code.
Also, the amount field title is different in each table one for
Sales,
one
for COS and one for Income.

Thank You,




:

You could, but if the field names are the same, why not just
base
the
single
report on a single query and modify the SQL when you click the
"Launch
Report" button



Hello,

Is it possible to use a form to select which table a Query
will
utilize.?

I have three different tables: 1)Sales 2)COS 3)Income.

Each table has 3 columns/fields 1)contract 2)amount 3)Date.

I need to run similar reports from each. I would like to run a
report
from
any of the three reports and then select the date range to run
from
the
report - using one form. The only way I know how to do this is
to
create 3
different queries, and then have the user select on the Form
which
query
to
run. Is it possible to have the user select on the form which
table
to
use
in
one query?

Thank You
Click to expand...
Click to expand...
Click to expand...
 
Back
Top