Generating Project Number from multiple form fields

dbkreuz

New member
Local time
Today, 15:53
Joined
Aug 6, 2008
Messages
2
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.
 
tell about tables and fileds in detail
and also
what is criterea for picking the number from a range
 
Tables:

Alpha Code table has one field with three records. Table used to populate combo box on form.

Year table has one field with years 04 through 15 records currently populated. Table used to populate combo box on form.

Category Table has three fields: Cat_Name, Min_Value, Max_Value, 5 category records populated.

Project Table has following fields: Alpha code, year, project category, numeric identifier, and full project number (primary key), as well as the other info fields needed.

When the user picks the category for the project from the combo box, I need to have something (query?) search for the highest value used in the range for that category in the year selected stored in the main table. Once the value is found, 1 is automatically added to it.

Here is rough schematic:

Choose Alpha Code: i.e. ABCD
Choose year: ie. 08
Choose category: i.e Cat-1 (min value 1001, max value 1999)
-> main table searched for highest used value in chosen category in chosen year (i.e. 1345, add one and store 1346). [triggered with on Change event?]
Project number field in main table then populated with concatenated value: i.e. ABCD081346
 

Users who are viewing this thread

Back
Top Bottom