Results 1 to 15 of 15
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Filtering query-based report that can be opened from two or more places

    I have a report that until now could only be opened through one form, so the query it is based on used a field on that form as its match criteria to filter.



    Criteria:
    [Forms]![frmRMAR]![RMA_nb]

    Now, I need to be able to open this report from a second location and filter the query it is based on depending on this form as well, with the same field.


    I imagine there should be a simple way to run the underlying query by passing an argument in as the criteria from any form, but I'm not sure how. Any ideas?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I would use a TempVar for the criteria and populate that before opening the report.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Welshgasman View Post
    I would use a TempVar for the criteria and populate that before opening the report.

    I've never used a TempVar before, two quick questions on this after some research if you don't mind me asking:
    Can I put TempVars.RMA_nb as the query criteria?
    Can I overwrite the value of the TempVars each time I open the report from a form, i.e.

    TempVars.RMA_nb = [Forms]![frmRFR].RMA_nb
    DoCmd.OpenReport "rptRFR" ...

    ?

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Yes. However with TempVars, the way I use them, you must use the Value property.

    I am presuming you open the report from a form (now multiple forms)?
    so just
    Code:
    TempVars("RMA_nb").Value = Me.RMA_nb
    DoCmd.OpenReport "rptRFR" ...
    That is how I use Tempvars.

    They also have an .Add/Remove method, if you wish to use them that way.

    For the query criteria it would be Tempvars!EMA_nb

    https://www.devhut.net/2021/05/12/access-tempvars/

    https://docs.microsoft.com/en-us/office/vba/api/access.tempvars.add


    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Welshgasman View Post
    Yes. However with TempVars, the way I use them, you must use the Value property.

    I am presuming you open the report from a form (now multiple forms)?
    so just
    Code:
    TempVars("RMA_nb").Value = Me.RMA_nb
    DoCmd.OpenReport "rptRFR" ...
    That is how I use Tempvars.

    They also have an .Add/Remove method, if you wish to use them that way.

    https://www.devhut.net/2021/05/12/access-tempvars/

    https://docs.microsoft.com/en-us/off...s.tempvars.add
    So the TempVar object needs to be created before I can start overwriting the value. I planned to do this in my AutoExec function, but when I'm developing the DB the autoexec function is skipped because I open the file with shift-click. What do you recommend as a good way to handle initializing TempVar that I can manually call? Alternatively, I could create the variable each time the report is opened and clear it right after. Is this a better method?

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No, if you use it the way I do, the first value set, creates the tempvar with that value. Same assignment with another value updates it. So before you open the report, set the tempvar with a value.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    I am being prompted for TempVars.RMA_nb.Value when opening from my form button. The code on the form button is:

    Code:
    Private Sub cmdRMA_Click()
        TempVars("RMA_nb").Value = Me.[RMA_nb].Value
        DoCmd.OpenReport "rptRFR", acViewReport, , , acWindowNormal
    End Sub
    The query that builds the report has the criteria:

    [TempVars].[RMA_nb].[Value]

    Any idea why this isn't working? Sorry, first time working with these.

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    [TempVars]![RMA_nb].[Value]

    I've never used or seen the value property included on the tempvar itself.
    The .value property is almost always required for the assignment control name on the right of the equals sign.

    Below is how I do it.

    Tempvars!RMA_nb = me.RMA_nb.value



  9. #9
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by davegri View Post
    [TempVars]![RMA_nb].[Value]

    I've never used or seen the value property included on the tempvar itself.
    The .value property is almost always required for the assignment control name on the right of the equals sign.

    Below is how I do it.

    Tempvars!RMA_nb = me.RMA_nb.value


    OK! I believe that was part of it. Another mistake I made was that my query criteria used a . instead of a ! in: [TempVars].[RMA_nb]

    Changed that to [TempVars]![RMA_nb] and it is working!

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Changed that to [TempVars]![RMA_nb] and it is working!
    Yes. Assigning values to tempvars is tricky.

    Tempvars!RMA_nb = me.RMA_nb is incorrect
    You must include the .value because without it, tempvars assumes you want to assign the control (me.RMA_nb) itself, not the control's value. This will result in an error.
    Tempvars!RMA_nb = Me.Rma_nb.value is correct.

    However, you can assign the tempvar to a field value in the form's recordsource without the .value clause. In this case use the square brackets to indicate it as so.
    Tempvars!RMA_nb = [RMA_nb] is correct.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can't argue the point because I have little experience with TempVars but perhaps the syntax is just wrong for TempVars. I say that because .Value is the default property of most controls thus doesn't need to be included in a myriad of other cases. Access does not presume that you are trying to pass the object when using str = Me.txtFName, yes? Perhaps it is a quirk of TempVars, or it's just the required syntax. I would think that Tempvars!RMA_nb = Me!Rma_nb ought to work as the ! would invoke the default property value of the name of what follows it - in other words, Value. Not that I advocate using ! where it isn't required. Hope someone can test the use of !
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Hmm, there is a reason why I found I had to use it, but just tried it now and it works without?
    Code:
    tempvars("tt") = "Paul"
    ? tempvars("tt")
    Paul
    From one of my DBs
    Code:
        TempVars("EmployeeID").Value = Me.cboEmployeeID.Column(0)
        TempVars("Employee").Value = Me.cboEmployeeID.Column(1)
        TempVars("UserLevel").Value = DLookup("DataOrder", "tblLookup", "LookupID = " & Me.cboEmployeeID.Column(3))
    So none the wiser.

    I did say you had to use the ! in the query? Access surrounds the criteria with [ & ], if you do not
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    WGM, I suppose it depends on the tempvar's assignment syntax.
    I never use the format Tempvars.("varname").value = something
    My preference is Tempvars!varname = "Paul"
    ?Tempvars!varname
    Paul

    The cat suffers multiple skinnings again.

  14. #14
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    For the sake of posterity, all of the specific syntax I ended up using is as follows:

    Event on click button on form:

    Code:
    Private Sub cmdRMA_Click()
        TempVars!RMA_nb = Me.RMA_nb.Value
        DoCmd.OpenReport "rptRFR", acViewReport, , , acWindowNormal
    End Sub


    Report's source query criteria:

    Code:
    [TempVars]![RMA_nb]
    And this works perfectly well. To be honest I always forget the significance of the brackets, and tend to use them when in doubt.
    I find it interesting the many ways you can call on the specific TempVar object within TempVars... don't understand the reason for that, but, sure.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I did a little playing. I raised an error (32538) trying to assign the value of a form control via form/control reference and relying on the default property. Don't know if such an error was ever raised by OP or not but it would have helped to know if it was. Access does handle that as if you're trying to pass the object itself. Interesting.

    FWIW and for those who might follow in the future, another approach might be to pass something to the report open args and either set its recordsource or filter based on that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-14-2018, 07:21 PM
  2. Filtering Query Based on Criteria
    By McArthurGDM in forum Queries
    Replies: 1
    Last Post: 11-20-2014, 03:31 PM
  3. Replies: 7
    Last Post: 07-11-2013, 10:45 AM
  4. Replies: 8
    Last Post: 02-07-2013, 09:01 PM
  5. Replies: 13
    Last Post: 09-27-2010, 03:10 PM

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