Solved

VBA Access 2010 work Access 2016 parameter fail

Posted on 2016-11-02
14
34 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
  • 8
  • 5
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

by:Rey Obrero
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now