Solved

Access 2010 selective export

Posted on 2014-12-05
3
158 Views
Last Modified: 2014-12-05
Access 2010

I found code to do what I want but it does not work.  The code is:

[embed=snippet 8267437]Dim nLowKey As Integer
Dim nHighKey As Integer
Dim sKey As String
nLowKey = txtLowKey
nHighKey = txtHighKey
sKey = "HOLDER"
Dim qry As QueryDef
Dim sqlStmt As String
sqlStmt = "SELECT * FROM CARDS WHERE (" & sKey & " = " & nLowKey & ") OR (" & sKey & " = " & nHighKey & ");"
Set qry = CurrentDb().QueryDefs("qryTemp")
qry.SQL = sqlStmt
DoCmd.TransferText acExportDelim, "ExportCards", qry.Name, "D:\IBMCollection\Cards.csv"

Open in new window



It goes fine until the Set qry statement.  Then I get a Run-time 3265 Item not found in this collection

Where is qryTemp defined?  
In the DoCmd where is qry.Name defined?
Dim nLowKey As Integer
Dim nHighKey As Integer
Dim sKey As String
nLowKey = txtLowKey
nHighKey = txtHighKey
sKey = "HOLDER"
Dim qry As QueryDef
Dim sqlStmt As String
sqlStmt = "SELECT * FROM CARDS WHERE (" & sKey & " = " & nLowKey & ") OR (" & sKey & " = " & nHighKey & ");"
Set qry = CurrentDb().QueryDefs("qryTemp")
qry.SQL = sqlStmt
DoCmd.TransferText acExportDelim, "ExportCards", qry.Name, "D:\IBMCollection\Cards.csv"

Open in new window

0
Comment
Question by:IBMJunkman
3 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 250 total points
ID: 40484140
Just create a query named "qryTemp". Put any old SQL statement in it. The SQL within will be replaced by the code you posted above.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40484143
If you create a dummy saved query named qryTemp with "Select 1 as stuff" your cose would then work.  You are overwriting the SQL of an EXISTING query.  If it doesn't exist, well BANG! is the result.
0
 

Author Closing Comment

by:IBMJunkman
ID: 40484146
It worked!  The overwriting was mentioned but the fact it was an existing query escaped me.  Many thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

22 Experts available now in Live!

Get 1:1 Help Now