Generate Reports based on multiple tables?

[sgupta76]

I have one table for each salesrep in my commission database that tracks their individual transactions.
For example:
Salesperson A has Table 'tblSalesA'
Salesperson B has Table 'tblSalesB'

I want to generate a report called 'Transaction Details' for each Salesperson and save the report to a network folder.
I want to use only one report and change the Record Source of the report to the appropriate salesperson table and save it on the network folder.
How do I change the record source property of the report using VBA?
Thanks.

 

[dhookom]

First, it isn't generally a good idea to have each sales person in a separate table. You would generally keep all the Sales in a single table with a field to identify which salesperson made the sale.

I would create a union query from all the tables so that it would be one virtual recordset. The SQL might look like:
SELECT *, "Bob Smith" as Salesperson
FROM tblSalesA
UNION ALL
SELECT *, "Betty Anderson"
FROM tblSalesB
UNION ALL
SELECT *, "Jerry Woods"
FROM tblSalesC
UNION ALL
...other tables...;
You can then open the report with a WHERE CLAUSE or use a query with a criteria that references a control on a form.

YOu can save the report as a snapshot or PDF on your network folder.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sgupta76]

Hi Duane,
Thanks for the input. I think I will use your suggestion. I really didn't want to have separate table for each salesperson, but our sales comp plans are complex and it required me to have separate tables.
I just want to know out of curiousity, is it possible to change the RecordSource of a report at run time. If so how do you do that in VBA?
Thanks.

 

[dhookom]

Sorry, I should have answered that question in my first response. You can set the Record Source property of a report in the On Open event of the report. For instance, if you have a combo box [cboSalesPerson] on a form [frmSelectSP] then you could use code like:
[Blue]

    Dim strSQL as String
    strSQL = "SELECT * FROM tblSales" & Forms!frmSelectSP!cboSalesPerson
    Me.RecordSource = strSQL

[/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sgupta76]

Duane,
Just one more question. What if I don't want to use the On Open event of the report but am trying to run the report in another module. How do I reference a Report and change its record source. For example you can reference a form like [Forms]!frmFormName!...... I would like to know if it possible to reference reports like forms and change their record source property.


Thanks again.

 

[dhookom]

If you don't want to use my earlier suggestions then my next option would be to change the SQL of a saved query. Base your report on a query "qselSales". Then change the SQL using DAO code like:
Dim strSQL as String
strSQL = "SELECT * FROM tblSales" & Forms!frmSelectSP!cboSalesPerson
Currentdb.QueryDefs("qselSales&quot.SQL = strSQL


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[thewizz]

i dont know if it applys but know that the openreport function has a embeded WHERE Clause when you call it
small ex:
dim stlinkcriteria as string
stlinkcriteria "noProduit = " + me.mybox.text
openreport form1,stlinkcriteria,stNormal

(this code may have errors !!)

 

[dhookom]

That's what my first suggestion offered. It was combining all the tables using a UNION query and then opening with a where clause. The actual syntax would be closer to:
DoCmd.OpenReport "srptYourReport", acPreview, , stLinkCriteria

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sgupta76]

Thanks Duane. I think I will use the Union query as you suggested.