Access 2010 VBA Parameter Query

[Kurt]

Hello All,

I have the following code that I inherited:

'Import the updated list
    
    'Variable Declaration
    SrcFilePath = ThisWorkbook.Path & "\01_Database.accdb"
        SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report];"
    TgtSheet = "DATA_Clean"
    Tgt1stCell = "iPt_NewData"
    TgtHeaderOffset = 8
    ' Create Parameter Object.
    Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)
    Param1.Value = " "
    Cmd1.Parameters.Append Param1
    Set Param1 = Nothing

I know this code is wrong how do I just press the enter key in the code to be able to use the parameter query report from the Access DB?

 

[ranman256]

No need to build a parameter, if you are building the SQL in this code.
SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report] where [state]='California'"

You only need a parameter if the previously build query has it.
set qdf = db.querydefs("qsReportByState")

so just put it in the SQL.

 

[Kurt]

No need to build a parameter, if you are building the SQL in this code.
SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report] where [state]='California'"

You only need a parameter if the previously build query has it.
set qdf = db.querydefs("qsReportByState")

so just put it in the SQL.

Hello Ranman256,

Thanks for the start and this is what I have been trying I used this query:

SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report] where [FLAG]='SPA'"

I get this error message now:

Run time error '-2147217904 (80040e10)
No value given for one or more required parameters.

Is this looking for another parameter or what am I missing here?

Also this is weird we are using Access 2007 but I think this code should still be the same and we are running Excel 2010.

 

[Kurt]

This is the Open Connection code:

    '[OPEN connection]
    cnn.Open strCnn
    rs.CursorLocation = adUseClient
    rs.Open SrcQuery, cnn, adOpenStatic, adLockReadOnly
    'rs.Open SrcQuery, cnn, adOpenStatic, adLockReadOnly '<== this is where it is breaking

This should be working but it isn't what else should I check for?

 

[Micron]

SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report] where [FLAG]='SPA'"

Does QryFNL_Report have any parameters in it? And don't forget the semicolon: 'SPA';"

 

[Kurt]

SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report] where [FLAG]='SPA'"

Does QryFNL_Report have any parameters in it? And don't forget the semicolon: 'SPA';"

Yes it does and I was trying to figure that part out so I tried to use the Flag column.

The VBA code has other SQL Statements just like this one and it works fine.

What can I try to check out next?

 

[Micron]

That was the point of the question in post #2 "You only need a parameter if the previously build query has it."

If a query you run uses a query that has parameters, you have to supply them. Rather than me post a link that will some day expire, Google "ms access query parameters vba". You will need to create a query def for your "calling" query, define and pass the parameters to it, and it it will automatically pass them to the "called" query.

 

[Kurt]

That was the point of the question in post #2 "You only need a parameter if the previously build query has it."

If a query you run uses a query that has parameters, you have to supply them. Rather than me post a link that will some day expire, Google "ms access query parameters vba". You will need to create a query def for your "calling" query, define and pass the parameters to it, and it it will automatically pass them to the "called" query.

When I open the Access Database it gives me the parameter query CLS_Current.

If I press the Enter key it runs which is the data that I need.

What is the vba code I need to add to press the enter key?

I know there are several ways to code pressing the Enter key but what works best in this scenario?

 

[Micron]

What is the vba code I need to add to press the enter key?

I know there are several ways to code pressing the Enter key but what works best in this scenario?

I have no idea, other than SendKeys, because you've gone from a parameter issue to wanting code to simulate a key press. If the query performs when opened from the db container (navigation pane) but not in code, replicating a key press is not going to help.

 

[Kurt]

Smitty helped me narrow it down it is in the srcFilePath name of the machine that I was using.

However now I get a C:\Users\11111111\Desktop|Disbursements\Disbursements-DebtBalance is not a valid path. Make sure that the path name is spelled correctly and that you are corrected to the server on which the file resides.

I have searched through all the vba modules and I cannot see this particular user name anywhere.

I also looked on the Excel spreadsheets to see if it was embedded anywhere per Smitty's suggestion and I still cannot see it. I have now transferred the files to my work laptop and I still get the same message.

Where the heck could this be embedded? Where do I look next?

Of course 1111111 is a fictitious user name to protect the innocent haha.