Results 1 to 12 of 12
  1. #1
    redekopp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2015
    Location
    Saskatoon
    Posts
    108

    Opening a monthly report from Client form


    Good morning. I have a client form called "frmClientEntry" that I am creating a button on called "btnMonthlyReport" . I have created a report called "rptEquipClaimForm" . The query and report work fine, except that it lists all clients. What I want is a way to be able to easily select which user I am looking to get their monthly report for. I need to create a select user field, which I thought if I was already on their Client form then I could just use their autonumber clientid and pass criteria. but then ill also need to choose the date range for this report.....

    Any idea a good way to go about this? I am completely stumped over here.
    I was trying this as a starter point to see if I could even get to the report with every transaction date but this code wont work:
    Code:
    On Error GoTo Err_btnOpenFile_Click
        Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "rptEquipClaimForm"
        
        stLinkCriteria = "[clientDBID]=" & "'" & Me![clientDBID] & "'"
        DoCmd.OpenReport stDocName, acViewReport, , stLinkCriteria, acDialog
        
        
    Exit_btnOpenFile_Click:
        Exit Sub
    Err_btnOpenFile_Click:
        MsgBox Err.Description
        Resume Exit_btnOpenFile_Click
    I get type mismatch, I think its because it was copied from a text field not a number, but I don't know the proper fix for the stLinkCriteria.

    Any help would be great, thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If your field is numeric:


    stLinkCriteria = "[clientDBID]=" & Me![clientDBID]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    redekopp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2015
    Location
    Saskatoon
    Posts
    108
    Quote Originally Posted by pbaldy View Post
    If your field is numeric:


    stLinkCriteria = "[clientDBID]=" & Me![clientDBID]
    Thanks, any idea how I can go about transferring that AND a date range through?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does this help?

    BaldyWeb wherecondition

    Multiple criteria would be separated by AND or OR as appropriate to your desired logic.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    redekopp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2015
    Location
    Saskatoon
    Posts
    108
    that could work, im still getting a type mismatch error with
    Code:
     stLinkCriteria = "[clientDBID]=" & Me![clientDBID]
    the clientDBID is an autonumber in tblclients and just a number in tblClaimEntry . they are both part of a qry for the report.

    EDIT: OOPS I had It set to integer. it works! except it asks me for the ID before proceeding, how do I make it automatic?!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wouldn't expect a type mismatch, but if both fields are there you may need to disambiguate:

    stLinkCriteria = "TableName.[clientDBID]=" & Me![clientDBID]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    redekopp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2015
    Location
    Saskatoon
    Posts
    108
    yeah so when I go from my form which has clientDBID (from tblClients) and then try to open report it asks me value for tblNHBentry.clientDBID . the whole point of this was to avoid this step and have it automatically send through. I think im missing something?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The prompt is Access telling you it can't find something. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    redekopp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2015
    Location
    Saskatoon
    Posts
    108
    fixed thank you

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The code is fine. The parameter prompt is coming from the report (try opening it directly from the nav pane). The problem is in Group and Sort.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    redekopp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2015
    Location
    Saskatoon
    Posts
    108
    Quote Originally Posted by pbaldy View Post
    The code is fine. The parameter prompt is coming from the report (try opening it directly from the nav pane). The problem is in Group and Sort.
    woohoo, thank you.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 15
    Last Post: 09-01-2015, 12:20 PM
  2. Replies: 1
    Last Post: 03-29-2014, 10:19 AM
  3. Replies: 6
    Last Post: 11-10-2012, 09:49 PM
  4. Replies: 1
    Last Post: 01-10-2012, 03:44 AM
  5. Replies: 2
    Last Post: 07-26-2011, 08:26 AM

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