Setting the Record source of the sub form to a stored proc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have accomplished this several times, but something must be different
about the latest two cases. And it is intermitent at best.

I have a main form that opens. On the on_load, ( I have tried on load, on
open, on current, and on activate) I set the record source of the sub form to
a stored proc with a paratmeter. ei

Me!frmOrderDetail.Form.RecordSource = "Exec spOrderDtlTempSelect '" &
strTempTable & "'"

The variable is populated. I get the following message:
"You entered an expression that has an invalid reference to the property
Form/Report."

I swore this was working this morning. Is there some "watch outs" that
should be followed when doing this? I found another case today that while is
returning a different message, is failing about 35% of the time.

Any thoughts?
Steve
 
Create a pass-through query that invokes the Stored Procedure, and use the
pass-through query as the recordset.

Since you're trying to change the parameter being passed to the Stored
Procedure, you'll have to reset the SQL of the stored query:

Dim qdfCurr As DAO.QueryDef

Set qdfCurr = CurrentDb().QueryDefs("MyPassthroughQuery")
qdfCurr = "Exec spOrderDtlTempSelect '" & strTempTable & "'"

Me!frmOrderDetail.Form.RecordSource = "MyPassthroughQuery"
 
Doug,
Thanks for the response. I don't quite understand. I am not familiar with
pass-through queries.

You Dim'd a variable qdfCurr, then set it to
CurrentDb().QueryDefs("MyPassthroughQuery")

then set it again to my "Exec spOrderDtlTempSelect '" & strTempTable & "'"

I can't get that to compile. Do need to create MyPassthroughQuery somewhere
else?

I'll keep trying to get it to compile

Steve
 
Well, the code I gave did assume that you'd created a pass-through query
(named MyPassthroughQuery, but you can certainly change that!)

However, I did make a typo. I left out the SQL property:

qdfCurr.SQL = "Exec spOrderDtlTempSelect '" & strTempTable & "'"

If you can't get it to compile, odds are you're using Access 2000 or 2002,
and you haven't set a reference to DAO. In the VB Editor, select Tools |
References from the menu bar, scroll through the list of available
references until you find Microsoft DAO 3.6 Object Library, put a checkmark
beside it and exit the dialog.
 
Back
Top