Dynamically Creating Reports (1 Viewer)

jeran042 · Nov 16, 2017

Good morning all,

I have a report with some calculated fields on it. And these fields are hyperlinked to open a form with the detail that makes up the calculation.

On that form, there is a button to export the details to excel (via ExportWithFormatting), and everything works fine if there is only one variable in the [COST_CENTER] field. Meaning, if you are exporting the detail and it is only looking at 1 number it exports with no issue.

The problem lies when you open a form with multiple [COST_CENTER], the where clause is only picking up the fist variable. Below is a sample of the where clause I am using,

My question is, how can I adjust this to pick up all the variables?

Code:

[COST_CENTER]=[Forms]![frm: Head Count]![COST_CENTER]

,

Very much appreciated,

Ranman256 · Nov 16, 2017

reports shouldnt really open forms.
If you want to query multiple cost centers , I use a 'picked' table. The user picks the multiple items to filter in the query (CostCenters) then join the picked table to the data table to get only those items.
Then export it.

jeran042 · Nov 16, 2017

Very interesting! I sort of inherited this db.
Following your statement, why shouldn't report open forms?

If I was creating this from scratch, I obviously would have built these "reports" as forms, and generated reports off of the data,

Having said that, I believe I have to build a an sql statement, but unsure how the clause would go, any thoughts?

Pat Hartman · Nov 17, 2017

Where does the criteria for the report originate? Use whatever technique that code is using. It is probably building an SQL string that has an In(...) clause that includes the multiple cost centers.

Minty · Nov 17, 2017

Actually a report opened as a form can have some benefits as it's read only viewing.

For instance you can get the text boxes to grow and shrink on a continuous report based form, which is a neat way of displaying variable length text fields.

arnelgp · Nov 17, 2017

change your criteria to just:

fnCostCenter([COST_CENTER])

then copy and paste the code in New Standard Module:

Code:

Public Function fnCostCenters(varCenter) As Boolean
        Dim rs As DAO.Recordset
        Dim Delimited As String
        Set rs = [Forms]![frm: Head Count].Form.RecordsetClone
        With rs
            .FindFirst "[COST_CENTER]=" & FixSQL(varCenter)
            fnCostCenters = (.NoMatch = False)
            .Close
        End With
        Set rs = Nothing
End Function

Public Function FixSQL(p As Variant) As Variant
    Select Case VarType(p)
    Case VbVarType.vbNull
        FixSQL = "Null"
    Case VbVarType.vbString
        FixSQL = """" & p & """" 'Chr(34) & p & Chr(34)
    Case VbVarType.vbBoolean, VbVarType.vbByte, _
        VbVarType.vbCurrency, VbVarType.vbDecimal, _
        VbVarType.vbDouble, VbVarType.vbInteger, _
        VbVarType.vbLong, VbVarType.vbSingle
        FixSQL = p
    Case VbVarType.vbDate
        FixSQL = "#" & Format(p, "mm/dd/yyyy") & "#"
    End Select
End Function

jeran042 · Nov 17, 2017

When I update the where clause in my macro to "
fnCostCenter([COST_CENTER])" and paste the code in a new module, I get the error message "Undefined function 'fnCostCenter' in expression?

arnelgp · Nov 17, 2017

did you paste it in moule. Alt F11. insert module. paste the code

jeran042 · Nov 17, 2017

I did actually, please see the attached screen shot

jeran042 · Nov 17, 2017

Sorry, in addition, this is what the full macro looks like,

arnelgp · Nov 17, 2017

the function is in plural. fnCostCenters

arnelgp · Nov 17, 2017

di you use this query for other purpose? if not can you edit the query in dwsign view. go to sql view and put the criteria there. then remove the where from your macro. if you are using the query for other purpose, make a copy and use this copy to set the query and use in your macro.

arnelgp · Nov 17, 2017

ok just tested it with macro and it works. no need to modify the query or macro

jeran042 · Nov 17, 2017

So now I am being promted for the parameter "

jeran042 · Nov 17, 2017

So now I am being promoted for the parameter "fnCostCenters"
Please excuse the previous message

arnelgp · Nov 17, 2017

ok, what is the real name of the field, cost center? put that inside the function.

Dynamically Creating Reports (1 Viewer)

jeran042

Registered User.

Ranman256

Well-known member

jeran042

Registered User.

Pat Hartman

Super Moderator

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

jeran042

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

jeran042

Registered User.

Attachments

jeran042

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

jeran042

Registered User.

jeran042

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread