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  

Use same data for all records



 
 
Thread Tools Display Modes
  #1  
Old February 10th, 2005, 08:01 PM
Shel
external usenet poster
 
Posts: n/a
Default Use same data for all records

Is there a way to do the following?

The scenario: The “Animals” database contains the “AnimalsInfo” table.
tblAnimalInfo contains the “AnimalType” field. User1 is always entering data
for Cats. User2 is always entering data for Dogs. User3 is always entering
data for Fish.

The question: What is the easiest way to automatically fill in the
“AnimalType” field based on which user is entering the record?

I really don’t want to use the built in security feature for this because
that entails setting up security and permissions for each user, which is not
necessary in this application.

--
Thanks in advance!
Shel
  #2  
Old February 11th, 2005, 07:07 AM
John Vinson
external usenet poster
 
Posts: n/a
Default



"Shel" wrote:

The question: What is the easiest way to automatically fill in the
“AnimalType” field based on which user is entering the record?

I really don’t want to use the built in security feature for this because
that entails setting up security and permissions for each user, which is not
necessary in this application.


A couple of suggestions. If each user is working from their own PC, linked
to a shared backend database (as they should be, if you have multiple users!)
then you can have each user set the appropriate Default property for this
field. You might also want to set the field's Tab Stop property to No so they
don't need to tab through it - they could click into it if needed for an
out-of-the-ordinary entry.

Or, if all users are timesharing the same PC, you can set the DefaultValue
property of the field in its own AfterUpdate event:

Private Sub AnimalType_AfterUpdate()
Me!AnimalType.DefaultValue = Chr(34) & Me!AnimalType & Chr(34)
End Sub

Chr(34) is the " character needed to delimit the default string.

John W. Vinson/MVP
  #3  
Old February 11th, 2005, 04:38 PM
onedaywhen
external usenet poster
 
Posts: n/a
Default


John Vinson wrote:
"Shel" wrote:

User1 is always entering data
for Cats. User2 is always entering data for Dogs. User3 is always

entering
data for Fish.

The question: What is the easiest way to automatically fill in the
"AnimalType" field based on which user is entering the record?


If each user is working from their own PC, linked
to a shared backend database (as they should be, if you have multiple

users!)
then you can have each user set the appropriate Default property for

this
field.


Unlike the subject matter in another thread, I can't imagine many
shared attributes between the AnimalTypes (isn't there an oft heard
phrase about the difference between cats and dogs ... what then fish?!)
So perhaps there is value in modelling each as a separate table, with a
base table for the (few?) common attributes. Then you could present a
VIEW (Query, Form, whatever) specific to each table/AnimalType/user.

Here's a structure I stole from somewhere. John, you get to practice
your new-found Jet CHECK constraints g:

CREATE TABLE Animals
(AnimalID CHAR(10) NOT NULL PRIMARY KEY,
AnimalType CHAR(4) NOT NULL,
CHECK(AnimalType IN ('CAT', 'DOG','FISH')),
UNIQUE (AnimalID, AnimalType),
common columns);

CREATE TABLE Cats
(AnimalID CHAR(10) NOT NULL PRIMARY KEY,
AnimalType CHAR(4) DEFAULT 'CAT' NOT NULL, CHECK(AnimalType = 'CAT'),
UNIQUE (AnimalID, AnimalType),
FOREIGN KEY (AnimalID, AnimalType)
REFERENCES Animals(AnimalID, AnimalType)
ON UPDATE CASCADE
ON DELETE CASCADE,
cat specific columns);

CREATE TABLE Dogs
(AnimalID CHAR(10) NOT NULL PRIMARY KEY,
AnimalType CHAR(4) DEFAULT 'DOG' NOT NULL,
CHECK(AnimalType = 'DOG'),
UNIQUE (AnimalID, AnimalType),
FOREIGN KEY (AnimalID, AnimalType)
REFERENCES Animals(AnimalID, AnimalType)
ON UPDATE CASCADE
ON DELETE CASCADE,
dog specific columns);

CREATE TABLE Fishes
(AnimalID CHAR(10) NOT NULL PRIMARY KEY,
AnimalType CHAR(4) DEFAULT 'FISH' NOT NULL,
CHECK(AnimalType = 'FISH'),
UNIQUE (AnimalID, AnimalType),
FOREIGN KEY (AnimalID, AnimalType)
REFERENCES Animals(AnimalID, AnimalType)
ON UPDATE CASCADE
ON DELETE CASCADE,
fish specific columns);

Jamie.

--

  #4  
Old February 11th, 2005, 06:34 PM
Tony Vrolyk
external usenet poster
 
Posts: n/a
Default

To build on John's suggestion...

If you occasionally update the frontend file - new programming, queries,
reports, etc - you will end up overwriting the default values or you will
have to create multiple copies of the frontend and distribute them to their
appropriate user. You should provide a way for users to set their own
default, preferably in a way that doesn't need to be reset after every
frontend update.

John, I am sure that is where you would have gone next.

I have accomplished this in a couple ways depending on the need.

1. In my app, on several forms, there is a subform control whose source
object can be changed by an option group on the main form. I allow users to
set their own defaults using this solution.

Have a frontend linked with both the main backend data file (which in my
case is on a shared volume on the server) and also a "system" mdb located on
the users PC. In the local linked file I have a table where they store their
preferred default view. You could use this same solution to store the
default animal for that PC. This allows out to update the main frontend file
without overwriting the users settings. Of course this doesn't work in a
shared PC environment.

2. Sometimes I use the function http://www.mvps.org/access/api/api0008.htm
to determine the user logged on and then set various options as needed. This
can also be handy to do some lightweight security.

If fOSUsename = "johndoe" Then ...

You could even create a system table where a list of usernames is stored
with their default animal then do a dlookup to determine the correct setting
to use when opening the form. This allows you make the code generic rather
than hard coding all the different usernames.

strDefault = Dlookup("[DefaultAnimal]","tblSystem","[UserName]='" &
fOSUsername & "'")

Hope something here works for you
Tony V





"John Vinson" wrote in message
...


"Shel" wrote:

The question: What is the easiest way to automatically fill in the
"AnimalType" field based on which user is entering the record?

I really don't want to use the built in security feature for this because
that entails setting up security and permissions for each user, which is
not
necessary in this application.


A couple of suggestions. If each user is working from their own PC, linked
to a shared backend database (as they should be, if you have multiple
users!)
then you can have each user set the appropriate Default property for this
field. You might also want to set the field's Tab Stop property to No so
they
don't need to tab through it - they could click into it if needed for an
out-of-the-ordinary entry.

Or, if all users are timesharing the same PC, you can set the DefaultValue
property of the field in its own AfterUpdate event:

Private Sub AnimalType_AfterUpdate()
Me!AnimalType.DefaultValue = Chr(34) & Me!AnimalType & Chr(34)
End Sub

Chr(34) is the " character needed to delimit the default string.

John W. Vinson/MVP



 




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
multiple docs, one data source kp Mailmerge 12 January 31st, 2005 05:41 PM
Rapid input Via datasheet RudyR_Seattle General Discussion 4 January 31st, 2005 02:33 AM
transpose john Using Forms 1 November 24th, 2004 07:16 PM
SQL view of messed up action queries Kendra Running & Setting Up Queries 2 August 31st, 2004 09:53 PM


All times are GMT +1. The time now is 11:00 AM.


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