Auto fill values in form- 3 situations

[kami]

Hi! I am an advanced beginner access user and I’ve been trying to make a form a little more user-friendly. My limited skills have only gotten me so far, and I am still struggling to find solutions to these three things:



1. I would like to have Client ID automatically fill in based on values in other fields in the form. The Client ID is the first letter of the first name, last three letters of last name and year of birth. There is currently a spot to type in first name and last name, but these fields only need to be filled out for about 1% clients. If possible I would like to have spots to type in first name and last name that are not recorded in the table, but are used to construct the Client ID. If it isn’t possible, the already existing fields will do.

2. The form contains two dates: Sample Date and Session Date. They are always the same, but I have to keep both on the form. I would like to automatically fill in the Session Date after Sample Date is filled in. If it makes a difference, these dates are broken up by month, day and year in separate fields.

3. There are three types of “colors” and three types of “flavors” listed in drop boxes. Red and vanilla always go together. I would like to make it so when red is chosen from the drop box, vanilla will automatically be filled in.

Any suggestions are greatly appreciated! Thank you!

[Rod]

You will need to write a few lines of VBA code. I assume you're happy with that and know how to get to the form's module. If you don't then ask.

Splitting dates into separate controls for month, day and year does mean you cannot use all those handy Access date functions but for the moment don't consider redesigning.

As an example let's assume that the month field for the sample date is a field called intSampleDateMonth and the field for the session date month is called intSessionDateMonth. In the AfterUpdate procedure for intSampleDateMonth simply type:

 
intSessionDateMonth = intSampleDateMonth

Do similar for the day and year portions.

Let's now assume that the field containing the last name is called txtLastName. Whether this is the existing field or a new one does not matter for this example. In the AfterUpdate event of txtLastName type:

 
txtLastName = Trim(txtLastName)
txtClientId = Left(txtLastNmae,1) & Right(txtLastName)

You do not tell us where the date of birth comes from but you will have to enhance the second line above to append the date of birth in whatever format you require, yyyymmdd, mmddyy, etc.

A word of warning.

You may want to test whether a client id already exists and if so do nothing. I can't think of anything worse than already assigned ids changing because someone corrects the spelling of the last name.

[kami]

Got it all to work! Thanks so much!