Define what you mean by next serial value.
If you created two new records in succession, do you want:
The first is easy, the second can be challenging
and neither should be used as the primary key linking your records to records in other tables.
Interesting observation. Because if I think about it, the second one could get even more trickier if for example I had BHS02001, BHS02002, BHS04001, BHS05001. Lots of variables there!
I think I will go with Number 1's method. And I will keep an auto generated Serial ID as a separate field in the background. This will be a separate identifier.
With Option 1, your auto entered serial number can be part of the solution for this.
Just define a calculation field that returns text as:
CategoryField & GradeLevelField & SerialNumberField.
If you need leading zeroes, you can use this variaton:
CategoryField & GradeLevelField & Right ( "0000" & SerialNumberField ; 5 )
That is too easy. You are fantastic.
I have another question.
If I have a table that I am using as a value list for a field in another table, (Schools table, School Name Field and School District Field)
I want to enter a school name for a student in School Field on Student Info Layout and I want it to put the school district that the school is in into the field School District on the Student Info Layout without having to enter the School District separately each time I choose a school.
So the School District is the Parent record in the Schools Table and the School Name is the child.
How do I do this?
If you have a relationship linking the two tables by school, simply place the School district Field from the schools table on your students layout.
You can also define a looked up value field option on a school district field in your student table to copy the district name from the schools table, but I don't think you'll need that option here.
I had tried that and I just realized I had one thing wrong but with your help, it works. Looking good.