combo boxes and default values

[uscitizen]

i have a pair of fields that occur on all tables in my database. the values they would contain would be constant across records in each table and across all tables in the database. to get 'round having the user fill in that redundant data, i thought i'd have a look-up list (combo box) attached to each of the two fields but the user would still have to click on the drop down arrow to select the value. since one of the two fields can have up to 255 characters and spaces of textual information, i'd prefer not to have to manually type in this data as the default for each of the tables. what i had in mind was somehow entering the text into its look-up table once and referencing that particular value in some 'easy' way in the 'default value' property row in each table.

 

[WildHare]

Prime candidates for Lookup tables. A simple, perhaps numeric key, and a textual description that goes with it.

Store the KEY value in your base table, as a "foreign" key, as they are so jingoistically called. Link the two tables on a One To Many to Display the matching TEXT value anytime you want.

You CAN set a particular 'key' code as the default value for your foreign key field in the base table.

For example, consider a table of States and their sales Tax rates: NJ /.06 , DE / 0 etc etc.

A table that contains STATE as a field, could DEFAULT to "NJ" if most of my customers are in New Joisey.

Does this shed some light?

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[uscitizen]

welll yes and no..........

consider that the text i am trying to avoid writing as the default value in every table is a research project title (these things can get pretty hairy). is your suggestion this. in a lookup table, create two columns, one a single digit numeric one, the second column a text field into which i write the massively long project title (field length of 225). then, in the base table, use the text field as a primary index from which i create a relationship with the text field in the lookup table. lastly, as the default, i would specify the single digit, e.g. '1', from the look-up table?

 

[WildHare]

No - the matching field between the two tables is the short numeric key, not the long text field.

The long text field should be typed into your computer ONE TIME. One Time ONLY. To populate the lookup (reference) table.

Re-read my example again, only this time substitute "Name of State" for "Tax Rate":

NJ : New Jersey
MI : Mississippi
NJ : New Hampshire


Customer Table:
Name : Me
Address: Here
City : There
|--STATE : NJ
| Zip. : 00000
| etc etc etc
|
|
| State-Name-Lookup Table
|--StateCode : NJ
StateName : New Jersey
StateCapital: Trenton
StateBird : Mosquito
StateMotto: Garden State

See the linkage? Using the "NJ" at the customer level, I can go into the State-Name-Lookup-Table and retrieve ANY of the other longer textual fields, without having to store them more than ONE TIME.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[uscitizen]

okay, let's take it from the top again.....

maybe if i put some perspective on this it might help (me).

atop each form in the form header part what i (think i) want is what is called the project id code and the project title. i would set each of the two fields' tab values to 0 or something to prevent the user from wandering into the header and trying to change them.

i want them to appear on every record since every record will only apply to the named project id and title: e.g. id = 9007, title: "the effect of new jersey swamp gas on the mosquito population". although a database will be used for only one project at a time, it is possible that data across databases might one day be 'pooled' so it'd be kind of good to be able to differentiate among records from each database and this way a new data entry person would not mistakenly enter data from one project into the database meant for another. so the objective is to make life as easy for the clerk -- (s)he won't have to enter the same info on each record in each table/form.

what i (keep thinking i) want is to have a lookup-field that would populate the data from the look-up table in the id and title fields in the base table without any active participation of the clerk's. i, the developer, could ahead of the data entry effort, enter in the look-up table a single record's worth of data, i.e. the id number and the project title and 'woosh' those items would appear in the related table's related fields and henceforth in the header part of the form using the base table.

i think i tried your suggestion already, w/o much luck. i created a lookup table with a pair of fields, a number field and a textfield. the number field was designated the primary key. in the relationships are of access, i dropped a line from the lookup table to the base table's number fields such that the numerical fields were 'hooked up' and the infinity symbol is adjacent to the number field in the base table. i designated the id number field in the base table a lookup field, hooking up to the lookup table's name, indicating there are two columns. so far everything's copacetic: i can see that the drop down button will display both the number id and title from the look-up table. but that's not what i wanted. i wanted the title to be entered into the title field on the base table. i hope i'm managing to put a lid on the confusion factor...