storing a variable

[david]

I want to give the user the ability to change a setting.

Example Discount rate = .4

value stored in record 1 tblSettings.Discount

the user will be able to change the value through a form that only
allows modifying record 1.

How do I pass this value to a module and then refer to it in queries,
forms and reports.

David

[Rich P]

In the form where the user is updating the value of Discount, you can
use the form as the container for the value. It sounds like you want to
use this value as a parameter in your queries, forms, reports. You can
refer to the form in your queries as follows:

In a query design view, in the field which you want to filter with this
value/parameter, goto the criteria row of that field and type something
like this:

Forms!yourForm!Text1

yourForm is the form where the user is updating the value (the form name
- it could be frmDiscount and you would write

Forms!frmDiscoutn!Text1

)
Text1 is the control where the user is entering/updating the value.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

"david" <le******@sympatico.ca> wrote in message
news:ff************************@posting.google.com ...

I want to give the user the ability to change a setting.

Example Discount rate = .4

value stored in record 1 tblSettings.Discount

the user will be able to change the value through a form that only
allows modifying record 1.

How do I pass this value to a module and then refer to it in queries,
forms and reports.

David

It sounds like you want to store this one single variable for your whole
application. Although I can't think of a time when I've needed a _discount_
setting across the whole application, I often have similar variables like
MyCompany, MyAddress, MyPhone, etc so these settings can be printed out on
reports.
I do this by having a table tblDbInfo with one row and use
var=DLookup("[MyCompany]","tblDbInfo","[ID]=1") to extract this information.

Variations on this basic theme involve writing a public function to do the
lookup, or setting a global variable at application startup (this approach
needs some caution)

Hope this gives you an idea

Fletcher

[Tom]

For queries, just add TblSettings to your query, do not join TblSettings to
anything and pull down the discount field into the query grid. TblSettings by
not being joined to anything will form a cartesian product with the rest of your
query but since there is only one record in TblSettings you end up with the same
number of records as what the rest of your query would return if TblSettings was
not included in the query.

To display the discount rate directly on a form or report, add a field and put
this expression in the Control Source property of the textbox:

=DLookup("[Discount]","TblSettings")

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com

"david" <le******@sympatico.ca> wrote in message
news:ff************************@posting.google.com ...

I want to give the user the ability to change a setting.

Example Discount rate = .4

value stored in record 1 tblSettings.Discount

the user will be able to change the value through a form that only
allows modifying record 1.

How do I pass this value to a module and then refer to it in queries,
forms and reports.

David

[David Berniker]

I want to give the user the ability to change a setting.
Example Discount rate = .4
value stored in record 1 tblSettings.Discount Variations on this basic theme involve writing a public >function to do

the lookup, or setting a global variable at >application startup (this
approach needs some caution)

Thanks for your help

How do I create a function or global variable based on the value of a
record in a table.

David
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

"David Berniker" <le******@sympatico.ca> wrote in message
news:3f***********************@news.frii.net...

I want to give the user the ability to change a setting.
Example Discount rate = .4
value stored in record 1 tblSettings.Discount

Variations on this basic theme involve writing a public >function to do

the lookup, or setting a global variable at >application startup (this
approach needs some caution)

Thanks for your help

How do I create a function or global variable based on the value of a
record in a table.

David

Create a new module and write a function like the following:

Public Function GlobalDiscount() As Single
GlobalDiscount = CSng(Nz(DLookup("[Discount]", "tblSettings", "[ID]=1"),
0))
End Function

To define a global variable just put this in your new module:
Public g_sngDiscount As Single
But since you would have to set it at application startup and make sure it
was updated as necessary, in this case you would probably be better off
getting live data by calling the function each time - ie forget about a
global variable.
Fletcher