Unanswered: I Need Some Help with Auto Generate String
I need some constructive input to be able to finish my program.
I have a field called (1) DateRec, another (2) Municipality and another (3) Type. These three fields invariably will automatically generate a string of number for another field that is called FileNumber. This FileNumber is already the Primary Key in the program. The (1) is the date received, that is the date the application of the client was received. (2) The Municipality field will be programmatically structured to produce one or two letter characters that will represent each Municipality in the FileNumber for every application within the County where the applicant is from. Right now in the program, all the 23 Municipalities are listed in a combo box with their full names. And (3) which is the Type is the state of the application, it will only be involved in the FileNumber, when it is an "EXEMPT", then, the nomenclature will be; an 'E'.
Here comes the drill! When the user enters the date received, it will be a complete date, with day, month and year. The only thing that will go to the FileNumber will be the year, like 2006. When a Municipality is chosen by the user, for instance Atlantic City, it will generate a nomenclature like 'AC', if it is for Brigantine it will be a 'B'. Each Municipality will have a unique identifier of one or two characters only. And there are twenty-three Municipalities in all. Then the program will run a query that will check for the last FileNumber under that Municipality the user has chosen and update the middle number with one number increment. So the FileNumber will be something like AC-2-2006 or B-1-2006, for another entry on this same Municipality it will be AC-3-2006 or B-2-2006, AC-4-2006 or B-3-2006 etc. Hence, the middle number will be like a counter that will help the user to know the number of applications for each municipality within a certain year. Then, when there is an Exemption in the application, the user will select EXEMPT from Type, it will emerge the 'E' with the middle number; like AC-5E-2006. For this to work the program will enforce the user to enter Date received, and the Municipality and check for Type before he exit, the program. This is all that is needed.
I definitely, need a help to get this working. The purpose for this is to debar the user from entering wrong File numbers.
I am afraid that you have broken one of the fundamental rules of relational database design. Please have a gander at this. Basically - you don't want to make you PK a derivation of other fields. You could, of course, have a composite key but not this.
if you sort out your primary key and also your relationships between tables you will probably be most, if not all, the way there to answering your question