Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16

    Unhappy Finding a report by client ID


    Hi.

    I've looked online and cant find the answer to this simple problem!

    I have a report set up already, but what I want to be able to do is, when I open the report, I get a filter box (or similar) that asks me for a client ID (a unique number for every client), that once entered would bring up the report(s) for that client only.

    I have done it before, but for the life of me cant remember how!

    Please help/signpost to YouTube

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You filter in the form before opening the report

    If ClientID is a text field:
    Code:
    DoCmd.OpenReport "MyReportName", acViewPreview, , "ClientID = '" & Me.txtClientID & "'"
    If it's a number field
    Code:
    DoCmd.OpenReport "MyReportName", acViewPreview, , "ClientID = " & Me.txtClientID
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    Quote Originally Posted by ridders52 View Post
    You filter in the form before opening the report

    If ClientID is a text field:
    Code:
    DoCmd.OpenReport "MyReportName", acViewPreview, , "ClientID = '" & Me.txtClientID & "'"
    If it's a number field
    Code:
    DoCmd.OpenReport "MyReportName", acViewPreview, , "ClientID = " & Me.txtClientID
    thanks for the reply.

    the cleint ID is a Number data type....

    so, where do i put all that "stuff" that you quoted above?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Add it to a button click event on your form

    Code:
    Private Sub MyButtonName_Click()
        DoCmd.OpenReport "MyReportName", acViewPreview, , "ClientID = " & Me.txtClientID
    End Sub
    Replace with actual names from your database
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    thanks, ill give it a go.

    One thing that's still doing my tree in however, is the fact that last time I built a database (many years ago - that I don't have access to anymore), i definitely had a little window that popped up when I opened a report that allowed me to filter the report by Clint ID.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's a parameter input box - another way of doing it
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    Hi all.

    Coming back to this to get it to work.

    In the post above, ridders52 stated that, from what I can tell, I could create a button on my form that will open the results for that cliwnt only.

    I tried to do such, right clicked the button and went on "build event" from the pop-up menu, but then wasn't sure what details to add where.

    Do I copy and past "DoCmd.OpenReport "Report1", acViewPreview, , "ClientID = " & Me.txtClientID" into the "Where condition = " line?

    for added help, my two linked tables are called "Assessment details" and "Client details"

    My linked field (unique number) is "Payroll_Number"

    My form is "Client details" (this has a assessment details sub-form in it)

    And my report is currently called "Assessment report".

    From the above code from ridders52, what do I rpelace, and where?!)

    Any help or advice welcomed.

    As you can tell, I am a bit of a newbie at all this, sorry :-(

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could post a copy of your database so readers can review your forms, report etc?

  9. #9
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    it says my DB is too bit :-(

    it's only 1.5mb

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Best to do a compact and repair; then create a Zip file containing your database.

  11. #11
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16

    Red face

    like this?

    (ps thanks )
    Attached Files Attached Files

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Burnsie,

    You should not name different objects with same name.

    Be explicit, name a table tbl.... or T....
    For Forms, frm.... or F.....

    It is confusing for readers/maintainers to have a table and a form named "client details".
    I would also recommend you do NOT have field or object names with embedded spaces.

    I do not use macros. I modified your button click event procedure as follows:
    Code:
    Private Sub Command40_Click()
    'made this a vba procedure
    DoCmd.OpenReport "Assessment Report", acViewPreview, , "Payroll_number = " & Me.Payroll_Number
    End Sub
    This will print a single report for the "employee/person" being displayed on your Client details form.

    Suggest you rename things

    tblClientDetails
    frmClientDetails

    Good luck.

  13. #13
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    Quote Originally Posted by orange View Post
    Burnsie,

    You should not name different objects with same name.

    Be explicit, name a table tbl.... or T....
    For Forms, frm.... or F.....

    It is confusing for readers/maintainers to have a table and a form named "client details".
    I would also recommend you do NOT have field or object names with embedded spaces.

    I do not use macros. I modified your button click event procedure as follows:
    Code:
    Private Sub Command40_Click()
    'made this a vba procedure
    DoCmd.OpenReport "Assessment Report", acViewPreview, , "Payroll_number = " & Me.Payroll_Number
    End Sub
    This will print a single report for the "employee/person" being displayed on your Client details form.

    Suggest you rename things

    tblClientDetails
    frmClientDetails

    Good luck.
    Thanks for the help and advice orange.

    I will follow advice re table names.

    You state how you've updated my event procedure.... fancy uploading it? ;-)

    B

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I showed the code in the post. If you go to design view of the form, highlight the button, and look at the properties
    this is the code to put in for the click event.
    Code:
    Private Sub Command40_Click()
    'made this a vba procedure
    DoCmd.OpenReport "Assessment Report", acViewPreview, , "Payroll_number = " & Me.Payroll_Number
    End Sub

  15. #15
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    Quote Originally Posted by orange View Post
    I showed the code in the post. If you go to design view of the form, highlight the button, and look at the properties
    this is the code to put in for the click event.
    Code:
    Private Sub Command40_Click()
    'made this a vba procedure
    DoCmd.OpenReport "Assessment Report", acViewPreview, , "Payroll_number = " & Me.Payroll_Number
    End Sub
    Hi orange

    thanks for the continued help.

    I have amended the code as detailed (changing in my new names too).

    However, although I now get a parameter box appear that asks me to enter a number (Payroll_number) when I click the button, yet whatever I input, it always takes me to record no.1 and I have to scroll through the others to get the one I want (ie "123456").

    any ideas?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Getting a specified Client Name on Report
    By Khalid Tanweer in forum Reports
    Replies: 3
    Last Post: 08-28-2017, 06:56 PM
  2. Replies: 3
    Last Post: 11-25-2014, 01:14 PM
  3. Replies: 3
    Last Post: 10-30-2012, 12:28 PM
  4. Missing client details report
    By crxftw in forum Reports
    Replies: 3
    Last Post: 08-16-2011, 12:19 PM
  5. Replies: 1
    Last Post: 07-26-2011, 06:10 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