Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17

    Question OpenRecordset with Query error

    I'm trying to get this VBA to work with a query instead of a table.


    Its works when I pull the field data from the table but I get results I don't want.
    My report works and gives the results I want based on the query but the VBA is pulling from a table before the results are filtered through the query.

    Is there a way to make the VBA work using the query?

    Additionally, is there a way to make the path not static so that the database could be used on other computers?

    Click image for larger version. 

Name:	OpenRecordSet Query.JPG 
Views:	43 
Size:	54.9 KB 
ID:	44001

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,558
    Your post title implies there is an error message??
    Answer to your second question is yes - use a file dialog file or folder picker. Or use a relative path (e.g. you can base the location relative to the db itself if there will be some consistency across the board).

    To your first question: https://docs.microsoft.com/en-us/off...set-method-dao

    However, I can't see why it would make a difference which you use if both are properly designed. If you get the correct results from a query, it should make no difference if you put that query into vba and run it as a sql statement.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,926
    what is the error?

    what does this mean?
    its works when I pull the field data from the table but I get results I don't want.
    is there a way to make the path not static so that the database could be used on other computers?
    in principle yes - but depends on what alternative paths you want. If it just to change 'Keith' to 'Sam' then use environ("username")

    For the future, please copy paste your code rather than using an image. Also recommend you get in the habit of indenting your code as it makes it much easier to read - particularly when others are looking at it

  4. #4
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17
    Thank you both for your replies. Sorry about not pasting the code itself. Not sure what happened but it works now...maybe I didn't close the window or something to refresh the form it is part of.

    I'm not a programmer, found the code online and was able to adapt to my use.

    Not sure what you mean about setting the environment. Ultimately what I would like is for a box to pop up asking where the user would like to store the reports.

    Code pasted:
    Private Sub ExportReports_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String


    mypath = "C:\Users\Keith\Desktop"

    Set db = CurrentDb()

    Set rs = db.OpenRecordset("SELECT ManagerName From qry_STF_Report_Manager_Detail", dbOpenDynaset)

    Do While Not rs.EOF

    temp = rs("ManagerName")
    MyFileName = rs("ManagerName") & ".PDF"

    DoCmd.OpenReport "STFMVS1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
    DoCmd.Close acReport, "STFMVS1 User Level Access Report"

    rs.MoveNext
    Loop

    Set rs = Nothing
    Set db = Nothing
    End Sub

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,558
    Ideally, the request would be to paste your code between code tags (# on forum toolbar). Hope you will agree that this is much easier to read:
    Code:
    Private Sub ExportReports_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String, mypath As String, temp As String
    
    mypath = "C:\Users\Keith\Desktop"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT ManagerName From qry_STF_Report_Manager_Detail", dbOpenDynaset)
    
    Do While Not rs.EOF
       temp = rs("ManagerName")
       MyFileName = temp & ".PDF" '<<if you're going to assign a value to a variable (temp), why not use it?
    
       DoCmd.OpenReport "STFMVS1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'"
       DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
       DoCmd.Close acReport, "STFMVS1 User Level Access Report"
    
       rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set db = Nothing
    End Sub
    Ultimately what I would like is for a box to pop up asking where the user would like to store the reports.
    My suggestion remains the same then. msofiledialogfolderpicker (aka filedialogfolderpicker)

    Would be enough to simply have
    MyFileName = rs("ManagerName") & ".pdf" . I'm saying the temp variable isn't really needed at all.
    Last edited by Micron; 01-22-2021 at 07:50 PM. Reason: added info
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17
    Quote Originally Posted by Micron View Post
    Ideally, the request would be to paste your code between code tags (# on forum toolbar). Hope you will agree that this is much easier to read:

    My suggestion remains the same then. msofiledialogfolderpicker (aka filedialogfolderpicker)

    Would be enough to simply have
    MyFileName = rs("ManagerName") & ".pdf" . I'm saying the temp variable isn't really needed at all.
    Yes much easier to read, thank you for the suggestion.

    I also removed the temp variable and adjusted the code.

    I will look in to filedialogfolderpicker option.

  7. #7
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17
    I have added some select folder code. I am getting a Compile Error, "Expected End With" Could someone provide some direction please?

    I had to add the temp variable back in also. While the code would run my reports were empty.

    Code:
    Private Sub ExportReports_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String
    Dim sFolder As String
        
        ' Open the select folder prompt
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Choose your save location"
            .ButtonName = "Select"
            If .Show = -1 Then ' if OK is pressed
                sFolder = .SelectedItems(1)
            End If
    
    Set db = CurrentDb()
    
    Set rs = db.OpenRecordset("SELECT ManagerName From qry_STF_Report_Manager_Detail", dbOpenDynaset)
    
    Do While Not rs.EOF
        temp = rs("ManagerName")
        MyFileName = "STF User Level Access " & rs("ManagerName") & ".PDF"
    
        DoCmd.OpenReport "STFMVS1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFolder & MyFileName
        DoCmd.Close acReport, "STFMVS1 User Level Access Report"
    
        rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set db = Nothing
    End Sub

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,558
    It's exactly what the message says. You started a With block but didn't End it. Pseudo code:

    Code:
    With Something
      .This
      .That
    End With
    Can get similar messages if you don't properly group blocks. This will cause an error because of the misplaced ending statements.

    Code:
    With
      .statements
       If something Then 
          do stuff
    End With
       End If
    A good reason to use proper indentation too - it helps to catch such errors.

    Suggest you step through your code, making a selection and also a cancel (no selection) and see what happens.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17
    I added the "end with" statement, no more compile error. The dialog window pops up, allows me to choose a folder. However, its still saving the reports to my desktop, not the folder I chose.

    Code:
    Private Sub ExportReports_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim sFolder As String
        
        ' Open the select folder prompt
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Choose your save location"
            .ButtonName = "Select"
            If .Show = -1 Then ' if OK is pressed
                sFolder = .SelectedItems(1)
            End If
        End With
    
    Set db = CurrentDb()
    
    Set rs = db.OpenRecordset("SELECT ManagerName From qry_STF_Report_Manager_Detail", dbOpenDynaset)
    
    Do While Not rs.EOF
        temp = rs("ManagerName")
        MyFileName = "STF User Level Access " & rs("ManagerName") & ".PDF"
    
        DoCmd.OpenReport "STFMVS1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFolder & MyFileName
        DoCmd.Close acReport, "STFMVS1 User Level Access Report"
    
        rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set db = Nothing
    End Sub

  10. #10
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    621
    You will need to add a \ to sFolder. before you can concatenate MyFileName ?
    See if that fixes it, but you should start inspecting variables, not assume they hold correct values?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  11. #11
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17
    Ok, got my code to work....for the most part... This works, until I try to add acHidden

    Code:
    Private Sub ExportReports_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim sFolder As String
        
        ' Open the select folder prompt
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Choose your save location"
            .ButtonName = "OK"
            If .Show = -1 Then ' if OK is pressed assign folder name to sFolder variable
                sFolder = .SelectedItems(1)
            Else
                Exit Sub
            End If
        End With
    
    Set db = CurrentDb()
    
    Set rs = db.OpenRecordset("SELECT ManagerName From qry_STF_Report_Manager_Detail", dbOpenDynaset)
    
    Do While Not rs.EOF
        temp = rs("ManagerName")
        MyFileName = "STF User Level Access " & rs("ManagerName") & ".PDF"
        
        DoCmd.OpenReport "STFMVS1 User Level Access Report", acViewPreview, , "[ManagerName]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFolder & "\" & MyFileName
        DoCmd.Close acReport, "STFMVS1 User Level Access Report"
    
        rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set db = Nothing
    End Sub
    The moment I add acHidden here
    Code:
    DoCmd.OpenReport "STFMVS1 User Level Access Report", acViewPreview, , "[ManagerName]='" & temp & "'", acHidden
    I get Run-time error 2487 The object Type argument for the action or method is blank or invalid, it highlights this line
    Code:
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, sFolder & "\" & MyFileName
    Works fine until I add the acHidden option...any ideas?

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,558
    You cannot pass an empty string ("") as a report name. If you want to pass nothing to an optional argument, you leave the value of an argument blank, you don't pass "" to it (as a rule) as you did here
    acViewPreview, ,

    where you left the filter argument blank.

    https://docs.microsoft.com/en-us/off...docmd.outputto
    https://docs.microsoft.com/en-us/off...cmd.openreport
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  13. #13
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    17
    Well so much for that....tried to edit the code some and now when the editor opens it just freezes and beeps at me...can't even edit it anymore now.

    Good thing for backups.

    Really stuck on this. I have read over those documents you linked multiple times, even before you linked them as they come up in my searches all the time. I don't understand them. Wish they had relevant examples for the various options. For someone who isn't a programmer those documents are about as useful as a submarine with screen doors.

    I've tried a few different options and several of them worked, until I add the acHidden function. Is there somewhere you can link with better explanations and examples for a VBA noob?

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,558
    I know of no other info to direct you to. It seems basic to me (not trying to be condescending here) so I don't know what else to say.
    Can you post a zipped copy of your db? You should compact/repair it first but given the current situation I wonder what the result of that attempt would be. If it has sensitive info, you could manually run update queries, or eliminate it, or try this

    If you do post, make sure we know what to do to replicate the issue.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  15. #15
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    621

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

Similar Threads

  1. Error with OpenRecordset
    By snsmith in forum Modules
    Replies: 15
    Last Post: 06-04-2019, 01:43 PM
  2. Replies: 3
    Last Post: 02-26-2016, 12:34 PM
  3. dbSeeChanges does not fix openrecordset error
    By lringstad in forum Access
    Replies: 1
    Last Post: 12-03-2013, 03:33 PM
  4. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  5. db.OpenRecordset error
    By jscriptor09 in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 12:17 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 - Senior Forums