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

[1DMF]

Hi,

I have a query that uses a parameter, and I want to use that query as the

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

    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

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

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

 

[MajP]

Look at the parameters collection of the querydef object

https://msdn.microsoft.com/en-us/library/office/ff193967.aspx

 

[1DMF]

Hi Majp,

Isn't that what I was doing?

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

 

[dhookom]

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

 

[strongm]

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

 

[1DMF]

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

Like [RCM]

to

Like "*"

and then applied your logic...

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

 

[MajP]

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.

 

[1DMF]

No worries MajP - it made me re-evaluate the whole thing anyway!

"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