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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
multiple docs, one data source | kp | Mailmerge | 12 | January 31st, 2005 04:41 PM |
Rapid input Via datasheet | RudyR_Seattle | General Discussion | 4 | January 31st, 2005 01:33 AM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |