ODBC Pass-through query with parameter from table

[Brett]

Hello,

I'm not experienced at all with VB programming, so I am not sure how
to handle this (using VB or other method):

I would like to have a table record used as the parameter for a stored
procedure called in a pass-through query via an ODBC connection.

Pass-through "QueryName" has the following SQL: sp_report
JobEstimatesVsActualsDetail show Text, Label, AmountEstCost,
AmountActualCost, AmountDifferenceCost, AmountEstRevenue,
AmountActualRevenue, AmountDifferenceRevenue parameters DateMacro =
'All', EntityFilterFullNameWithChildren = 'CustomerName',
SummarizeColumnsBy = 'TotalOnly'

and the following ODBC Connect Str property: ODBC;DSN=QuickBooks
Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for
QuickBooks\Optimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N

Thus, I would like to insert or pass (don't know the terminology) a
customer name from my table Customers to the parameter
EntityFilterFullNameWithChildren = 'x' where "x" is CustomerName from
the Customers table.

To simply this, let's start with the assumption that there is only one
name in the Customers table since I will probably pass that name from
a form selection from a list a customers (CustomersList table). I'm
trying to keep this example simple to establish the pass-through and
get the data into Access before I move on to the forms and reports if
that makes any sense.

Thanks!

 

[Duane Hookom]

You can use a little DAO code to update the SQL property of your p-t query.
Assuming code in a form with a control lboCustomerName:

Dim strSQL as String
strSQL = "sp_report JobEstimatesVsActualsDetail show Text, Label, " & _
"AmountEstCost, AmountActualCost, AmountDifferenceCost, " & _
"AmountEstRevenue, AmountActualRevenue, AmountDifferenceRevenue " & _
"parameters DateMacro = 'All', EntityFilterFullNameWithChildren = '" & _
Me.lboCustomerName & "',SummarizeColumnsBy = 'TotalOnly'"
CurrentDb.QueryDefs("QueryName").SQL = strSQL

 

[Brett Barry: Go Get Geek!]

Due to my inexperience with forms, if you could help me out with
"Assuming code in a form with a control lboCustomerName:":

I have a form called "Select Customer" with Customer Name_Label and a
List Box called CustomerName with Control Source blank (don't know
what if anything should go here), Row Source "SELECT [Customers].
[FullName] FROM Customers; " and on Event tab for On Dbl Click there
is the [Event Procedure] with the following code:

Private Sub CustomerName_Click()

Dim strSQL As String
strSQL = "sp_report JobEstimatesVsActualsDetail show Text, Label, " &
_
"AmountEstCost, AmountActualCost, AmountDifferenceCost, " & _
"AmountEstRevenue, AmountActualRevenue, AmountDifferenceRevenue "
& _
"parameters DateMacro = 'All', EntityFilterFullNameWithChildren =
'" & _
Me.lboCustomerName & "',SummarizeColumnsBy = 'TotalOnly'"
CurrentDb.QueryDefs("JobEstvsActuals").SQL = strSQL

End Sub

When I double-click on the customer in the list box, I am getting the
error "Compile error: Invalid outside procedure" and I think it is
because I don't know what to put for "Me.lboCustomerName" in the Event
Procedure. The pass-through query is called "JobEstvsActuals".

 

[Duane Hookom]

Have you attempted to compile the code?

Your list box name is CustomerName, not lboCustomerName so try:

Dim strSQL As String
strSQL = "sp_report JobEstimatesVsActualsDetail show Text, Label, " & _
"AmountEstCost, AmountActualCost, AmountDifferenceCost, " & _
"AmountEstRevenue, AmountActualRevenue, AmountDifferenceRevenue " & _
"parameters DateMacro = 'All', EntityFilterFullNameWithChildren ='" & _
Me.CustomerName & "',SummarizeColumnsBy = 'TotalOnly'"
Debug.Print strSQL
CurrentDb.QueryDefs("JobEstvsActuals").SQL = strSQL
End Sub

You can then check the SQL by pressing Ctrl+G or opening your pass-through
in sql view.

 

[Brett Barry: Go Get Geek!]

That worked! Now, I understand that the "Me" part is necessary.

Thank you very much (even thought this may be simple for most
folks)...you have saved me a lot of time, money, and frustration.

 

[Duane Hookom]

It's not necesarily simple for most. Glad to hear you got it working.