I have worked with Access on and off over the years, but am still a fair newbie.
I have dilemma on having a form generate a project number based on input from three controls. Here's what I need. I am using Access 2007. I'm sure this question covers multiple areas of this forum, but I put it in the forms section since this is all driven from my entry form.
Each project number has three parts. A 4 letter alpha code, a two digit year, and a 4 digit numeric identifier: i.e. ABCD081002.
In my form, the user picks the alpha code from a combo box, the year from a combo box, and a project category from a combo box. Each project category has a specific numeric range for the 4 digit identifier: i.e Cat 1 - 1001-1999, Cat 2 - 2001-2999 and so on. These values cannot be reused within the same year, but are reused each year.
After the user picks the category, I need the database to find the highest value associated with that category in that year and then automatically add 1 to it. Then I need the form to concatenate the values from the three controls and place them into a field in my table. This field is the primary key for the table. I would also like to have this value displayed on the form itself as well.
I have tables set up for the Alpha code, the year and the Project category. My project category table has 3 fields, Category Name, Min Value and Max Value.
I have fields in my main table for the Alpha code, year, project category, numeric identifier, and full project number (primary key), as well as the other info needed.
Any help on this would be greatly appreciated. I'm not sure if I really need all of those fields in my table or not, but that's where I started.
I have dilemma on having a form generate a project number based on input from three controls. Here's what I need. I am using Access 2007. I'm sure this question covers multiple areas of this forum, but I put it in the forms section since this is all driven from my entry form.
Each project number has three parts. A 4 letter alpha code, a two digit year, and a 4 digit numeric identifier: i.e. ABCD081002.
In my form, the user picks the alpha code from a combo box, the year from a combo box, and a project category from a combo box. Each project category has a specific numeric range for the 4 digit identifier: i.e Cat 1 - 1001-1999, Cat 2 - 2001-2999 and so on. These values cannot be reused within the same year, but are reused each year.
After the user picks the category, I need the database to find the highest value associated with that category in that year and then automatically add 1 to it. Then I need the form to concatenate the values from the three controls and place them into a field in my table. This field is the primary key for the table. I would also like to have this value displayed on the form itself as well.
I have tables set up for the Alpha code, the year and the Project category. My project category table has 3 fields, Category Name, Min Value and Max Value.
I have fields in my main table for the Alpha code, year, project category, numeric identifier, and full project number (primary key), as well as the other info needed.
Any help on this would be greatly appreciated. I'm not sure if I really need all of those fields in my table or not, but that's where I started.