Results 1 to 10 of 10
  1. #1
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19

    Question Using Query field results within a VBA event procedure?

    I'm trying to write a script in VBA to use with an On Click event within my report. My report "Record Source" is QryRptShopFloorFollower_MainForm, in the Page Header I have an image that I wanted to have start a print job when clicked, but need to pull information that is gained from the Query for this. My image name is Auto_Logo0 and has an On Click event procedure. In the VBA script, I have something similar to:


    CreateObject("Shell.Application").Namespace(0).Par seName("documentpathhere.pdf").InvokeVerb ("Print")

    I plan on trying to loop through and print multiple documents through VBA when the image is clicked, but at the moment I'm just trying to get a single document path to transfer from the query in to vba so I can use it. I've tried so many different options and always get 1 of about 3 different errors depending on what I've tried.

    When the report is loaded, there is a prompt for "MO Number" which is what is used to populate the Query. Taking all of this a step further, I think I would like to pass that "MO Number" prompt to VBA and run a separate sql query from vba to produce my list of documents to print.

    Any help on getting this to work would be really awesome, I have a decent history in programming/reporting/etc but am new to access and vba and just trying to learn as I go basically.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    just let the user dbl-click obj,

    this opens the pdf in its native app (adobe or other ) using the code below. call it via: OpennativeApp txtbox
    (where txtbox has the path to the document)

    then let the user print from adobe.


    The code below will open any document given , in its native app. (xls opens in excel, .doc in word, etc)
    place this code into a module:
    Code:
    Attribute VB_Name = "modNativeApp"
    Option Compare Database
    Option Explicit
    #If VBA7 Then
      'Declare PtrSafe Sub...
        Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
        Declare PtrSafe Function GetDesktopWindow Lib "user32" () As Long
    #Else
       'Private Declare ptrsafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
       'Private Declare ptrsafe Function GetDesktopWindow Lib "user32" () As Long
    #End If
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, msg As String
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                msg = "File not found"
            Case SE_ERR_PNF
                msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                msg = "Access denied"
            Case SE_ERR_OOM
                msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                msg = "DLL not found"
            Case SE_ERR_SHARE
                msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                msg = "DDE busy"
            Case SE_ERR_NOASSOC
                msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                msg = "Invalid EXE file or error in EXE image"
            Case Else
                msg = "Unknown error"
        End Select
    '    MsgBox msg
    End If
    End Sub
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function

  3. #3
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    Your response kind of confused me, but I think you're missing what I am trying to do. I do not know what the path to the pdf is until I pull the path from the query results. Also, there could be anywhere from 1 up to 100 documents getting printed, depending on the information entered in to the first prompt and the results pulled back from the query. I definitely don't want to have to print these documents individually and want to let the software do it all automatically. The single line of code that I posted will work for printing automatically just like I want, it opens the pdf in reader, prints and closes reader. This is perfect, but I've only gotten it to work if I manually put a document path in the code, I have no been able to successfully pull in a path from the query at all.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you need to rework your process if I understand you correctly. If the report has multiple pages and you want to save each one individually with a different name (.pdf) you are going towards a dead end. What you want is to create a recordset (using your criteria entered at the prompt) that gives you the unique ids of the entities returned by your query (i.e. PartID) then loop through this and save the updated report that only has individual parts (limited by the ID from the loop) as PDF (easiest is to use Docmd.OutputTo).
    What I do is use a global variable that I update inside the loop to hold the current unique id; I have a custom function that returns that global variable and I use this function in the criteria for the report's recordsource query:
    Code:
    'I have this in a standard module
    Public lCurrent_PartID_Record As Long
    
    Public Function vcCurrent_PartID_Record()
    vcCurrent_PartID_Record= lCurrent_PartID_Record
    End Function

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    Nope, maybe I am explaining it poorly. The pdf's that I want to print have really nothing to do with the report, other than they are the drawing files that match to what MO is input in to the first prompt. The pdf's are already created and stored in a specific location on the server before the report is ran. The report is a manufacturing order report, showing a list of parts that need to be created, sometimes it's broken all the way down to the base components, sometimes it'll break down to sub-assemblies and not base components, which is why I may want to run my own sql query inside vba to better control what documents I grab.

    When you run the report, it asks for "MO Number?". I need to be able to pass that information in to a vba so that I can use it to run a new sql query separately from everything else going on, when the image I chose is clicked. The end of my "main" sql query is : WHERE (((dbo_MIMOH.mohId)=[MO NUMBER?]));
    I would like access to [MO NUMBER?] in vba, and be able to write and run an sql query in that vba code when the image is clicked. Then I will loop through my results from the query and use a print command to print all of the drawing files that I return from my query.

    Hope this helps make it a little more clear.


    PS: or! I can create a query in access, use [MO NUMBER?] like the current query I'm using, write the sql to grab the information I want and use the outputs in vba? I just don't know how to get any of this information to pass in to the vba coding correctly.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can add a text box on your report bound to mohId (=[Mo Number] and reference that in the OnClick as Me.txtMohID (or whatever you name the text box), make it hidden (Visible =False) if you don't want it to show.
    Or a better approach would be to prompt for the [MO NUMBER?] in a different place, like the code used to open your report. You could use a "reporting" form to gather your parameters and the nice thing about that is that you continue to have access to them for further processing like in your case.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    Okay, so I've tried to do something similar though I used docPath as my "control source" for the textbox. I changed it to mohId, and I am just simply trying to get the value of mohId to show in a "MsgBox" temporarily to make sure I can get the field information to actually show up. If I do:

    Private Sub Auto_Logo0_Click()


    MsgBox "Me.tb_docPath"


    End Sub

    When I click the image, it literally pops a message box up that says "Me.tb_docpath". I'm assuming there is a syntax I am missing or something to be able to use this information, but I am unsure of the vba portion as I have tried many many different ways and can never get it to display the actually information from the text box.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If the textbox is named txtMohID and its control source is mohId (the field from the underlying query) then to display the actual id you simply use:
    Code:
    Private Sub Auto_Logo0_Click()
    'MsgBox "Me.tb_docPath"
    MsgBox Me.txtMohID  'notice you leave the double quotes out otherwise you get the string enclosed in them rather than its actual value
    
    End Sub
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    aebstract is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    19
    Awesome, I have no idea what I've been doing wrong honestly because I thought I've tried it without the quotation marks inside the print command I showed earlier. Got the MsgBox to work AND the print command to work. This solves the issue that made me create this post, now moving forward I just need to figure out how to run an sql query from within the vba coding and loop through the results to print off all of the correct documents that correspond to the current report that isn't being viewed. Thanks a lot for the help!

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to help, if you get stuck post back and I'm sure you'll get some help
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 09-27-2019, 10:58 AM
  2. Query runs before On Open Event Procedure
    By shuddle in forum Access
    Replies: 3
    Last Post: 12-21-2016, 01:36 PM
  3. Replies: 1
    Last Post: 03-29-2014, 07:46 PM
  4. Access 2010 Query or Event Procedure Issue?
    By justphilip2003 in forum Queries
    Replies: 22
    Last Post: 04-19-2013, 02:39 PM
  5. Replies: 3
    Last Post: 05-07-2012, 12:17 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