Getting Paramters into my Query

[Joy]

Hi -

I have made an Instructor Expense form where the user enters number of
Kilometers driven and meal expenses. The form calculates the instructor's
total expenses for several days of reporting. I based it on Order/Order
Details from Northwind database. Note: each line must be editable, which
means the underlying query has to be editable.

It works absolutely perfectly, except for one glitch. My query has to have
parameters in it, i.e., Kilometer Rate (reimbursement / kilometer driven)
and a Tax rate on meals. Meal expenses can be reported as tax included/not
included. (For example, breakfast can be reported as $5.75, tax included,
or $5.00 tax not included.)

I have 2 variables in my program that are read in and can be changed only by
the administrator. They are available to me in VBA. They are called
curKilometerRate and curTaxRate.

So I want the query to run, using these 2 parameters, without asking the
instructor to enter them when he enters his expenses.

Is it possible to pass the values from VBA to a query? Can you give me an
example of how to do this?

Thank you so very much!!! Your help is greatly appreciated.

Joy

 

[Chris2]

Hi -

I have made an Instructor Expense form where the user enters number of
Kilometers driven and meal expenses. The form calculates the instructor's
total expenses for several days of reporting. I based it on Order/Order
Details from Northwind database. Note: each line must be editable, which
means the underlying query has to be editable.

It works absolutely perfectly, except for one glitch. My query has to have
parameters in it, i.e., Kilometer Rate (reimbursement / kilometer driven)
and a Tax rate on meals. Meal expenses can be reported as tax included/not
included. (For example, breakfast can be reported as $5.75, tax included,
or $5.00 tax not included.)

I have 2 variables in my program that are read in and can be changed only by
the administrator. They are available to me in VBA. They are called
curKilometerRate and curTaxRate.

So I want the query to run, using these 2 parameters, without asking the
instructor to enter them when he enters his expenses.

Is it possible to pass the values from VBA to a query? Can you give me an
example of how to do this?

Thank you so very much!!! Your help is greatly appreciated.

Joy

Joy,


VBA Example:

Public Function fGetKilometerRate() As Currency

Dim curKilometerRate As Currency

'Do some code here, like . . .
curKilometerRate = 0.25

fGetKilometerRate = curKilometerRate

End Function



Query Example:

SELECT Y1.*, fGetKilometerRate()
FROM YourTable AS Y1


Sincerely,

Chris O.