Get Table name for a query from form

[briana.askew]

Ok this is what I'm trying to do:

I have a database with about 30 tables (which are actually linked to Excel
spredsheets). I need to be able to query each table and make a report only
showing certain information for it. I started by doing individual queries for
each table and individual reports for each query. That slowed the program
down ALLOT! and made it crash all the time because it became so large. I
figured out how to get a query for a list of table names using the
MSysObjects table but I need to know how to have a user open a form and
select a table name and have a query run on that table based on the user's
selection. Ex. I have Tables called class A, class B, and class C. In each
table is a list of student names, ages, and...height. I want a user to be
able to open a form and in a drop down menu (or whatever) select class c and
hit a command button that will start a query which will then produce a report
listing only the students in that class over the age of 18 and with there
name and age only appearing. PLEASE PLEASE HELP!!!!!

 

[MGFoster]

Ok this is what I'm trying to do:

I have a database with about 30 tables (which are actually linked to Excel
spredsheets). I need to be able to query each table and make a report only
showing certain information for it. I started by doing individual queries for
each table and individual reports for each query. That slowed the program
down ALLOT! and made it crash all the time because it became so large. I
figured out how to get a query for a list of table names using the
MSysObjects table but I need to know how to have a user open a form and
select a table name and have a query run on that table based on the user's
selection. Ex. I have Tables called class A, class B, and class C. In each
table is a list of student names, ages, and...height. I want a user to be
able to open a form and in a drop down menu (or whatever) select class c and
hit a command button that will start a query which will then produce a report
listing only the students in that class over the age of 18 and with there
name and age only appearing. PLEASE PLEASE HELP!!!!!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to use VBA to dynamically change a query to look in the
selected table. The form and the report have to have these VBA
routines:

THE FORM

Private Sub cmdRunReport_Click()
'
' A command button on the form, named "cmdRunReport" opens the
' report.

DoCmd.OpenReport "ReportName"

' put in some error handling routines - for Cancelled error

End Sub

THE REPORT

Private Sub Report_Open(Cancel As Integer)
'
' The report reads the ComboBox on the form that holds
' the name of the table to report on:

' this is the query that will have a table name
' added to it.
Const SQL = "SELECT student_first_name, student_last_name, " & _
"student_age " & _
"FROM |1 " & _
"WHERE student_age > 18"

Const FRM = "FormName" ' change to your form's name

' make sure we have a table name to work with
if not isnull(Forms(FRM)!cboTableName) then

' this command puts the table name into the SQL query
' and sets the report's RecordSource
Me.RecordSource = Replace(SQL, "|1", Forms(FRM)!cboTableName))

else

' this cancels the report with a message
cancel = true
msgbox "No Table Name", vbcritical, Me.Name

end if

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmaEH4echKqOuFEgEQIJ7gCcCOiH7NAE/HImvbCJ9Jz1NVmpKrIAn0E9
03/837Z7aMRpC14tAnKPIj09
=cCtT
-----END PGP SIGNATURE-----

 

[briana.askew]

Ok what you wrote didn't work exactly how you said ( I probably have it
wrong!) but it did put me on the right track! I can't seem to get the SQL
part of what you wrote working.

I just put in another being a little more specific on exactly what I'm
trying to do and what I've managed so far:
Here's what I have so far:

Every month I pull a bunch of reports from a website. I work for the Army
and it is a list of names of people in different Army branchs. Each branch
has multiple units so I have to pull the list by unit and put them in Excel
spreadsheets so they can be combined for each branch. This leaves me with
about 50 Excel spreedsheets in one workbook each branch made up of about 5
units. I've created a Access Database that links to each Excel spreedsheet in
this workbook and which creates a table for each spreedsheet (I know the
design is strange and not ideal but if you saw the way I have to get this
info you would understand there is no other way). I need the database to
query whatever table I want it to and then give me a report based on that
query (the criteria for the query never changes). I started by creating
queries and reports for every table and that left me with a huge database
that was slow and crashed all the time. I also realized that if I ever leave
this job and the Army comes out with a new branch this program would be
useless cause no one else in my office would know how to create the queries
and the reports to pull that branch. I figured creating a table for a new
branch would be easy and I could teach that to someone here but everything
else would have to be automatically updated. I asked a question here the
other day about how to change the table name in a query based on the users
choice in a form, I got some code that didn't really work (I'm probably doing
something wrong!)but it definitly put me on the right track. I've created one
form that asks the user what branch they want to pull a report on using a
combo box. The recordsource for the combo box is a query on the msysobjects
table so (if understand correctly) all my coworkers would have to do is
create a new table for the new branch and link it to it's Excel spreadsheet
and that would automatically update in the msysobjects table making it an
vailable choice in my combo box. The form then uses the choice made to open a
report. This is workign fine and the report does recognize the table name
from the user's choice; however, it is giving me a report of all the table's
records and only need those that meet the criteria I want. How can I get the
report to look at one field (the field is called "missing") and only display
those records that havea value in them or have a particular value in them? I
can't do a query because I can't figure out how to get the query to change
it's tab;e source based on a user entry in a form... Any suggestions?!!?
Thanks

 

[MGFoster]

briana.askew wrote:

The form then uses the choice made to open a
report. This is workign fine and the report does recognize the table name
from the user's choice; however, it is giving me a report of all the table's
records and only need those that meet the criteria I want. How can I get the
report to look at one field (the field is called "missing") and only display
those records that havea value in them or have a particular value in them? I
can't do a query because I can't figure out how to get the query to change
it's tab;e source based on a user entry in a form... Any suggestions?!!?
Thanks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe I over thunk the solution to your original problem.

I believe that now you are just putting the table name in the report's
RecordSource. So, now, all you have to do is use the WhereCondition
parameter of the OpenReport method:

DoCmd.OpenReport "Report Name", WhereCondition:="missing IS NOT NULL"

This will filter the report's data to only those rows (records) that
have some data in the "missing" column (field). If you want a specific
value in the "missing" column the parameter would be like this:

WhereCondition:="missing='specific value'"

Note the single quote inside the double quotes. If the data type of
"missing" is not Text then the parameter would look like this:

WhereCondition:="missing=255"

The WhereCondition parameter is supposed to look just like the WHERE
clause in an SQL statement.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmewT4echKqOuFEgEQJkWACgoEEWvXvwSSvy0cXfi+kjbsISjGgAn1jl
kE1ZxFSgCKADMct2XZaZ/ClL
=ikPm
-----END PGP SIGNATURE-----