Solved

VBA Access 2010 work Access 2016 parameter fail

Posted on 2016-11-02
14
54 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 30

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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

740 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