VBA code to create a report in MS Access

Question

Can anyone help me create a code satatement that will allow me to create a report with the sQ statement below? The problem I'm having is that my form allows you to input a Cost center, but when I click on the command button to execute the code it asks me to input the cost center again before it shows me the report. I want to eliminate having to enter the cost center again and just take it from when it is enters on the form.

Private Sub CmdCC_Click()

Set mydb = CurrentDb

myCC = txtCC.Value

If IsNull(myCC) Or myCC = "" Then
   MsgBox "Please enter a Cost Center!", vbCritical + vbOKOnly, pTitle

End If

 sQ = "SELECT ZBASED.ACCT_UNIT, CenterName, ZBASED.ACCOUNT, ZBASED.ACCOUNT_DESC " & _
    "FROM ZBASED, CCtable " & _
    "WHERE (ZBASED.ACCT_UNIT = " & myCC & ") And (CenterNo = " & myCC & ") " & _
    "ORDER BY ZBASED.ACCOUNT;"

Answer 1

All you have to do is reference the form you are calling the report from in the SQL, for example

SELECT foo FROM bar WHERE foo=[Forms]![frmReporting]![txtFoo]

You then have a button on frmFoo that opens the report, you can include some logic in before the docmd.OpenReport call to validate the input i.e. make sure they have entered a cost centre

Answer 2

If the report is already based on say,

SELECT ZBASED.ACCT_UNIT, CenterName, 
ZBASED.ACCOUNT, ZBASED.ACCOUNT_DESC 
FROM ZBASED, CCtable 

(There is no point in using ORDER BY with a report, you must use the report's own Oder & Grouping properties)

You can use the Where argument of OpenReport:

DoCmd.OpenReport "ReportName", acViewPreview, , "ZBASED.ACCT_UNIT = " & myCC _
         & " And CenterNo = " & myCC