Solved

VBA Access 2010 work Access 2016 parameter fail

Posted on 2016-11-02
14
28 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sending Attachment via CDO 3 22
Code editor Problem 8 16
TT Fresh Order Sheet Update. 4 13
Error in query 3 0
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

10 Experts available now in Live!

Get 1:1 Help Now