UDF in RecordSource: How to promp for parameters?

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

Hi,

I am trying to use parametrized UDFs in a form's RecordSource. My
problem: I want the user to be prompted for parameter input.

Background: I have a bunch of UDFs - some parametrized, some not -
that can be selected by the user in an Access form. When selected, the
UDF is used as a filter against the existing RecordSource of a second
form. That's just a simple line of VBA (strSQL being the original
RecordSource of the form):

MyForm.RecordSource = strSQL & " WHERE Id In (SELECT Id FROM " & MyUDF
& ")"

This works for non-parametrized UDFs. My problem with parametrized
UDFs is:

- I don't know whether the selected UDF accepts parameters or not
- I don't know how many parameters are accepted or what their names
are
- I need the user to be prompted for parameters, if necessary

Prompting the user for parameters doesn't work with the code above, of
course. It works fine when opening the UDF by using
DoCmd.OpenFunction, but: this returns the UDF's results in a table
view - and I need to re-use these results in order to change my form's
record source. Can anyone give me a hint on how to do this?
 
Christine

You've described "how". Please describe "what", as in "what are you trying
to accomplish?"
 
Jeff,
You've described "how". Please describe "what", as in "what are you trying
to accomplish?"
Click to expand...

Sorry, I thought that was clear: I want the user to be prompted for
parameter input, when using a parametrized UDF as a RecordSource.

There is no prompt when I use an UDF in the RecourdSource property as stated
in my first post.

There is the kind of prompt I'm looking for when I open the UDF using
DoCmd.OpenFunction (or double clicking in the queries window) - but in that
case the result of the UDF is returned immediately in a table view I can't
use in a query.

Btw, I forgot to tell: I'm using Access 2003 and MS SQL Server 2000 SP3.

Thanks for your help,
Christine
 
Christine

My intent with my earlier response was to get a description, without any
technical terminology, of what a user would do. Your description still
contains technical jargon that many users would not be able to relate to.
Try explaining what you want to have happen in terms your 80 year old mother
would understand (assuming she isn't/wasn't Grace Murray Hopper <g>!).

You've described "using a parameterized UDF as a RecordSource", but you're
in a "queries" newsgroup. Can you draw a connection between these for me?
When you talk about using DoCmd.OpenFunction, then talk about the queries
window, I'm just not following.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Got this info from SQL'r 2000 Books On Line:

You can find out info about UDF's by using the built-in SP "sp_help."
Syntax: sp_help <function name>. It will return a recordset of:
Parameter_name, Type, Length, Prec(ision), Scale, Param_order.

A call to a parameterized UDF would be: udf_Cubic(ht, ln, wd).

To pass the params to the UDF my guess would be:

strUDF = MyUDF & "(" & strParam1 & "," & strParam2 & ")"

strSQL = strSQL & " WHERE id In (SELECT id FROM " & strUDF & ")"


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEZDPYechKqOuFEgEQI7dgCfUTCNz3N+Z2xeKOCkq5c6L9LPXaIAoPcX
XbUcMpS+7ghC9mYsS7Vl3YNa
=wDTT
-----END PGP SIGNATURE-----
 
Jeff,
My intent with my earlier response was to get a description, without any
technical terminology, of what a user would do.
[...]

You've described "using a parameterized UDF as a RecordSource", but you're
in a "queries" newsgroup. Can you draw a connection between these for me?
Click to expand...

Sorry for disturbing - guess I'm wrong here. I hadn't realized there was
also an access.adp.sqlserver newsgroup and figured, this group would be most
adequate for my question (after all, a User-defined Function is a kind of
query).

I should try over there in the adp group since my problem relates both to
SQL Server and to Access/VBA.

Thanks for your help,
Christine
 
Got this info from SQL'r 2000 Books On Line:

You can find out info about UDF's by using the built-in SP "sp_help."
Syntax: sp_help <function name>. It will return a recordset of:
Parameter_name, Type, Length, Prec(ision), Scale, Param_order.
Click to expand...

Thanks! This isn't exactly what I was looking for (lazy bum as I am, I hoped
there was a way to have access find out whether a function requires
parameters, and then prompt the user with some kind of "enter value" form
automatically if necessary). But i can work with this information - I'll
have to query for parameters in my UDF, and then find a way to have the user
enter values if necessary. So, at least I know I'm not trying to achieve
something impossible :-)

Thanks a lot,
Christine
 
Back
Top