System Selected Table for Query/Report

[Apprentice]

I pull several excel worksheets into Access "New Tables" useing code.

I have several reports and corisponding queries that have already been
developed.

Because the tables are new, I would like a combo box that lists the new
tables for selection when calling a report. So the user would select the
table from the combo box and the query and report would then be produced
based on that selection.

Any Ideas? I have a direction useing:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

But not sure what order and how to put it all together?

Thanks in advance for any help.

 

[Piet Linden]

I pull several excel worksheets into Access "New Tables" useing code.

I have several reports and corisponding queries that have already been
developed.

Because the tables are new, I would like a combo box that lists the new
tables for selection when calling a report.  So the user would select the
table from the combo box and the query and report would then be produced
based on that selection.

Any Ideas?  I have a direction useing:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

But not sure what order and how to put it all together?

Thanks in advance for any help.

Something is fishy here. Why do you always import into new tables?
What if you were to delete the contents of the destination table, run
the canned TransferSpreadsheet/Imports and then open the reports?

 

[Apprentice]

Hi Piet, you helped me yesterday with the importing of excel workbooks,
thanks again..

This is a once a year project that I am building a user database for. There
are 70 workbooks in which I pull into the database as tables. The
reports/queries will not change for a year. Not really a need to overright
the tables.

There are only 7 reports based on the tables. all seven are the same only
from different tables/queries.

The user imports all workbooks from a common directory with a command
botton. Instead of creating 70 queries and reports. I was hoping the user
could select what data set/table they wanted the report for and thats the one
they get.

So the query and report would be like a wildcard and produce based on the
original selection.

Does this make any sense?



Thanks


--
Your guidance is greatly appreciated!

I pull several excel worksheets into Access "New Tables" useing code.

I have several reports and corisponding queries that have already been
developed.

Because the tables are new, I would like a combo box that lists the new
tables for selection when calling a report. So the user would select the
table from the combo box and the query and report would then be produced
based on that selection.

Any Ideas? I have a direction useing:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) ORDER BY
MSysObjects.Name;

But not sure what order and how to put it all together?

Thanks in advance for any help.

Something is fishy here. Why do you always import into new tables?
What if you were to delete the contents of the destination table, run
the canned TransferSpreadsheet/Imports and then open the reports?

 

[Piet Linden]

Hi Piet, you helped me yesterday with the importing of excel workbooks,
thanks again..

This is a once a year project that I am building a user database for.  There
are 70 workbooks in which I pull into the database as tables.  The
reports/queries will not change for a year.  Not really a need to overright
the tables.

There are only 7 reports based on the tables.  all seven are the same only
from different tables/queries.

The user imports all workbooks from a common directory with a command
botton.  Instead of creating 70 queries and reports.  I was hoping the user
could select what data set/table they wanted the report for and thats theone
they get.

So the query and report would be like a wildcard and produce based on the
original selection.

Does this make any sense?

Thanks

Wow, that was a lot harder than I thought... must be the rust...
Anyway, this worked... The basic idea is that you create some code
that does the following:
1. opens the report in design mode (hidden)
2. changes the recordsource
3. closes the report and saves the changes
4. opens the same report again in preview mode.
5. might not be a bad idea to reopen in design mode and reset the
recordsource to something you know is valid..? or do it in the error
condition ... so it will open something next time...

This is a minimalist form:
- a combobox cboRecordSource
- a button: cmdChangeSrcAndPreview

the control source for the combobox:
SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name)
Like "*11") AND ((MSysObjects.Type)=5)) ORDER BY MSysObjects.Name;

You could use a list, since yours is static. Doesn't matter...
whatever works for you. This is just a quick and dirty example.

Code behind your button:

Option Compare Database
Option Explicit

Private Sub cmdChangeSrcAndPreview_Click()
On Error GoTo Err_cmdChangeSrcAndPreview_Click

Dim stDocName As String

stDocName = "Zip Code Report"

'--open the report in design view (hidden) so we can change the
recordsource
DoCmd.OpenReport stDocName, acViewDesign, , , acHidden
Reports![Zip Code Report].RecordSource = Me.cboRecordSource

'--save the changes
DoCmd.Close acReport, stDocName, acSaveYes

'--open the report
DoCmd.OpenReport stDocName, acPreview


Exit_cmdChangeSrcAndPreview_Click:
Exit Sub

Err_cmdChangeSrcAndPreview_Click:
MsgBox Err.Description
Resume Exit_cmdChangeSrcAndPreview_Click

End Sub

and there you have it... (but do you have whiskey in the jar?)

Metallica on the brain, what can I say?

Pieter