INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

query parameters, is it possible to save a value in the query?

query parameters, is it possible to save a value in the query?

(OP)
Hi,

I have a query that uses a parameter, and I want to use that query as the [table] argument in a transferspreadsheet command.

Only I can't seem to work out how I provide the value for the parameter before the spreadsheet transfer is run.

I have...

CODE

Dim sFile As String
    Dim qdQuery As QueryDef
    
    Set qdQuery = CurrentDb.QueryDefs("xlsMyQuery")
    
    ' RCM's can only see their own data
    If oUser.Job = "Regional Compliance Manager" Then
        qdQuery![RCM] = oUser.Name
    Else
        qdQuery![RCM] = "*"
    End If

    sFile = XLS_Export("xlsMyQuery") 

But when the XLS_Export uses the xlsMyQuery as the [table] argument, I am still getting the query parameter value dialog box request.

Is it possible to 'save' parameter values in queries, I can't seem to work it out if it is possible.

I've tried passing the QD object to the transferspreadsheet method, but it didn't like that, I've also tried using a recordset object and passing that to the XLS export, but again invalid data type.

I believe the transferspreadsheet [table] parameter will only accept a string value of the name of either a table or a query.

Looking around one possibility that sprung up is to replace the query parameter with a UDF where I could dynamically provide the value that way, but before I go refactoring, I thought I'd ask you guys if it was possible with a query parameter.

Thanks,
1DMF.





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

RE: query parameters, is it possible to save a value in the query?

(OP)
Hi Majp,

Isn't that what I was doing?

CODE

qdQuery![RCM] 
It's the shorthand syntax for the query parameters collection isn't it.

Just like [Forms]![FormName] - instead of using the forms collection and its methods.

Or am I missing something?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

RE: query parameters, is it possible to save a value in the query?

I would just change the SQL property of the querydef so I wouldn't have to worry about parameters. Build the SQL, apply the SQL to the SQL property of the querydef, and do whatever.

Duane
Hook'D on Access
MS Access MVP

RE: query parameters, is it possible to save a value in the query?

I would imagine 1DMF is using parameters to avoid the risk of SQL injection

RE: query parameters, is it possible to save a value in the query?

(OP)
I see what you're saying Duanne, and I considered that. but it would mean hard coding SQL in the code...so I thought long and hard and came up with the idea of reading the SQL property, and using replace to change it to the value I want.

So in my query I changed the parameter from

CODE

Like [RCM] 
to

CODE

Like "*" 
and then applied your logic...

CODE

If oUser.Job = "Regional Compliance Manager" Then
        
    ' change search criteria
    Set qdQuery = CurrentDb.QueryDefs("xlsMyQuery")
    sSQL = qdQuery.SQL
    qdQuery.SQL = Replace(sSQL, "*", oUser.Name, , , vbTextCompare)
        
    ' run export
    sFile = XLS_Export("xlsMyQuery")
        
    ' reset querydef for next time.
    qdQuery.SQL = sSQL
    qdQuery.Close
        
Else
    sFile = XLS_Export("xlsMyQuery")
End If 

Works perfectly smile, many thanks for the inspiration.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

RE: query parameters, is it possible to save a value in the query?

Quote:

It's the shorthand syntax for the query parameters collection isn't it
Yes you are right, my fault. This is the reason I detest bang notation and default properties. So without knowing off the top of your head that the parameters collection is the default member and not the fields property (like the recordset or tabledef objects), makes debugging difficult. VB did no one any favors by maintaining two notations and default behavior.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close