Results 1 to 11 of 11
  1. #1
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47

    Modular Report // generating SQL string as identifier

    I have a form (designed and implemented by user Gicu (https://www.accessforums.net/showthr...ighlight=Boost) which allows me to open a query, apply a custom filter, and view how many results match that particular request.

    On this form, there is a textbox that contains the string filter.

    I was wondering if there was a way to export this particular string to a report on an on-click method?

    Understandably, this filter string will be a new variable each time someone changes what they are searching for.

    I want to do this, so that each report has an automated title - identifying what the results are showing.

    Attached is an image that supports my textual breakdown above.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	22 
Size:	127.9 KB 
ID:	35278

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just pass the filter in the docmd.openreport action - it is one of the parameters
    I want to do this, so that each report has an automated title - identifying what the results are showing.
    don't understand what this means - use the filter as a file name?

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I think he just wants the filter string displayed as the title of the report, as in shown in the header?

    Place a new textbox in on the report. Set the control source of a text box equal to openparams as ajax suggested OR just equal to the textbox in the filter form itself.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think he just wants the filter string displayed as the title of the report, as in shown in the header?
    good catch! misread the requirement

  5. #5
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Sorry Ajax, I'll clarify.

    I have a template report that opens on the cmd.OpenReport.

    What I'd like to see is the filter string as an identifier - or as kd2017 has identified, as a title.

    Thanks for the feedback both of you - will attempt to get that sorted now!

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your form has a button click event to open a report? Then you could use the expression (or one like it) for the control that you're showing in your pic. Then pass that literal string to the Open Args parameter in the DoCmd object's Open Report method. In other words, if txtMyCriteria is a report control for holding the string, you'd do

    DoCmd.OpenReport "rptMyReport", acViewPreview, , , , "[Gender] = 'Male' AND [LOC] Like 'UPP'"

    then in the report open code, make the textbox that will hold the string = Me.OpenArgs as in

    Me.txtMyCriteria = Me.OpenArgs
    The red part should be the OpenArgs parameter if I got the parameter positions correct.

    Note: your process should determine if the report is already open because if it is, it will simply restore it to view and won't reflect any changes to the criteria on the form. I believe that would be

    Code:
    If Application.CurrentProject.AllReports ("rptMyReport").IsLoaded Then DoCmd.Close "rptMyReport"
    
    DoCmd.OpenReport "rptMyReport", acViewPreview, , , , "[Gender] = 'Male' AND [LOC] Like 'UPP'"
    All suggested code here is "air code" (from my small head), and is untested for your situation. Aside from assuming the report view would be print preview, I didn't provide any other parameters other than the default ones when opening your report.

    EDIT: If your criteria string can be long, it may be easier to read your code if you assign the string to a variable, such as

    strCrit = "[Gender] = 'Male' AND [LOC] Like 'UPP'" (strCrit is the string variable that you would declare)
    DoCmd.OpenReport "rptMyReport", acViewPreview, , , , strCrit
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Hi all, have managed to link the title of the report to that of tbl_AdvancedSearch's filter textbox.

    Now, what I would like to achieve is for the form to open with the filter from tbl_AdvancedSearch.

    I have tried a few different methods - but it doesn't seem to operate as I intend.

    What would be the best way for me to pass the filter applied to the query in tbl_AdvancedSearch to that of my report via the openReport button?

    As mentioned - the title of my report on the other side of openReport button is that of the tbl_AdvancedSearch filter (I have already tried to directly apply the contents of this title textbox to that of the report filter, but it doesn't want to work).

    Cheers

    Boost

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have tried a few different methods - but it doesn't seem to operate as I intend.
    I have already tried to directly apply the contents of this title textbox to that of the report filter, but it doesn't want to work
    you've been given a number of example solutions that do work, so either you haven't tried them, you have not applied them correctly or you haven't told us something which would impact the solution. Either way, 'doesn't work' doesn't help us to help you.

    Provide all of the actual code from the sub or function you tried (not a free typed approximation) that didn't work and also explain why it didn't work - nothing happened, wrong result, you get an error (in which case what is the error description) or something else. Also provide some example data - in particular the filter string you have created and any other variables.

  9. #9
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Morning Ajax, you were right - there was an error in my code which I ironed out last night and managed to successfully complete! Thanks very much for clarifying the rules of said forum, and I will endeavour to stick to them.

    The way that I achieved linking the contents of frm_advancesearch's filter to the report was:

    Code:
    OnOpen: 
    
    Private Sub Report_Open(Cancel As Integer)
    DoCmd. SetWarnings False
    DoCmd. ApplyFilter, [Forms!]!{frmRunQueries]![txtFilter]
    DoCmd. SetWarnings True
    
    End Sub
    Then in the Report's Filter property, I set the value to link to the appended title (from from_AdvanceSearch) and it looks like:

    Code:
    Filter: =[Forms]![frmRunQueries]![txtFilter]
    Filter On Load: Yes
    Once again, thanks for all of your assistance!

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    glad you got it working

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Provide all of the actual code from the sub or function you tried (not a free typed approximation)
    @Boost: Looks like you typed out your code in your last post. Sorry if I'm wrong, but there's no way that what I just read could work.
    So the suggestion isn't a rule, but it's a good suggestion that will save us from calling out syntax errors that you may not really have.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-21-2018, 08:49 AM
  2. Modular Criteria
    By neo651 in forum Queries
    Replies: 1
    Last Post: 08-22-2016, 05:08 PM
  3. Replies: 5
    Last Post: 08-25-2014, 05:33 PM
  4. Replies: 5
    Last Post: 08-02-2012, 08:49 AM
  5. Help on Generating a Report!
    By ETCallHome in forum Reports
    Replies: 10
    Last Post: 06-22-2011, 01:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums