This will be very simple i am sure however I am just getting my feet wet at creating forms. I would like to create a process to generate my job tickets. I thought I would use a PK which would contain a yyyymmdd format for date and then a sequential field 00 for the unique identifier. I would like to increment the unique identifier by one for each job but when the day changes to the next day, reset the unique identifier back to one. if on 20041103 i have three jobs come in i should see 2004110301, 2004110302, 2004110303. On the start of the next day I want to see 2004110401 and so on. I am the only one in the office so I am not afraid of generating two tickets at the same time and I also will not be taking in more than 99 jobs in one day. This is only a start to my project. I will be adding as I go. If I could get the code to change the number when the date changes I would be most appreciative. I think I have an idea but as I can see you guys are geniuses out there.
access will not natively support what you would like to do here. If you're dead set on that scheme, you will probably have to write a custom function to do this for you. On the other hand, that's odd way to run your primary keys... There's really no reason to put date and sequence information in your primary key. You would be much better off running an arbitrary primary key, then storing the date in a seperate field. You can derive the sequence using those bits of information. This would give you greater flexibility, and eliminate the headache of trying to generate funky keys.
Teddy is right. To do what you want to do, you will have to write some code to manage that. I just wanted to add... In Access your primary key can consist of more than one field. And good database design dictates that each field should only have one piece of data stored in it. So if you are going to generate a two digit code for each job then I would have a JobID and a date field and the Primary Key would be the two fields. It is typically easier to combine two fields to display for the user than it is to separate two pieces of information in one field.
Sorry I must not have explained myself too well. I have taken field one ,Jobdate, and field two, Jobid, to make a PK of which the Jobid is unique based on the date. I understand I may need to code this and that is not a problem. I have this set up this way to track how many jobs at a glance I have on any given day. I have been manually doing this now for 3yrs and would hate to reinvent the wheel. This form is going to be used so when a job comes in I can just fill in the form and the number gets generated automatically. I will increment the Jobid until the day changes. If I check the system date and find it is not the same as the last jobdate should I not be able to set the Jobid back to 1. If the date does not change then increment by 1. I am not sure how to call the last record in the DB back to check to see if the date changed or not. Thanx for your response. It does help.