Use same data for all records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Shel said:
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.
Click to expand...

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
 
John said:
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.
Click to expand...

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.

--
 
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
 
Back
Top