Keith Silva Posted September 18, 2000 Posted September 18, 2000 When I create a new record, I want to use the auto-enter feature to calculate the contents of the RecordID field (a text "key" field). I want the RecordID field calculation to use three other fields in the new record. The calculation would be as follows: LastName&", "& FirstName&" - "&Phone, where LastName, FirstName, and Phone are fields in the new record. I like this approach because the RecordID field is understandable to the user (rather than a sequence number) and, consequently, debugging and managing the database is easier. The problem I'm having is that the calculation occurs when the new record is opened and these three fields are blank. Consequently, the RecordID field does not contain any information except the ", -". Is there someway to delay the calculation until the fields are populated? Is there some other way to work around this situation? Thanks, Keith Silva
Kurt Knippel Posted September 19, 2000 Posted September 19, 2000 quote: Originally posted by Keith Silva: When I create a new record, I want to use the auto-enter feature to calculate the contents of the RecordID field (a text "key" field). I want the RecordID field calculation to use three other fields in the new record. The calculation would be as follows: LastName&", "& FirstName&" - "&Phone, where LastName, FirstName, and Phone are fields in the new record. I like this approach because the RecordID field is understandable to the user (rather than a sequence number) and, consequently, debugging and managing the database is easier. The problem I'm having is that the calculation occurs when the new record is opened and these three fields are blank. Consequently, the RecordID field does not contain any information except the ", -". Is there someway to delay the calculation until the fields are populated? Is there some other way to work around this situation? Ok, here is the problem. You are choosing user-definable data to use are your IDs. This is bad, both for the reason above (the data does not exist to build the key) and because this data can change (key "Smith - John - 865-2345" can be easily associated with Mr Johnson Smithey at 234-3454) or worse might be duplicated. Setup a standard ID system as follows: RecordID_kpt = Auto-entered Calculation [status(CurrentDate) & "-" & Status(CurrentTime) & "-" & Status(CurrentRecordID)] Do not make this available or even visible to the user, and use this for all of your important relationships and functionality that relies on IDs. This insures that each record get a UNIQUE, UNDUPLICATABLE key. Use the funky Name & PhoneNumber type of IDs for what is shown to the customer on the screen. They will never know that you are not using this for the relationships and functionality. ------------------ =-=-=-=-=-=-=-=-=-=-=-=-= Kurt Knippel Consultant Database Resources mailto:[email protected] http://www.database-resources.com =-=-=-=-=-=-=-=-=-=-=-=-=
Keith Silva Posted September 20, 2000 Author Posted September 20, 2000 Sounds like the voice of reason and experience talking. I'm going to follow your advice. Thanks for the response to my question. Your answers to other questions have been very helpful to me as well. Regards, Keith Silva
Recommended Posts
This topic is 8817 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now