Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55

    How to View and Print only ONE record in a Report

    I have a form with a sub-form. The form contains customer contact information and the sub-form is the work order information. Using a query I created a Report using information from the customer contact table and the work order table. I want to create a button at the bottom of the form/sub-form that will bring up the report on the screen and then can be printed from there. I can't figure out how to get it to only do the one selected record though. I have googled and I find information about entering code and I don't understand it because I am the opposite of a coding expert, lol. If someone can help explain to to me step by step in lamens terms I would appreciate it. If you need anymore information from me just let me know.

    Thanks in advanced.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can open a filtered version of your report using a where condition in the opening arguments.
    The where argument is a string in the same syntax as the criteria in a DLookup or similar so
    Code:
    Dim sWhere as String
    
    sWhere = "[MyIDFieldOnMyReport] = 1234 "
    
    DoCmd.OpenReport "YourReportName" , acViewPreview , , sWhere
    This would open a report filtered to the ID 1234

    You will need to get the current order ID from your sub form , but that isn't to difficult to achieve.

  3. #3
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    OK, So there is not a way to just a have a button to click to open the report and print it of only one record without having to enter the ID? Because I am not sure my boss would like it that way, he wants it as simple and user friendly with the least steps possible...

  4. #4
    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
    What customer/work order specifically do you want to report/show?
    If any record (random) is acceptable, then you could have such done via a button click.

  5. #5
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    The one that is showing on the screen is the one I want to view and print....

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    No you can do that.

    You can retrieve the current subform record ID very simply in code, something like this attached to your buttons click event;


    Code:
    Dim sWhere as String
    Dim iMyID as Long
    
    iMyID = Me!YourSubformControlName.Form!YourOrderIDControlName
    
    sWhere = "[MyIDFieldOnMyReport] = " & iMyID
    
    DoCmd.OpenReport "YourReportName" , acViewPreview , , sWhere
    Change the bits in red to your forms controls and report field names.
    This assumes your order ID is a number and not a text field
    Last edited by Minty; 12-14-2017 at 03:21 AM.

  7. #7
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    So I tried that and I got the run time error saying "Microsoft Access can't find the field 'frmWorkOrderSubform' referred to in your expression.

    My form is called frmCustomers, the subform is called frmWorkOrderSubform, I have them linked by Customer ID and Work Order No which are both autonumber.....The report is called rptWorkOrder.
    I used a query called qryCustomersQuery to create the report.

  8. #8
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you post up your whole button code.
    Also the Control Name of the Work Order Number on the sub form?

    Is the Query qryCustomersQuery only used for this report?
    If so there is a simpler way.
    But You'll learn more by using the code.

  9. #9
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    Private Sub GenerateWorkOrder_Click()
    Dim sWhere As String
    Dim iMyID As Long

    iMyID = Me!frmWorkOrderSubform.Form!WorkOrderNo

    sWhere = "[WorkOrderNo] = " & iMyID

    DoCmd.OpenReport "rptWorkOrder", acViewPreview, , sWhere

    End Sub


    The Control Name is Work Order No

    Yes, the query is only used for this report.

  10. #10
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    I took the spaces out and that might be the problem but it give another error with the spaces in, when I tried to change the control name and take the spaces out it gave me an error in my form...ugghh what a mess....I hope I don't have to start over again....it took me a week to get the form/subform exactly the way I wanted it

  11. #11
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Two answers then
    Code:
    Private Sub GenerateWorkOrder_Click()
    Dim sWhere As String
    Dim iMyID As Long
    
    iMyID = Me!frmWorkOrderSubform.Form![Work Order No]
    
    sWhere = "[WorkOrderNo] = " & iMyID
    
    DoCmd.OpenReport "rptWorkOrder", acViewPreview, , sWhere
    
    End Sub
    Note the change to reflect the name. One of the problems with using spaces in field names is you then have to accommodate them by using square brackets.

    Secondly answer:
    You can put the subforms control reference into your query as a criteria.
    With your form open, open your query and under the Work Order No field in the criteria click the Expression Builder. Locate your form then subform in the list and the select the control.

    Don't do both of these - the code will be more useful long term, the saved filtered query is simpler but less flexible.

  12. #12
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    Ugh...I got the same error : Microsoft Access can't find the field 'frmWorkOrderSubform' referred to in your expression

  13. #13
    balderman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    It says field, but frmWorkOrderSubform is not a field, it's the subform.....IDK....

  14. #14
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Try
    iMyID = Me.frmWorkOrderSubform.Controls("Work Order No").Value

  15. #15
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It says field, but frmWorkOrderSubform is not a field, it's the subform.....IDK....
    That's the issue. You cannot refer to a subform by the form name. You have to use the name of the main form control that contains the subform. Sometimes the two names are the same, but more often they are not.

    Your code should be:

    iMyID = Me!containercontrolname.Form![Work Order No]where containercontrolname is the name of the name of the control (a subform or subreport control) containing the subform, not the name of the subform itself.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-28-2017, 09:51 AM
  2. Print single record in report and view
    By banpreet in forum Reports
    Replies: 3
    Last Post: 01-12-2017, 02:38 PM
  3. Print report on form in view
    By keiath in forum Reports
    Replies: 3
    Last Post: 11-11-2016, 12:32 PM
  4. View/Print PDF attachment in report
    By bnecrush in forum Access
    Replies: 3
    Last Post: 02-15-2016, 11:44 AM
  5. Replies: 2
    Last Post: 05-06-2012, 11: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