AutoNumber by another field

[lisa1965]

I need some help, I need to create a form that when a user wants to enter a new record, the id will be based on what type of record they are adding.

For example, there is a list of training courses (A-##, B-##, C-##) where the ## is the training course incident. When the user wants to add a new record he is first selected for which training course (A,B,C) and the new record id will be B-(max(##)+1).

Does anyone have any ideas how this is possible? I tried using one form for the filter criteria of which training course but I am having trouble actually creating the ID once the course is selected.

Thanks,
Lisa

 

[Banana]

Can you elaborate exactly what trouble you are having with creating the field?

Are you trying to create a ID using calculation in a bound field or via VBA or default value? What code do you have in place? It seems that you already have the right general idea; maybe you need to check for syntax or something like that?

 

[lisa1965]

AutoNumber database

I am attaching my database objects.

My form trnCourseCodesTable has a button to add new record, that button calls another form called filterCourseTitle. But my code is not matching my logic.

What I want to happen is when the user select the add new record button, he then selects which course they are adding and the program computes what the course number is "AA-(Max(##)+1)

 

[Banana]

I want to be sure-

Did you make a relationship between M_Courses and TrnCourseTable? In the sample, there's nothing, but I"m not sure if this is because of an error in importing/modifying/stripping.

You might want to add a combobox to select M_Courses (which seems to represent categories of classes). That will get us the letter code.

Then do a query that does Max() of Course with the criteria checking for same letter then add one. Now, combine the letter from the combobox with the number from the query. Be sure to lock the textbox holding the course code so it's not editable by user.

Hope that helps.