Question about a default table or method

[Joe Cilinceon]

What is the best way to have defaults in one location but available to the
whole application. Some examples would be Sales Tax rate, Late Fee charge in
addition to things like scanners/printers or other things used with in the
program.

 

[Tom]

You can set a default value for a field in the bottom part of the table
design view and this default value carries throughout the database
everywhere the field is used. You can also set the default value for a field
on a form and this default value will only be applied on that form. If you
set the default value in a table and want to use a different default value
on a particular form, set the default value also on the form and it will
override the table default value. The table default value will be applied on
all other forms.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

 

[Joe Cilinceon]

You can set a default value for a field in the bottom part of the
table design view and this default value carries throughout the
database everywhere the field is used. You can also set the default
value for a field on a form and this default value will only be
applied on that form. If you set the default value in a table and
want to use a different default value on a particular form, set the
default value also on the form and it will override the table default
value. The table default value will be applied on all other forms.

Thanks for responding Tom but I not quite what I was asking.

I'm looking for a method that will set a var thru a whole program. Now these
var are only used when calculating totals. A couple of examples would be
sales tax that is now 6% but tomorrow could change to 6.5% or a set fee
charge. At the moment I would have to go thru about 20 queries to find and
change these types of things. I would much prefer to either change it in a
table and load on startup or perhaps a module. I'm really not sure the best
way and that is what I'm asking here. Thanks again

 

[PC Datasheet]

Joe,

Look at TableDefs and Fields collection in the Help file. You need code that
gets to something like this:
Db.TableDefs("MyTable")!Fields("SalesTax").DefaultValue = .065

This might not be totally the correct syntax but it gives you the idea of
what to look for in the Help file.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

 

[Joe Cilinceon]

Thanks again I will check it out.

Joe,

Look at TableDefs and Fields collection in the Help file. You need
code that gets to something like this:
Db.TableDefs("MyTable")!Fields("SalesTax").DefaultValue = .065

This might not be totally the correct syntax but it gives you the
idea of what to look for in the Help file.

 

[Duane Hookom]

This is data. Data is stored in tables. Use functions and/or code to define
default values in text boxes on forms.

 

[John Marshall, MVP]

Take a look at "Understanding the Lifetime of Variables" in the VBA help to
get an understanding of how to set up a variable that will last for a
session.

As Duane mentioned, data should be kept in tables. Try and limit the use of
global variables to the smallest scope possible and load them from a table.
Remember, the data in the table could change and what is in the variable
will be obsolete.

John... Visio MVP

 

[PC Datasheet]

Go back to Visio, you don't know what you are talking about (just like all
the posts you have made!!) Joe asked about default values not global
variables.

 

[Joe Cilinceon]

Thanks that I kind of knew.

This is data. Data is stored in tables. Use functions and/or code to
define default values in text boxes on forms.

 

[Joe Cilinceon]

Thanks John

Take a look at "Understanding the Lifetime of Variables" in the VBA
help to get an understanding of how to set up a variable that will
last for a session.

As Duane mentioned, data should be kept in tables. Try and limit the
use of global variables to the smallest scope possible and load them
from a table. Remember, the data in the table could change and what
is in the variable will be obsolete.

John... Visio MVP

 

[Joe Cilinceon]

Don't worry about it. I've kind of had a problems since the beginning
getting my questions accross. I either give too much info or not enough.

 

[tina]

Joe, are you describing certain values that you use throughout your
database, but that don't really belong in a specific table? such as one, and
only one, current Sales Tax rate - with no need to store *prior* rate
values.

if this is what you're talking about, then read on for the solution i use:
i sometimes find myself with a few "miscellaneous" values that are used in
various places in my database but don't really "belong" in any of my data
tables. when that happens, i create a miscellaneous table, with *only one
record* (if you find that you need multiple records of one value, then
that's a legitimate table that should be built as such), and a separate
field for each value that i need to store. the table is not linked to any
other table, and the fields in the table usually don't have anything to do
with each other.

having built and populated tblMisc, you can retrieve a particular value
wherever you need it in your database, with a simple DLookup() function -
remember that the table has only one record in it, so all you need to do is
look up the field holding the value you're after, as

DLookup("ThisField", "tblMisc")

hth

 

[Vincent Johns]

Tina's "miscellaneous" Table has worked well for me, too. This is one
case in which having dozens of unrelated fields in a record makes a lot
of sense. I do try to give the fields suggestive names (not "Value1",
"Value2", etc.) and of course I add comments (in Table Design View) to
provide some explanation of what the datum means. (Those comments are
not just for someone else's benefit -- it also helps me, six months or
more later.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

tina wrote:

[...]

 

[tina]

This is one

case in which having dozens of unrelated fields in a record makes a lot
of sense.

well, i don't know about *dozens* of fields in a tblMisc, LOL. i think i've
had maybe five or six fields in a single miscellaneous table, tops.
sometimes a field to hold some arbitrary date that has some significance to
part or all of the database, maybe another field for the current user - just
so i don't have to keep looking it up on the system, maybe the name of the
company (that the db belongs to) for use in report headers - so it can be
changed easily in one place. that's about it - just a few odd little bits of
data that don't belong anywhere, but shouldn't be hard-coded because they
will/may need to be changed frequently/occasionally.

Tina's "miscellaneous" Table has worked well for me, too. This is one
case in which having dozens of unrelated fields in a record makes a lot
of sense. I do try to give the fields suggestive names (not "Value1",
"Value2", etc.) and of course I add comments (in Table Design View) to
provide some explanation of what the datum means. (Those comments are
not just for someone else's benefit -- it also helps me, six months or
more later.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

tina wrote:

[...]

if this is what you're talking about, then read on for the solution i use:
i sometimes find myself with a few "miscellaneous" values that are used in
various places in my database but don't really "belong" in any of my data
tables. when that happens, i create a miscellaneous table, with *only one
record* (if you find that you need multiple records of one value, then
that's a legitimate table that should be built as such), and a separate
field for each value that i need to store. the table is not linked to any
other table, and the fields in the table usually don't have anything to do
with each other.

having built and populated tblMisc, you can retrieve a particular value
wherever you need it in your database, with a simple DLookup() function -
remember that the table has only one record in it, so all you need to do is
look up the field holding the value you're after, as

DLookup("ThisField", "tblMisc")

hth

 

[Allen Browne]

Joe, you've already got some good answers. Here's a couple more ideas.

Application and User settings
=====================
A disadvantage of creating a Field in a miscellaneous table for each default
is that as the application grows you have to modify the table every time you
want to add a new default. While that's doable, it's messy when you have
multiple users at different locations. It might be better to create a more
generic table with fields such as these:
TheVariable Text The name, such as "SalesTaxRate".
TheUser Text Zero-length string if it applies to all
users.
TheValue Text The current value to use for this variable
DataType Long A value that is a member of vbVarType
Descrip Text Description of what this variable is used
for.

Advantages:
- Add new variables without redesigning the table.
- Store application-level preferences.
- Store user user-level preferences (e.g. suppress this warning for this
user), adding new users whenever needed.
- Have the user's preferences available from any machine they log into, and
keep them when the front end is updated (because they are not kept in the
workstation's front end.)

Disadvantages:
All values are stored as text. Use VarType(), IsNumeric(), and IsDate() to
validate that the entry is suitable, but it still requires a bit of
manipulating. For example, percentages need to be stored as fractions, e.g.
0.1 for 10%, and dates need to be converted to double so the regional
settings of one user are not interpreted differently by another.

Suggestions:
- Use a 2-field primary key: TheVariable + TheUser.
- Set these properties for the TheUser field:
AllowZeroLength Yes
Default Value: ""
As part of the primary key, neither field can be null, but TheUser defaults
to a zero-length string which represents an application-wide preference.

Object-level settings
===============
Sometimes you want the software to remember a setting per object, such as
which one of a workstation's printers should be used as the default for
printing a label report. You might want to consider creating a custom
property on the report itself.

Advantage: The property stays with the report even if it is copied or
renamed.

Disadvantage: the setting is lost when yor replace the front end with an
update.

You can download an example of this approach for Access 2002 or 2003:
Printer Selection Utility
at:
http://allenbrowne.com/AppPrintMgt.html

HTH.

 

[StopThisAdvertising]

"Tom" <[email protected]> schreef in bericht
Who is Tom ?? Again an alias for *too* stupid questions Steve ??

Arno R

 

[StopThisAdvertising]

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

Is Tom doing homework now ??

Arno R

 

[peregenem]

It might be better to create a more
generic table with fields such as these:
TheVariable Text The name, such as "SalesTaxRate".
TheUser Text Zero-length string if it applies to all
users.
TheValue Text The current value to use for this variable
DataType Long A value that is a member of vbVarType
Descrip Text Description of what this variable is used

Sounds a bit too close to the "One True Lookup Table" design flaw to
me:

http://www.dbazine.com/ofinterest/oi-articles/celko22

 

[Joe Cilinceon]

Thank you tina and that is exactly what I was asking. I was looking for
methods of doing this efficiently.

 

[Joe Cilinceon]

Thanks Vincent I going to give it a try. It seems pretty simple to
implement.

Tina's "miscellaneous" Table has worked well for me, too. This is one
case in which having dozens of unrelated fields in a record makes a
lot of sense. I do try to give the fields suggestive names (not
"Value1", "Value2", etc.) and of course I add comments (in Table
Design View) to provide some explanation of what the datum means. (Those
comments are not just for someone else's benefit -- it also
helps me, six months or more later.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

tina wrote:

[...]

if this is what you're talking about, then read on for the solution
i use: i sometimes find myself with a few "miscellaneous" values
that are used in various places in my database but don't really
"belong" in any of my data tables. when that happens, i create a
miscellaneous table, with *only one record* (if you find that you
need multiple records of one value, then that's a legitimate table
that should be built as such), and a separate field for each value
that i need to store. the table is not linked to any other table,
and the fields in the table usually don't have anything to do with
each other. having built and populated tblMisc, you can retrieve a
particular
value wherever you need it in your database, with a simple DLookup()
function - remember that the table has only one record in it, so all
you need to do is look up the field holding the value you're after,
as DLookup("ThisField", "tblMisc")

hth