1. You have to make a choice between Pat's suggestion and mine. They are fundamentally the same with one difference: Pat provided you with a formula that simulates an autonumber and WILL RESET for each different year (ex: 02_00001, 02-00002, 02-00003, 03-00001), but you will have to manage POTENTIAL DUPLICATE keys conflicts in multi-users environment, as she explained.
Mine won't reset (ex: 02_00001, 02-00002, 02-00003, 03-00004), but uses true increment autonumber (so you don't have to worry about duplicates. This is managed by Access)
If you decided to go for the autonumber, you can use a two-fields primary key as I suggested, but you do not really need to, since Access will take care of ensuring the uniqueness of the autonumber. It would be really necessary if you decided to use Pat's formula which do not produce unique numbers, but reset for each new years (In that case, only the association year + number may be unique)
2. Neither my nor Pat's suggestion will ever store a built serial key in 00-00000 format in your table. We both exposed extensively why this would be bad practice! Our suggestions will store separately the year and a sequential number, and DISPLAY your 00-00000 format serial. Your users won't see the difference.
Perhaps I was not clear on that, but Pat was: you won't let anyone fill the serial number. On the contrary you will disable/lock this control. Your serials are built automatically through the methods we suggested!
It is a VERY bad idea to let the users enter/modify primary keys! Among various reasons for that: you would have to write code to trap already existing keys entered by your user and get them corrected; You would have also to provide to your users an easy mean to know what keys have already been used...
3. Did you consider Pat's advice regarding negotiating the year format with your customers? (code the year on 4 digits instead of 2). This is something I had hesitated to recomand also. Although not vital, it is practical.
4. Your form Clearance_ID control is not ok. First call it txtSerial, for example, to avoid any confusion with the Clearance_ID field which is only an incrementing number, not the entire serial you want to display. Then, with the method we suggested, you would not use a bound control since your built serial key would not actually be stored in your database. You would just display it on the fly. So txtSerial should be an unbound textbox with acontrol source set to : = [txtYear] & "-" & [txtClearance ID]
5. I suggested to use the default value =Right(Year(Now(),2)) for your txtDate bound control, and not for txtSerial, as an easy mean to store the current year in your underlying table whenever a new record is created.
6. I don't know where you took Format(Year(Now(),2))
I suggested Format(Now(),"yy") and Right(Year(Now(),2)) (unconsistent me
)
and Pat's: Format(Date(),"yy")
All give the same result. Pat's formula is somewhat more efficient.
7. The above mentionned = [txtYear] & "-" & [txtClearanceID]
in txtSerial implies that you should have two other controls on your form called txtYear and txtClearanceID (note the prefixes to help to distinguish between controls and fields, and to identify a textbox)
These are bound (to Year and ClearanceID) controls, which may be invisible. Why using invisible text boxes ? There would be other ways to get the Year and ClearanceID corresponding to the current record of the underlying table, but this is a very simple way to have them at disposal automatically.
8. Avoid spaces in you field and table names: use Clearance_ID or ClearanceID and ClearanceCoverPage etc.
Please, tell me if I am speaking Chinese. I know my poor English can be confusing. You may get clearer explanations from Pat or others.
