By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,345 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,345 IT Pros & Developers. It's quick & easy.

Changing the source of a report and query

P: n/a
I am using a form to select a table and then opening a report using the
selected table as the record source. This is a simplified description of
what I'm doing.

First, cboSelectBOM selects the table from a list of tables in the
database. Then, command button cmdPrintXref produces the following code:

Private Sub cmdPrintXref_Click()
ReportSource = cboSelectBOM
DoCmd.OpenReport "rptPartNoXref", acViewPreview
End Sub

The report contains uses the OnOpen event to run "Me.RecordSource =
ReportSource"

This works fine and the report opens with the selected table as the
report source. However, I now need to run a query on the table and I have
not been able to figure out how to define the report source in the query.
Can anyone suggest a method of doing this?

A related question - if I set up a query based on a specific table, is
there an easy way to change the table without adding the new table and
then changing each field individually?

Any help will be greatly appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Bob Sanderson wrote:
I am using a form to select a table and then opening a report using the
selected table as the record source. This is a simplified description of
what I'm doing.

First, cboSelectBOM selects the table from a list of tables in the
database. Then, command button cmdPrintXref produces the following code:

Private Sub cmdPrintXref_Click()
ReportSource = cboSelectBOM
DoCmd.OpenReport "rptPartNoXref", acViewPreview
End Sub

The report contains uses the OnOpen event to run "Me.RecordSource =
ReportSource"

This works fine and the report opens with the selected table as the
report source. However, I now need to run a query on the table
What table? The one selected in the combo?

and I have not been able to figure out how to define the report source in the query.
Can anyone suggest a method of doing this?
Can you open a recordset instead. Ex:
Dim strSQL As String
Dim rst as recordset
strSQL = "Select * from " & ReportSource & " WHere ...."
set rst = currentdb.openrecordset(strSQL)
rst.MoveFirst
Do while not rst.EOF
Process
rst.MoveNext
Loop
rst.Close
set rst = Nothing


A related question - if I set up a query based on a specific table, is
there an easy way to change the table without adding the new table and
then changing each field individually?
Well, you could have a "junk" query and stuff the SQL string into it.
Sub qt()
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Query1")
MsgBox qdf.SQL
qdf.SQL = "Select * From AnotherTable"
MsgBox qdf.SQL
DoCmd.OpenQuery "Query1"
Set qdf = Nothing
End Sub

Any help will be greatly appreciated.

Nov 13 '05 #2

P: n/a
What table? The one selected in the combo?


Yes
Nov 13 '05 #3

P: n/a
Bob Sanderson wrote:
What table? The one selected in the combo?

Yes


And your response to the rest of the questions?

Could you expand upon the problem? Provide more details?

Nov 13 '05 #4

P: n/a
Salad <oi*@vinegar.com> wrote in news:lRGJd.6143$r27.2742
@newsread1.news.pas.earthlink.net:
Bob Sanderson wrote:
What table? The one selected in the combo?

Yes


And your response to the rest of the questions?

Could you expand upon the problem? Provide more details?


Your response to my question has provided me with a solution but I'll
describe the situation for the benefit of anyone else who's following the
thread.

I have a form which displays a list of tables. The idea is to select a
table using a combo box (cboSelectBOM) and then open a report
(rptPartNoXref) with that table as a record source while applying a query
to that source.

This is my final code:

Dim strSQL As String

strSQL = "SELECT DISTINCT [" & cboSelectBOM & "].DashNo, [" &
cboSelectBOM & "].ItemNo, [" & cboSelectBOM & "].Manufacturer, [" &
cboSelectBOM & "].MfrPartNo, [" & cboSelectBOM & "].Vendor, [" &
cboSelectBOM & "].VendorPartNo, [" & cboSelectBOM & "].JobNo, [" &
cboSelectBOM & "].BoMRev FROM " & cboSelectBOM & " WHERE ((([" &
cboSelectBOM & "].MfrPartNo)<>[VendorPartNo])) OR ((([" & cboSelectBOM &
"].JobNo) Is Not Null)) ORDER BY [" & cboSelectBOM & "].MfrPartNo;"

ReportSource = strSQL
DoCmd.OpenReport "rptPartNoXref", acViewPreview

The report contains the following OnOpen function:

Me.RecordSource = ReportSource

Thanks for the help.


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.