Running a query on a table based on a form.

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Hi all,

This is probably a simple question. I have a database of information and a
form that allows the user to specify the daterange and material that are
then passed to a query.

Unfortunately I have then got to select the table programatically as I
cannot find a "ALL tables" entry in the query designer window. These tables
are added and removed so I have a bit of code that searches for the tables
and adds them to a combo box.


Dim CurrentTablename

For Counter = 1 To Application.CurrentDb.TableDefs.Count - 1
CurrentTablename =
Application.CurrentDb.TableDefs.Item(Counter).Name
If Left(CurrentTablename, 4) <> "MSys" Then
Me.Material.AddItem CurrentTablename
End If
Next

this works !!! to get a list of meterials. I then want to run a query on the
material selected (TABLE selected).

(SELECT * from [Me.Material]) does not appear to work. Any thoughts or
hints?

Code snippets appreciated.

Donald Goodlad
Australia.
 
Compose the SQL for the query in code, such as

dim cSQL as string
cSQL = "SELECT * FROM " & Material.Value & " WHERE someclause"

You say "run a query"; if you want to update, use UPDATE of course. If
you want to do something with the resulting dataset, record by record,
you can do

dim rs as recordset
set rs = currentdb.openrecordset(cSQL)
Unfortunately I have then got to select the table programatically as I
cannot find a "ALL tables" entry in the query designer window. These tables
are added and removed so I have a bit of code that searches for the tables
and adds them to a combo box.
Click to expand...

The combobox can be a table/query type, with as its source

select name from msysobjects where type=1 and name not like "msys*"

(if you want to include linked tables too, their type is 6)
 
Don,

Me.Material should not be enclosed in the quotes, so Access treats it as a
variable rather than a text string. Change your SQL statement to:
strSQL = "SELECT * FROM " & Me.Material & " WHERE....."

By the way, your For... loop misses out the firt table; It should be:
For Counter = 0 to Application.CurrentDb.TableDefs.Count - 1

One table per material???? Quite unusual db design... unless there is a good
reason for it, it seems like a sub-optimal design.

HTH,
Nikos
 
Nikos,

Thanks for the hints. I have reports I want to display but due to the fact
they are based on queries and the queries are based on tables I am stuck. Is
there an easy way to overcome this? As you can see I am using SelectedStart
and SelectedEnd to limit the data for the reports I can use variables from
forms in queries but not good with access. But I'm still stuck with the
issue of queries being limited to one table. Can you help in this matter?

1. Change the Reports. So the data is from an SQL statement like the one you
sent in your first reply.

2. Suggest a better database table structure. At present I have a series of
tables for different materials in a logging.mdb and a set of additional data
for each of those records in a materials.mdb. I am creating a MDB called
reports.MDB to pull data out for laymen to use. (most cannot see what the
data means without comparing it to yesterdays results.) I then plan on
charting the data using MSCHART1 so people can see a simple comparison.



Dim stDocName As String

Dim Counter

Dim test1


SelectedStart = FROM_Month & "/" & FROM_day & "/" & FROM_year & " " &
Format("00:00:00", "General Date")

SelectedEnd = TO_Month & "/" & To_Day & "/" & TO_Year & " " &
Format("23:59:59", "General Date")


If ReportToPrint = 1 Then

stDocName = "Statistical Summary Report DateRange"

End If


If ReportToPrint = 2 Then

stDocName = "Generic Query Report DateRange"

End If

DoCmd.OpenReport stDocName, acPreview



An help appreciated.

Donald Goodlad

Nikos Yannacopoulos said:
Don,

Me.Material should not be enclosed in the quotes, so Access treats it as a
variable rather than a text string. Change your SQL statement to:
strSQL = "SELECT * FROM " & Me.Material & " WHERE....."

By the way, your For... loop misses out the firt table; It should be:
For Counter = 0 to Application.CurrentDb.TableDefs.Count - 1

One table per material???? Quite unusual db design... unless there is a good
reason for it, it seems like a sub-optimal design.

HTH,
Nikos

Don said:
Hi all,

This is probably a simple question. I have a database of information
Click to expand...
and
a
form that allows the user to specify the daterange and material that are
then passed to a query.

Unfortunately I have then got to select the table programatically as I
cannot find a "ALL tables" entry in the query designer window. These tables
are added and removed so I have a bit of code that searches for the tables
and adds them to a combo box.


Dim CurrentTablename

For Counter = 1 To Application.CurrentDb.TableDefs.Count - 1
CurrentTablename =
Application.CurrentDb.TableDefs.Item(Counter).Name
If Left(CurrentTablename, 4) <> "MSys" Then
Me.Material.AddItem CurrentTablename
End If
Next

this works !!! to get a list of meterials. I then want to run a query on the
material selected (TABLE selected).

(SELECT * from [Me.Material]) does not appear to work. Any thoughts or
hints?

Code snippets appreciated.

Donald Goodlad
Australia.
Click to expand...
Click to expand...
 
Bas Cost Budde,

Thanks for the hint about the linked tables and your prompt response. All
the Tables are linked tables.
This is becuase I am making this a reports Database containing only reports
and queries. I want to LOCKDOWN to VBA and leave the users to create now
queries and leaving all the data in the other two databases untouched
Materials.mdb and logging.mdb. These databases are updated by an external
application which I can change if required. I thought that keeping each
table a different material would make it a lot easier. The material name is
still stored in the table as a field and I know this is inefficient but I
did not know I could not know It was so hard to select all tables for a
query. I responded to the other response also with additional data and
code.
 
Don,

On the db structure: I would suggest one table for all materials in both
back end databases.
Then you only need one query, and can restrict the report to the selected
material by either:

(a) using a criterion in the query design which reads the material selection
on the form (e.g. = Forms!MyFormName!ControlMaterial), or
(b) adding a where clause in the VB statement that opens the report:
DoCmd.OpenReport stDocName, acPreview , , "Material = " &
Forms!MyFormName!ControlMaterial

HTH,
Nikos

Don said:
Nikos,

Thanks for the hints. I have reports I want to display but due to the fact
they are based on queries and the queries are based on tables I am stuck. Is
there an easy way to overcome this? As you can see I am using SelectedStart
and SelectedEnd to limit the data for the reports I can use variables from
forms in queries but not good with access. But I'm still stuck with the
issue of queries being limited to one table. Can you help in this matter?

1. Change the Reports. So the data is from an SQL statement like the one you
sent in your first reply.

2. Suggest a better database table structure. At present I have a series of
tables for different materials in a logging.mdb and a set of additional data
for each of those records in a materials.mdb. I am creating a MDB called
reports.MDB to pull data out for laymen to use. (most cannot see what the
data means without comparing it to yesterdays results.) I then plan on
charting the data using MSCHART1 so people can see a simple comparison.



Dim stDocName As String

Dim Counter

Dim test1


SelectedStart = FROM_Month & "/" & FROM_day & "/" & FROM_year & " " &
Format("00:00:00", "General Date")

SelectedEnd = TO_Month & "/" & To_Day & "/" & TO_Year & " " &
Format("23:59:59", "General Date")


If ReportToPrint = 1 Then

stDocName = "Statistical Summary Report DateRange"

End If


If ReportToPrint = 2 Then

stDocName = "Generic Query Report DateRange"

End If

DoCmd.OpenReport stDocName, acPreview



An help appreciated.

Donald Goodlad

Nikos Yannacopoulos said:
Don,

Me.Material should not be enclosed in the quotes, so Access treats it as a
variable rather than a text string. Change your SQL statement to:
strSQL = "SELECT * FROM " & Me.Material & " WHERE....."

By the way, your For... loop misses out the firt table; It should be:
For Counter = 0 to Application.CurrentDb.TableDefs.Count - 1

One table per material???? Quite unusual db design... unless there is a good
reason for it, it seems like a sub-optimal design.

HTH,
Nikos

Don said:
Hi all,

This is probably a simple question. I have a database of information
Click to expand...
and
a
form that allows the user to specify the daterange and material that are
then passed to a query.

Unfortunately I have then got to select the table programatically as I
cannot find a "ALL tables" entry in the query designer window. These tables
are added and removed so I have a bit of code that searches for the tables
and adds them to a combo box.


Dim CurrentTablename

For Counter = 1 To Application.CurrentDb.TableDefs.Count - 1
CurrentTablename =
Application.CurrentDb.TableDefs.Item(Counter).Name
If Left(CurrentTablename, 4) <> "MSys" Then
Me.Material.AddItem CurrentTablename
End If
Next

this works !!! to get a list of meterials. I then want to run a query
Click to expand...
on
the
material selected (TABLE selected).

(SELECT * from [Me.Material]) does not appear to work. Any thoughts or
hints?

Code snippets appreciated.

Donald Goodlad
Australia.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top