Creating a Code (1 Viewer)

[johnnyguy]

hi again thanks for the help with the last problem i had, i think ive sorted that now. but i have a new one.

im trying to create a code representing tutor names for the same database, for instance if i type in charles brown into the form, the tutor_code field is automatically filled in cb, so i want his initials to be the code. And then perhaps if there is more than 1 cb, it can put cb01 or cb02.

i thought i would need a query for the form to refer to, i have done some sql but have no idea how i can extract initials from a name and then check the code does not already exist and place a number after it. (cb02)

CJ

Ive attached the form.

 

[Kodo]

me.LecturerCode=left(txtSurname,1) & left(txtForename,1)

assuming your text boxes are LecturerCode, txtSurname and txtForename

 

[dcx693]

Why not have people pick a tutor name from a drop-down list? If you do that, and base the records in the drop-down list on a query that has 2 columns (one displaying the tutor names, and the other hidden column containing the initals), you can then use a small amount of VBA code to place the selected tutor's initials into another text box on the control. Very little typing involved for the user.

You can even set up the combo box to accept new entries along with new initials (but that's a bit more advanced than where you seem to be).

 

[johnnyguy]

hi thanks for that i now have a button on the form to generate a code of 2 letters from the surname and forename, does any1 know how i could do a check to see if those letters already exist in the table.

so i would generate cb01 and then if another cb came along it would automatically become cb02?

CJ