Setting a default to equal another field

enxio27

Registered User.
Local time
Today, 02:16
Joined
Oct 21, 2006
Messages
14
I have a membership database. A number of members go by their middle names or a nickname rather than their first name. I need to keep track of their real full names, but I want the new-record default of the preferred name field to equal the contents of the first name field. How do I set this in the table design? The table already has 1400 records, and it would be quite a job to manually copy each first name to the preferred name field, a job I would rather avoid.
 
The easiest way is to show the nickname field on whatever form you use create the record

in the afterupdate event for the first name have code something like

if len(nickname)=0 then nickname = firstname, as the nickname may aready have been set, and you don't want to overwrite it. If you need to make the nickname mandatory in the table design, or you could use code to determine that the nickname IS the firstname if the nickname is blank - a matter of taste really

set all the nicknames with an update query updating all the nicknames to firstname, where len(nickname)=0"
 
Hmmm. . . I'm going to have to chew on that one a bit. Do I need to set up a form in order for the preferred name to default to the first name for new records?
 
I've just tried - but I don't think you can define a default value by reference to other fields in the table -

in any event, see my reply to your other posting - you should use forms to enter data, not enter directly into the table.

for access to be anything other than a spreadsheet you need tables to relate to each other. once you have multiple tables, and have set up some relationships, then the value of an entry in a given table is meaningless without reference to contents of an associated table.

eg

in your membership table you have details of "Name", "Adress" , "Phone" . You also need a unique identifier for the table, so you exclude duplicate values. Name may be insufficient - you may have 2 John Smiths for instance, so you should assign an unique Member number to each member.

Now you may have another table for Membership Payments. For each member you store all the payments they make. In this table you need to store the member number from the other table.

Now if you open the Access payments table, the member details you see is just the member no. As the system becomes more complex these "foreign keys" build up, and it is very difficult to follow what is happening from the tables alone. In fact, nobody would try to do that. You process these tables into presentable information through queries and more importantly forms and reports.

I hope this helps. Thers lots of info here in this forum, and its worth getting a good primer from the reading list and getting a good basic understanding of what a (relational) database is all about.
 
I understand what you're saying. I do have a fair amount of experience with relational databases in general, but I'm pretty much at the bottom of the learning curve with respect to Access itself.

I haven't messed with the forms much because, up 'til now, the hassle just wasn't worth it. I think that now I'm going to have to bite the bullet and do it.

I have Access 2003 for Dummies, but there are just some questions it's not answering for me. I'm delighted to find these forums and such helpful, experienced folks like yourself.
 
UPDATE:

I finally figured out how to do what I want with a query, so that the preferred name comes out on the reports. That's really the only place that it mattered, anyway. Thanks for all your help on this!
 

Users who are viewing this thread

Back
Top Bottom