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

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

=-=-=-=-=-=-=-=-=-=-=-=-=

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