?
Solved

VBA Access 2010 work Access 2016 parameter fail

Posted on 2016-11-02
14
Medium Priority
?
86 Views
Last Modified: 2016-11-04
Former Access 2010 code worked for years
Build table and export separate spreadsheets
stepping through debug

fieldvalue = Cstr(rs("Full Facility Name"))         works
strexportquery = "Select * from MRA_52_LOCAL_Master where (((MRA_52_Local_Master.[Full Facility Name])=""" & fieldvalue & """))" works
set qdfnew = currentdb.createquerydef("myexportquerydef",strexportquery)   ......  ["created table"] works
dodmd.trasferspreadsheet acexport fails

popup window is looking for the parameter  [Full Facility Name]


FULL CODE
Function List_Entries2()

Dim fieldValue As String
    fieldValue = ""
Dim strExportQuery As String
    strExportQuery = ""
Dim curDirectory As String
    curDirectory = CurrentProject.Path
       
On Error GoTo Create_Error

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MRA_52_LOCAL_MASTER_2.[Full Facility Name]FROM MRA_52_LOCAL_MASTER_2 GROUP BY MRA_52_LOCAL_MASTER_2.[Full Facility Name]")
   
    If Not (rs.EOF And rs.BOF) Then
       
        rs.MoveFirst
       
        Do Until rs.EOF = True
            fieldValue = CStr(rs("Full Facility Name"))
           
            'USE QUERY RESULTS IN SECOND QUERY AND EXPORT
   strExportQuery = "Select * FROM MRA_52_LOCAL_MASTER_2 WHERE (((MRA_52_LOCAL_MASTER.[Full Facility Name])=""" & fieldValue & """))"
           
            Set qdfNew = CurrentDb.CreateQueryDef("myExportQueryDef", strExportQuery)
           
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myExportQueryDef", curDirectory & "\LBG - " & CStr(fieldValue) & ".xls", True
           
            CurrentDb.QueryDefs.Delete qdfNew.Name
                       
            rs.MoveNext
        Loop
       
    Else
        MsgBox ("There are no records")
    End If

List_Entries_Exit:
    Exit Function

Create_Error:
        MsgBox Error$
    Resume List_Entries_Exit
   
End Function
0
Comment
Question by:avgplusguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41871007
try first deleting the excel file if exists before the export

If dir(curDirectory & "\LBG - " & CStr(fieldValue) & ".xls") <> "" then
   kill curDirectory & "\LBG - " & CStr(fieldValue) & ".xls"
end if
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myExportQueryDef", curDirectory & "\LBG - " & CStr(fieldValue) & ".xls", True
0
 

Author Comment

by:avgplusguy
ID: 41871054
Rey,
Excellent suggestion.
Once the created myexportquerydef table was a problem and had to be deleted before re-running the code. The excel files are not there and are not a problem.

Before the query exported 12 facility spreadsheets without a problem.
Now it does not recognize the first supplied "Full Facility Name" in the table
and shows a popup error

Enter Parameter Value
MRA_52_Local_Master.Full Facility Name
0
 

Author Comment

by:avgplusguy
ID: 41871056
When you cancel out of it , it says reserved error
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:avgplusguy
ID: 41871145
I manually supplied the 12 facilities and it output the 12 files.
However, Each file has all 12 facilities instead of only the named facility
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41871160
<However, Each file has all 12 facilities instead of only the named facility>

check your query criteria.

btw, you don't need to delete the query everytime you run the export.
you can just replace the SQL  statement of the query using the querydef

dim qd as dao.querydef
set qd=curentdb.querydefs("myExportQueryDef")

Dim rs As DAO.Recordset
     Set rs = CurrentDb.OpenRecordset("SELECT MRA_52_LOCAL_MASTER_2.[Full Facility Name]FROM MRA_52_LOCAL_MASTER_2 GROUP BY MRA_52_LOCAL_MASTER_2.[Full Facility Name]")
     
     If Not (rs.EOF And rs.BOF) Then
         
         rs.MoveFirst
         
         Do Until rs.EOF = True
             fieldValue = CStr(rs("Full Facility Name"))
             
             'USE QUERY RESULTS IN SECOND QUERY AND EXPORT
    strExportQuery = "Select * FROM MRA_52_LOCAL_MASTER_2 WHERE (((MRA_52_LOCAL_MASTER.[Full Facility Name])=""" & fieldValue & """))"

         'change the sql statement of the query
 
      qd.sql=strExportQuery

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myExportQueryDef", curDirectory & "\LBG - " & CStr(fieldValue) & ".xls", True
             
                       
             rs.MoveNext
         Loop
       
     Else
         MsgBox ("There are no records")
     End If
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41872138
If you can, upload a sample database, and point where to look for the problem.
0
 

Author Comment

by:avgplusguy
ID: 41872234
thank you unfortunately meetings most of the day so cant get to this now
0
 

Author Comment

by:avgplusguy
ID: 41872857
Brief Break
I like the logic of the simplification but that is producing errors of its own.
I think my main error is in the query criteria.
I want to group by the field "full facility name" and write out 12 separate spreadsheets with each spreadsheet containing just its customers.
It appears to me that the fieldvalue is not passing the values to the [full facility name] parameter
in 2016 the way it did in 2010


Function List_Entries2()

Dim fieldValue As String
    fieldValue = ""
Dim strExportQuery As String
    strExportQuery = ""
Dim curDirectory As String
    curDirectory = CurrentProject.Path
       
On Error GoTo Create_Error
   
    Dim qd As DAO.QueryDef
    Set qd = curentdb.QueryDefs("myExportQueryDef")

Error message Object Required.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41872885
change this
  Set qd = curentdb.QueryDefs("myExportQueryDef")

    curentdb should be currentdb  (double r)
with

  Set qd = currentdb.QueryDefs("myExportQueryDef")

----

also, use this
    strExportQuery = "Select * FROM MRA_52_LOCAL_MASTER_2 WHERE MRA_52_LOCAL_MASTER.[Full Facility Name]='" & fieldValue & "'"
0
 

Author Comment

by:avgplusguy
ID: 41873008
getting closer
Item not found in this collection at same place now.,,,

I changed the field name from .[Full Facility Name] to .Full_Facility_Name to see if this would help prevent the message from popping up and asking for a specific facility, but it still pops up
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41873031
upload  a copy of the db.
0
 

Author Comment

by:avgplusguy
ID: 41874116
I had to get rid of identifying data, change values, dates, etc
Sample.accdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 41874355
here is the revised db, you can use both function
Sample_revised.accdb
0
 

Author Closing Comment

by:avgplusguy
ID: 41874388
This was great. I knew I would need a little more help because my challenges are usually more that fix this line....
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question