A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Question about a default table or method



 
 
Thread Tools Display Modes
  #21  
Old November 14th, 2005, 01:43 PM
John Marshall, MVP
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Steve is upset with me because I keep reminding him that these newsgroups
are for free support. The only reason he tries to answer questions is to
provide an excuse to solicit business. These newsgroups are provided by
Microsoft as a means for users to obtain free support from each other. steve
santos (AKA PCD) assumes that they were provided so that he could solicit
work.

You originally asked for "a variable that will last for a session." Global
variables will do this, but there are caveats.
1) Anything classified as data should be in a table
2) The information may be volatile and the variable may get out of sync with
the table.


John... Visio MVP


"Joe Cilinceon" wrote in message
...
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.

PC Datasheet wrote:
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.


"John Marshall, MVP" wrote in message
...
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" wrote in message
...
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.

--

Joe Cilinceon


--

Joe Cilinceon





  #22  
Old November 14th, 2005, 01:45 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Thanks Allen a touch more than I need since this is a single location
application. I will however look at your approach and do appreciate it.

Allen Browne wrote:
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.


"Joe Cilinceon" wrote in message
...
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.


--

Joe Cilinceon



  #23  
Old November 14th, 2005, 01:46 PM
John Marshall, MVP
external usenet poster
 
Posts: n/a
Default Question about a default table or method

I guess as a failed gas station attendant, you did not excel at reading. Joe
originally asked for "a variable that will last for a session.". Global
variables will do this, but there are caveats as I mentioned..

John... Visio MVP

"PC Datasheet" wrote in message
nk.net...
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.


"John Marshall, MVP" wrote in message
...
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" wrote in message
...
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.

Joe Cilinceon



  #24  
Old November 14th, 2005, 01:46 PM
external usenet poster
 
Posts: n/a
Default Question about a default table or method


Vincent Johns wrote:
"miscellaneous" Table has worked well for me, too.


A single row table of constants is a standard SQL trick which works
well. Of course you have to ensure it never contains more than one row
e.g.

CREATE TABLE Constants (
lock CHAR(1) DEFAULT 'x' NOT NULL PRIMARY KEY,
CHECK (lock = 'x'),
other columns...
);

  #25  
Old November 14th, 2005, 03:57 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Question about a default table or method

John Marshall, MVP wrote:
I guess as a failed gas station attendant, you did not excel at
reading. Joe originally asked for "a variable that will last for a
session.". Global variables will do this, but there are caveats as I
mentioned..
John... Visio MVP


Actually John I asked for the best method to set default values in an
application, be it as a public variable or Lookup Table. I didn't know and
really still don't know which method will work best for my needs. I gotten 2
different methods of doing what I want and will try both before deciding. It
really amounts to about 5 or 6 default values, such as sales tax rate, late
fee charges, lock cut fee, admin fee, lien fee and nsf check fee. I use
these charges thru out the application for calculations and it would be nice
not to have to rewrite these values in code every time they change. I hope
that is clearer.

--

Joe Cilinceon



  #26  
Old November 14th, 2005, 04:09 PM
John Marshall, MVP
external usenet poster
 
Posts: n/a
Default Question about a default table or method

I would go with tina's suggestion. It places anything that may change over
time in a single location and avoids the problem of using duplicate
information that may get out of sync.

John... Visio MVP


"Joe Cilinceon" wrote in message
...

Actually John I asked for the best method to set default values in an
application, be it as a public variable or Lookup Table. I didn't know and
really still don't know which method will work best for my needs. I gotten
2 different methods of doing what I want and will try both before
deciding. It really amounts to about 5 or 6 default values, such as sales
tax rate, late fee charges, lock cut fee, admin fee, lien fee and nsf
check fee. I use these charges thru out the application for calculations
and it would be nice not to have to rewrite these values in code every
time they change. I hope that is clearer.

Joe Cilinceon



  #27  
Old November 14th, 2005, 04:18 PM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default Question about a default table or method

I concur. These types of data are best kept in a table. That also allows use
of a form for the updating of the values very easily.
--

Ken Snell
MS ACCESS MVP



"John Marshall, MVP" wrote in message
...
I would go with tina's suggestion. It places anything that may change over
time in a single location and avoids the problem of using duplicate
information that may get out of sync.

John... Visio MVP


"Joe Cilinceon" wrote in message
...

Actually John I asked for the best method to set default values in an
application, be it as a public variable or Lookup Table. I didn't know
and really still don't know which method will work best for my needs. I
gotten 2 different methods of doing what I want and will try both before
deciding. It really amounts to about 5 or 6 default values, such as sales
tax rate, late fee charges, lock cut fee, admin fee, lien fee and nsf
check fee. I use these charges thru out the application for calculations
and it would be nice not to have to rewrite these values in code every
time they change. I hope that is clearer.

Joe Cilinceon





  #28  
Old November 14th, 2005, 04:26 PM
PC Datasheet
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Joe,

Another point that you might consider ---

Your case is a good example of storing a calculated value rather than
calculating it on the fly. Say you have ExtendedCost = Cost * Sales Tax
Rate. Since Sales Tax Rate will vary, it would make things simpler to store
ExtendedCost each time rather than calculate it on the fly.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

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" wrote in message
...
John Marshall, MVP wrote:
I guess as a failed gas station attendant, you did not excel at
reading. Joe originally asked for "a variable that will last for a
session.". Global variables will do this, but there are caveats as I
mentioned..
John... Visio MVP


Actually John I asked for the best method to set default values in an
application, be it as a public variable or Lookup Table. I didn't know and
really still don't know which method will work best for my needs. I gotten
2 different methods of doing what I want and will try both before
deciding. It really amounts to about 5 or 6 default values, such as sales
tax rate, late fee charges, lock cut fee, admin fee, lien fee and nsf
check fee. I use these charges thru out the application for calculations
and it would be nice not to have to rewrite these values in code every
time they change. I hope that is clearer.

--

Joe Cilinceon





  #29  
Old November 14th, 2005, 04:31 PM
PC Datasheet
external usenet poster
 
Posts: n/a
Default Question about a default table or method

Dimwit,

Ask your mother to read Joe's response to you. Dimwit keeps getting dimmer.


"John Marshall, MVP" wrote in message
...
I guess as a failed gas station attendant, you did not excel at reading.
Joe originally asked for "a variable that will last for a session.". Global
variables will do this, but there are caveats as I mentioned..

John... Visio MVP

"PC Datasheet" wrote in message
nk.net...
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.


"John Marshall, MVP" wrote in message
...
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" wrote in message
...
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.

Joe Cilinceon





  #30  
Old November 14th, 2005, 04:44 PM
Joe Cilinceon
external usenet poster
 
Posts: n/a
Default Question about a default table or method

If I'm following what you are saying here, I think I already do that. I
store amount paid, charges covered and in some cases the rate things cost at
that time. I also have several functions I use in place of doing it over and
over again such as getting the last day of a month etc.


PC Datasheet wrote:
Joe,

Another point that you might consider ---

Your case is a good example of storing a calculated value rather than
calculating it on the fly. Say you have ExtendedCost = Cost * Sales
Tax Rate. Since Sales Tax Rate will vary, it would make things
simpler to store ExtendedCost each time rather than calculate it on
the fly.


"Joe Cilinceon" wrote in message
...
John Marshall, MVP wrote:
I guess as a failed gas station attendant, you did not excel at
reading. Joe originally asked for "a variable that will last for a
session.". Global variables will do this, but there are caveats as I
mentioned..
John... Visio MVP


Actually John I asked for the best method to set default values in an
application, be it as a public variable or Lookup Table. I didn't
know and really still don't know which method will work best for my
needs. I gotten 2 different methods of doing what I want and will
try both before deciding. It really amounts to about 5 or 6 default
values, such as sales tax rate, late fee charges, lock cut fee,
admin fee, lien fee and nsf check fee. I use these charges thru out
the application for calculations and it would be nice not to have to
rewrite these values in code every time they change. I hope that is
clearer. --

Joe Cilinceon


--

Joe Cilinceon



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add New Field to DB Karen Database Design 7 October 19th, 2005 08:03 PM
Survey Results SAm Running & Setting Up Queries 10 May 17th, 2005 08:32 PM
Access combo box-show name, not ID, in table? write on New Users 30 April 30th, 2005 09:11 PM
Table Design A. Williams Database Design 3 April 29th, 2005 07:02 PM
unable to repair inobox Sudheer Mumbai General Discussion 1 February 20th, 2005 12:55 PM


All times are GMT +1. The time now is 07:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.