Query: Textbox and UserDefined Function problems

  • Thread starter Thread starter Greg (codepug
  • Start date Start date
G

Greg (codepug

I want to use a 'user defined function' in a form query. The only
time it seems to recognize
the function is when it is placed in a module. Also, how can I use
the value in my forms
textboxes as part of my query.

Thanks Greg
 
If you need to refer to the value in Text0 on Form1, use an expression like
this in your query:
[Forms].[Form1].[Text0]
If the form is open, it will read the value from the text box. If not (or if
the name doesn't match exactly), it will pop up a parameter dialog asking
you to enter the value when you run the query.

The expression is longer if the text box is on a subform. Details in:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

To create a user-defined function in VBA, the code must be in a module. JET
SQL (the query langauge in Access) can call VBA functions, but does not have
its own use-defined functions.
 
Thanks Allen

I appreciate the advice. I guess I will have to reconsider my userdef
Functions, since they are not in a module and they reference textboxes
in my form to a great degree.

Greg
 
One thing you might consider is putting the functions in a standard module
and passing the values from the form into them as their arguments. To take a
very simple example a function to calculate a gross price from a net price +
tax where the tax rate is a fractional value (e.g. 0.15 for 15%) in a control
on a form and the net price is a currency value in another control, so the
function would be like this:

Function GrossPrice(curNetprice as Currency, dblTaxRate as Double) As Currency

GrossPrice = curNetPrice * (1 + dblTaxRate)

End Function

You can then call the function in a query, or anywhere else for that matter
with:

GrossPrice(Forms!MyForm!NetPrice, Forms!MyForm!Taxrate)

Though more usually in a case like this the net price and tax rate would be
columns in the query, in which case they'd be passed into the function as its
arguments rather than referencing the open form. As you see the functions
can be far more generic in this way than when in a form's module, so can be
used in a variety of contexts in the database.

Ken Sheridan
Stafford, England
 
Back
Top