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
|
|||
|
|||
Set default value in table or form
I have been wondering if there is a reason to favor either the table, a
bound control, or VBA (the form's Current event, for instance) for setting the default value of a field. I prefer to stay away from the table just because once it is designed I would rather leave it alone and work with the form, but I don't know if there is any technical reason to prefer one over the other. |
#2
|
|||
|
|||
Set default value in table or form
Yes, that's a good reason. Particularly in a split database, maintenance is
simpler if you set the Default Value in the text box on a form, rather than have to change the default in the back end database. There is also a bug in Access that you can workaround if you do not set the Default Value in the table. Picture a form that has a query as its RecordSource. The query contains the table you really want for your form, as well as a lookup table that you are not writing to but want to read values from (e.g. for sorting or filtering.) If this lookup table has a Default Value set on one of its fields, then in some situations you will have problems adding new records in the subform. At the first keystroke when you begin typing the new record, Access complains with a fairly unobvious message that says it is unable to assign the value. It is trying to assign the Default Value to the field in the Lookup table (even though you are not trying to add a record to that table), and naturally that fails. Removing the Default Value from the lookup table (or from the text box on the form) averts this problem. Having struck this a couple of times, I never assign a Default Value to any table field now. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... I have been wondering if there is a reason to favor either the table, a bound control, or VBA (the form's Current event, for instance) for setting the default value of a field. I prefer to stay away from the table just because once it is designed I would rather leave it alone and work with the form, but I don't know if there is any technical reason to prefer one over the other. |
#3
|
|||
|
|||
Set default value in table or form
Allen,
Thanks for your response, and for all of the help you provide here and on your web site, including your awareness of bugs and how to go about swatting them. The point about maintenance on a split database is quite apparent now that I have read it. I am still relatively new to Access, but one of the things I picked up right away (from where I do not recall) was the idea of setting formats, defaults, etc. in the interface rather than in the table when possible. Lately I have been wondering if this was actually a good idea. Thanks once again. "Allen Browne" wrote in message ... Yes, that's a good reason. Particularly in a split database, maintenance is simpler if you set the Default Value in the text box on a form, rather than have to change the default in the back end database. There is also a bug in Access that you can workaround if you do not set the Default Value in the table. Picture a form that has a query as its RecordSource. The query contains the table you really want for your form, as well as a lookup table that you are not writing to but want to read values from (e.g. for sorting or filtering.) If this lookup table has a Default Value set on one of its fields, then in some situations you will have problems adding new records in the subform. At the first keystroke when you begin typing the new record, Access complains with a fairly unobvious message that says it is unable to assign the value. It is trying to assign the Default Value to the field in the Lookup table (even though you are not trying to add a record to that table), and naturally that fails. Removing the Default Value from the lookup table (or from the text box on the form) averts this problem. Having struck this a couple of times, I never assign a Default Value to any table field now. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... I have been wondering if there is a reason to favor either the table, a bound control, or VBA (the form's Current event, for instance) for setting the default value of a field. I prefer to stay away from the table just because once it is designed I would rather leave it alone and work with the form, but I don't know if there is any technical reason to prefer one over the other. |
#4
|
|||
|
|||
Set default value in table or form
"BruceM" wrote in
: I have been wondering if there is a reason to favor either the table, a bound control, or VBA (the form's Current event, for instance) for setting the default value of a field. In my book, it's horses for courses: Table-level settings should be reserved for items vital to data integrity. For example, Required fields probably should have default values to prevent nasty error messages on insert commands. The control's defaultvalue property is convenient on forms but won't help users who insert records using VB or Excel or Word etc. Or a different form! -- you have to remember to dial it in for all your user interface objects. Using the OnCurrent or BeforeInsert event is very flexible, and you can change the defaulted value in response to all kinds of other aspects; but suffers from all the disadvantages of the control DV and you need to take care not to let it overwrite good data in existing records. Hope that helps Tim F |
#5
|
|||
|
|||
Set default value in table or form
Tim,
Thanks for your thoughts on the subject. I hadn't considered the point about using several different forms for the same table. Apparently I haven't used default values in such cases, or I might have noticed the inconvenience of changing several forms. Same for importing data, a situation I usually encounter only at the beginning of the process of say, converting from Excel to Access, or form an old database into a newer one. Since default value as I understand it applies only to new records I would not have thought overwriting existing data to be a concern. "Tim Ferguson" wrote in message ... "BruceM" wrote in : I have been wondering if there is a reason to favor either the table, a bound control, or VBA (the form's Current event, for instance) for setting the default value of a field. In my book, it's horses for courses: Table-level settings should be reserved for items vital to data integrity. For example, Required fields probably should have default values to prevent nasty error messages on insert commands. The control's defaultvalue property is convenient on forms but won't help users who insert records using VB or Excel or Word etc. Or a different form! -- you have to remember to dial it in for all your user interface objects. Using the OnCurrent or BeforeInsert event is very flexible, and you can change the defaulted value in response to all kinds of other aspects; but suffers from all the disadvantages of the control DV and you need to take care not to let it overwrite good data in existing records. Hope that helps Tim F |
#6
|
|||
|
|||
Set default value in table or form
"BruceM" wrote in
: I hadn't considered the point about using several different forms for the same table. Standard practice: one form for creating the purchase order, a different one for the packers, another one for the payment receipts office.. My design philosophy is One Table = One Business Entity, One Form = One Business Process. Since default value as I understand it applies only to new records I would not have thought overwriting existing data to be a concern. It's only really a concern if you use the form OnCurrent event to fill in the default values -- you have to check if the current record is a new one (need to put something in) or an old one (don't overwrite old data). Best wishes Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query is not updatable - | Doug Johnson via AccessMonster.com | Running & Setting Up Queries | 3 | January 21st, 2006 12:36 AM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |