Pulling data to a report based on double filter

[Zoe]

I have created a form that filters a report using two combo boxes - cboCC and
cboEAN. The user chooses the cboCC first (CostCenterID), then the cboEAN
(ExpenseAcctID) and then hits cmdFilterReport which opens the report and
filters the report.

This is what I have:

Dim iFilterCC As Integer
Dim iReportType As Integer
Dim iFilterEAN As Integer

If IsNull(cboEAN) Then
MsgBox ("Please select a filter criteria.")
Else
iFilterCC = cboCC 'value from the Cost Center combo box
iFilterEAN = cboEAN 'value from the Expense Acct Number combo box

Reports![rptBudget].Filter = "[CostCenterID] = " & iFilterCC & " AND
[ExpenseAcctNumberID] = " & iFilterEAN

Reports![rptBudget].FilterOn = True

End If

Now what I want to do is pull data from a table - tblBudget also based on
the above chosen filters. This table lists a CostCenterID and ExpenseAcctID
with a corresponding Budget Amount. I want to be able to pull the amount
field to the report that corresponds to the two filters chosen. This table
has the following fields:
BudgetID
CostCenterID (from tlkpCostCenter)
ExpenseAcctID (from tlkpExpenseAccount)
BudgetAmount

What I want to do is once the user chooses the two filter values, I want the
report to open and be filtered but also want the report to pull data from the
tblBudget for those same filter values. I am not sure how to go about this.
Also - I have not linked the tblBudget to anything yet - not sure what kind
of link to set up?

Thanks!

 

[Pat Hartman]

You can't set the filter for the report until the report is open. Rather
than using the filters, use the where argument of the OpenReport method to
pass the criteria. This is more efficient since the data is filtered before
the report is opened.