Results 1 to 10 of 10

Onclick over report field, open another report

  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107

    Onclick over report field, open another report

    Hello, I need help building a code for the following.



    I created a report (Items to Orders per Vendor) that is telling me what items per vendor I need to order within a set of days that I enter.

    I would like to be able to click on the vendor name of that report and open the ordering report associated with that particular vendor. Also I would like the mouse cursor to change to a hand or arrow on mouse over.

    I know how to do OpenReport on click for a single report but I don't know how to associate the value "VendorID" of each vendor with its specific report since multiple vendors are showing on my report.

    Also, if there is no report associated with the vendor yet, an error message should prompt such as "There is no report associated with this vendor" with an OK button, otherwise it might break and give some other funky error.

    If possible and not too complex, ideally the vendorID value of the report would change color once clicked on and stay with the new color until refresh so I know I opened that vendor's report.

    Thank you

  2. #2
    Bob Fitz is online now Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,099
    Since you are trying to use this report as one might use a form, why not use a form to display the information on the screen. It should be possible to achieve your requirements with a form.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Yes, this is another way that I'm working on as well but I would like both. I'll have a selection form which will allow me to select a vendor and open its associated report. So I can just print the report telling me which vendor I need to look and select each report accordingly from the form but it's a longer work process than showing the list of vendors on the screen and directly clicking on each line to open their reports.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,303
    don't see why it isn't doable. Report controls have the events you should need and it is possible to get field data from the current/active record. The part I'm not sure of is the difficulty in swapping cursor reliably between field ans section(s).
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by Micron View Post
    don't see why it isn't doable. Report controls have the events you should need and it is possible to get field data from the current/active record. The part I'm not sure of is the difficulty in swapping cursor reliably between field ans section(s).
    I'm sure it's doable, at least calling the reports but I'm beginning with VB so this is out of my league at the moment. I just got back into working with Access after 17 years.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,303
    If I didn't have a cast on I could offer more than comments but I'm afraid in depth coding is out right now.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,232
    Report in PrintPreview not interactive. Must be ReportView.

    I really don't see advantage of report over form for this process.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Wayne is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Toronto, Canada
    Posts
    6
    I agree with June7. A form is a better idea displaying all relevant vendor data. Put a series of command buttons to open the various reports you require for each vendor, or a single command button to open a menu of reports available to print.

    In the OnClick Event of the Command Button, put this code:
    Code:
    DoCmd.OpenReport "YourReportName", acViewPreview, "", "[VendorName]=[Forms]![YourFormName]![VendorName]"    
    DoCmd.SelectObject acReport, "YourReportName"
    Or if you choose the menu option, put this code in the OnClick Event of the Command Button on the form:
    Code:
    DoCmd.OpenForm "YourMenuFormName", acNormal, , , , acWindowNormal    
    DoCmd.SelectObject acForm, "YourMenuFormName""
    Of course you would have to create the print menu form. I would use option buttons labelled with each report type to print (call it Frame0), and 2 Command buttons labelled "Print" and "Cancel".

    In the OnClick event of the Print Button on the menu form, put this code:
    Code:
    Private Sub btnPrint_Click()
    
        On Error GoTo Err_btnPrint_Click
        
        'To open the selected report in Print Preview
        
        Select Case Me.Frame0.Value
        
        Case 1 'If the user selects Invoice
        
        DoCmd.OpenReport "rptInvoice", acViewPreview, "", "[VendorName]=[Forms]![YourVendorFormName]![VendorName]"
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        DoCmd.SelectObject acReport, "rptInvoice"
        
        Case 2 'If the user selects Order report 
        
        DoCmd.OpenReport "rptOrder", acViewPreview, "", "[VendorName]=[Forms]![YourVendorFormName]![VendorName]"
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        DoCmd.SelectObject acReport, "rptOrder"
        
        Case 3 'If the user selects Something Else
    
        DoCmd.OpenReport "rptSomethingElse", acViewPreview, "", "[VendorName]=[Forms]![YourVendorFormName]![VendorName]"
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        DoCmd.SelectObject acReport, "rptSomethingElse"
        
        Case 4 'And so on....
        
        Put Code Here....
    
    
        End Select
        
    Exit_btnPrint_Click:
        Exit Sub
        
    Err_btnPrint_Click:
        MsgBox Err.Description, vbInformation, "Error"
        Resume Exit_btnPrint_Click
           
    End Sub
    You would use as many case statements as you have option buttons on the print menu form. Just make sure you include the WHERE clause in the OpenReport statement (shown in red). That way it will only print the report for the vendor you have the main form open to.

    In the OnClick event of the Cancel Button on the print menu form, put this code:
    Code:
    Private Sub Cancel_Click()
    
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        
    End Sub
    Hope this helps.

    Wayne

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    Quote Originally Posted by Wayne View Post
    I agree with June7. A form is a better idea displaying all relevant vendor data. Put a series of command buttons to open the various reports you require for each vendor, or a single command button to open a menu of reports available to print.

    In the OnClick Event of the Command Button, put this code:
    Code:
    DoCmd.OpenReport "YourReportName", acViewPreview, "", "[VendorName]=[Forms]![YourFormName]![VendorName]"    
    DoCmd.SelectObject acReport, "YourReportName"
    Or if you choose the menu option, put this code in the OnClick Event of the Command Button on the form:
    Code:
    DoCmd.OpenForm "YourMenuFormName", acNormal, , , , acWindowNormal    
    DoCmd.SelectObject acForm, "YourMenuFormName""
    Of course you would have to create the print menu form. I would use option buttons labelled with each report type to print (call it Frame0), and 2 Command buttons labelled "Print" and "Cancel".

    In the OnClick event of the Print Button on the menu form, put this code:
    Code:
    Private Sub btnPrint_Click()
    
        On Error GoTo Err_btnPrint_Click
        
        'To open the selected report in Print Preview
        
        Select Case Me.Frame0.Value
        
        Case 1 'If the user selects Invoice
        
        DoCmd.OpenReport "rptInvoice", acViewPreview, "", "[VendorName]=[Forms]![YourVendorFormName]![VendorName]"
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        DoCmd.SelectObject acReport, "rptInvoice"
        
        Case 2 'If the user selects Order report 
        
        DoCmd.OpenReport "rptOrder", acViewPreview, "", "[VendorName]=[Forms]![YourVendorFormName]![VendorName]"
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        DoCmd.SelectObject acReport, "rptOrder"
        
        Case 3 'If the user selects Something Else
    
        DoCmd.OpenReport "rptSomethingElse", acViewPreview, "", "[VendorName]=[Forms]![YourVendorFormName]![VendorName]"
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        DoCmd.SelectObject acReport, "rptSomethingElse"
        
        Case 4 'And so on....
        
        Put Code Here....
    
    
        End Select
        
    Exit_btnPrint_Click:
        Exit Sub
        
    Err_btnPrint_Click:
        MsgBox Err.Description, vbInformation, "Error"
        Resume Exit_btnPrint_Click
           
    End Sub
    You would use as many case statements as you have option buttons on the print menu form. Just make sure you include the WHERE clause in the OpenReport statement (shown in red). That way it will only print the report for the vendor you have the main form open to.

    In the OnClick event of the Cancel Button on the print menu form, put this code:
    Code:
    Private Sub Cancel_Click()
    
        DoCmd.Close acForm, "frmPrintMenu", acSaveNo
        
    End Sub
    Hope this helps.

    Wayne
    A series of buttons on a form is a lot easier to build and is my plan B. At the moment I'm working on a cascading combo box selection form.

  10. #10
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    107
    I found the answer here, very simple code

    https://bytes.com/topic/access/answe...ow-click-event

    Mine is even simpler and works as following, not over a report but a list box:

    Code:
    Private Sub lstReports_DblClick(Cancel As Integer)
    
    Dim varID
    
    
    varID = Me.lstReports.Column(0)
     
     
    'Open reports base on its name stored in varID
    DoCmd.OpenReport "" & varID & "", acViewReport
    
    
    'Reset mouse focus outside the List Box so we can use the mouse wheel again to scroll up and down the form
    cboPurchaseCat.SetFocus
    
    
    End Sub

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

Similar Threads

  1. Replies: 6
    Last Post: 03-28-2018, 11:20 PM
  2. Report won't open on OnClick from Search Form
    By ScottySchultz in forum Access
    Replies: 2
    Last Post: 01-30-2018, 07:42 PM
  3. Replies: 9
    Last Post: 07-03-2017, 11:24 PM
  4. Replies: 4
    Last Post: 12-09-2015, 09:02 AM
  5. Replies: 3
    Last Post: 03-11-2015, 12:36 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
  •  
Tech Forums: Microsoft Office Forums