Prompt for user input to be used in a Stored Procedure

  • Thread starter Thread starter Brett
  • Start date Start date
B

Brett

I am using the following SQL for the following stored procedure:

sp_report APAgingDetail show Text, Blank, TxnType, Date, RefNumber,
Name, DueDate, Aging, OpenBalance parameters DateTo = {d'yyyy-mm-
dd'}, AgingAsOf = 'ReportEndDate'

I want to be able to prompt the user for the date entry 'yyyy-mm-dd'.

I have seen other posts on this, but I am confused. I am using Access
2007 and this is pulling data from QuickBooks via the QODBC driver. Is
there a simple way to this? I don't have much VBA experience.

Thanks!
 
I generally modify the SQL property of the pass-through query with some DAO
code. Assuming you have a form with a text box "txtToDate" that I need in the
pass-through query. I would have code in some event of the form (maybe the
after update of txtToDate):

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qsptYourNameHere")
qd.SQL = "EXEC sp_reportAPAging '" & me.txtToDate & "' "
Set qd = Nothing
Set db = Nothing

This assumes you have set a reference to the DAO object library.
 
Well, I don't have a form with a text box. I usually just put in a
user prompt directly into the query. And since the pass-through is
based on an ODBC connection to a specific driver, how can I test that
this will work? Any more suggestions?
 
Brett said:
Well, I don't have a form with a text box. I usually just put in a
user prompt directly into the query. And since the pass-through is
based on an ODBC connection to a specific driver, how can I test that
this will work? Any more suggestions?
Click to expand...

Access parameter prompting will not work with passthrough queries.
Remember, the sql in a passthrough query is not "looked at" by Access:
it is "passed through" as-is to the target database.

You will need to modify the querydef's SQL property via vba to
accomplish this.

If you don't care about performance, you could enter a date in the
passthrough query that will guarantee that all the records are returned.
Then, create a new query, using the saved passthrough query as the data
source, in which you prompt the user for a date to be used to filter the
results returned from the passthrough query:

select ...
from savedpassthrough
where fieldname = [Enter Date]

I do not recommend this solution. I only offer it for completeness.
 
I agree with Bob. IMO, users should never be subjected to parameter prompts.
Always use controls on forms for user interface.
--
Duane Hookom
Microsoft Access MVP


Bob Barrows said:
Brett said:
Well, I don't have a form with a text box. I usually just put in a
user prompt directly into the query. And since the pass-through is
based on an ODBC connection to a specific driver, how can I test that
this will work? Any more suggestions?
Click to expand...

Access parameter prompting will not work with passthrough queries.
Remember, the sql in a passthrough query is not "looked at" by Access:
it is "passed through" as-is to the target database.

You will need to modify the querydef's SQL property via vba to
accomplish this.

If you don't care about performance, you could enter a date in the
passthrough query that will guarantee that all the records are returned.
Then, create a new query, using the saved passthrough query as the data
source, in which you prompt the user for a date to be used to filter the
results returned from the passthrough query:

select ...
from savedpassthrough
where fieldname = [Enter Date]

I do not recommend this solution. I only offer it for completeness.
Click to expand...
 
Back
Top