Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2017, 06:55 AM   #1
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Dynamically Creating Reports

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,

jeran042 is offline   Reply With Quote
Old 11-16-2017, 08:55 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,340
Thanks: 0
Thanked 737 Times in 722 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Dynamically Creating Reports

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.

pick list states -lbls.png
Ranman256 is offline   Reply With Quote
Old 11-16-2017, 11:02 AM   #3
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Dynamically Creating Reports

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?

jeran042 is offline   Reply With Quote
Old 11-16-2017, 04:14 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,953
Thanks: 13
Thanked 1,534 Times in 1,460 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Dynamically Creating Reports

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-16-2017, 04:23 PM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,356
Thanks: 159
Thanked 1,706 Times in 1,676 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Dynamically Creating Reports

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 11-17-2017, 01:05 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Dynamically Creating Reports

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-17-2017, 06:01 AM   #7
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Dynamically Creating Reports

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?
Attached Images
File Type: jpg error.message.JPG (17.6 KB, 34 views)

jeran042 is offline   Reply With Quote
Old 11-17-2017, 06:05 AM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Dynamically Creating Reports

did you paste it in moule. Alt F11. insert module. paste the code
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-17-2017, 06:07 AM   #9
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Dynamically Creating Reports

I did actually, please see the attached screen shot
Attached Images
File Type: jpg code.paste.JPG (72.9 KB, 49 views)
jeran042 is offline   Reply With Quote
Old 11-17-2017, 06:15 AM   #10
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Dynamically Creating Reports

Sorry, in addition, this is what the full macro looks like,
Attached Images
File Type: jpg full macro.JPG (34.2 KB, 47 views)
jeran042 is offline   Reply With Quote
Old 11-17-2017, 06:15 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Dynamically Creating Reports

the function is in plural. fnCostCenters
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-17-2017, 06:24 AM   #12
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Dynamically Creating Reports

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-17-2017, 06:28 AM   #13
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Dynamically Creating Reports

ok just tested it with macro and it works. no need to modify the query or macro
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-17-2017, 08:12 AM   #14
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Dynamically Creating Reports

So now I am being promted for the parameter "
jeran042 is offline   Reply With Quote
Old 11-17-2017, 08:14 AM   #15
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Dynamically Creating Reports

So now I am being promoted for the parameter "fnCostCenters"
Please excuse the previous message
Attached Images
File Type: jpg parameter.JPG (16.2 KB, 35 views)

jeran042 is offline   Reply With Quote
Reply

Tags
exportwithformatting , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically creating table using form Niros Modules & VBA 1 03-29-2012 04:46 AM
Form Controls - dynamically creating Ginny2222 Forms 8 11-03-2011 03:08 PM
Dynamically Creating MS Access Table SehDavies Tables 1 02-05-2008 06:17 AM
Connecting Two Reports Dynamically? Zinc Reports 1 08-14-2004 07:29 PM
creating text boxes dynamically farrah Forms 3 07-04-2002 04:20 AM




All times are GMT -8. The time now is 12:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World