Assigning Keys

[tbaxter]

I'm currently designing, on paper, an application that I'm trying to develop to track events. I can't give specifics but will try to elaborate a little.

To give more detail, I'll have options similar to the following in my main menu {sub-menus contained in brackets would show up as a new menu when a selection was made from the main menu}:

1. Add New Event
{1. Add New Type 1 Event}
{2. Add New Type 2 Event}
{3. Add New Type 3 Event}
{4. Return to Main Menu}
2. Edit Events
{1. Edit Type 1 Event}
{2. Edit Type 2 Event}
{3. Edit Type 3 Event}
{4. Return to Main Menu}
3. Print Report
{with various options}
4. Exit Application

Note: each event type deals with a different type of equipment, so a Type 1 event uses Type 1 equipment, etc.

When the user selects the type of event, a form will open, prompting them to enter information for that particular event. What I want is for the application to automatically enter the event ID based on the user's selection of type of event (menu choice) and his/her subsequent selection of the unit (for lack of a better term) using that type of equipment. The unit would be selected via a drop-down box on the form. The number assigned would be the 2-letter abbreviation for the unit plus the 1-digit number for the type of event (1 for Type 1, 2 for Type 2, 3 for Type 3) plus the 3-digit sequence number. If Unit1 (AD) has 5 Type 2 events already in the database and the user needs to enter the 6th, I want Access to automatically fill in AD2006 as the EventID when they select "Add New Type 2 Event" from the menu, and select Unit 1 (AD) from the drop box on the form.

In other words, when the user selects 1, for Add New Event, then 2, for New Type 2 Event, can Access "know" to place "2" as the 3rd character of the Event ID number? And then when the user selects Unit 1 (AD) from the drop-down on the form, can Access "know" to place "AD" as the first 2 characters of the Event ID number? And then search for the last number in sequence that was used for AD2XXX and assign the next unassigned number (006)?

Hopefully, this makes sense!

I've asked to attend an Access course which starts REAL SOON but have not yet received a response. I can't wait forever to begin designing this application so any help would be appreciated.

Thanks!


[This message has been edited by tbaxter (edited 12-28-2000).]

[This message has been edited by tbaxter (edited 12-28-2000).]

 

[tbaxter]

Okay, I've decided to change things just a bit from my above scenario to make things MUCH easier.

Rather than hinge the ID on the type of equipment AND the unit, I just want to base it on the unit. In other words . . .

The number assigned would be the 2-letter abbreviation for the unit plus a 3-digit sequence number. If Unit1 (AD) has 5 events "on the books" and the user needs to enter the 6th, I want Access to automatically fill in AD006 when they select "Add New Type (X) Event" from the menu, and select Unit 1 (AD) from the drop box on the form.

When the user selects 1, for Add New Event, then 2, for New Type 2 Event, can Access "know" to then place "AD" as the 1st two characters of the EventID based on the user's selection of Unit 1 from the drop-down on the form and then search for the last number in sequence that was used for AD and assign the next unassigned number (006)?

BTW, I've been granted permission and funds to attend the Access course I've asked for. It starts in about 1-1/2 weeks but I'm sure it'll be awhile before we even get to this type of scenario!

Thanks!