validation rule on a control

[Jeffrey Tan]

Hi there.

So I have a table called "users" and a form created from that table with some of the fields, including

• First_name
• Last_name
• address
• email
• windowsID

The table has existing records and the purpose of this form is to manage it for future new users to be added.

I wrote a sub and put it to the email setfocus method that automatically takes the first letter of the first_name and the full last name to create the email as well as add the @gmail.com part

email.text = (Mid(first_name.value,1,1) & Mid(last_name.value,1) & "@gmail.com")
 

i did the same for windowsID, where i take only the first two letters of the first_name and the first letter of the last_name

windowsID.text = (Mid(first_name.value,1,2) & Mid(last_name.value,1,1))
 

however, this won't work for records that do not follow this convention.. such as if the first_name of one user is "Bobby" and last_name is "Smith". the email should have been [e-mail] m. however, the user's email in that record is BobbySmith@gmai l.com.

i want to make sure that if the previous records are already populated with text, do not update the field using the code above. the stuff above will only "run" when there's a new record added. also have some kind of validation that if they click on the email field without putting in their firstname and lastname, a msgbox will appear telling them to fill in whichever is blank.

Thanks!

hope this isn't too hard to accomplish.

[NeoPa]

It's actually very easy Jeffrey. You simply set the .DefaultValue property of the control instead of its .Value property (Generally not a good approach to work with .Text unless it is a specifically textual issue).

The default value is only used for entering new records, and can easily be overridden by the operator if found to be inappropriate ;-)

It also has the benefit of not marking the new record as having been changed before the operator types anything in.