AutoNumbering Using Other Field

[mous]

I've searched the forum for this and although I've found similiar requests and responses, cannot find one to exactly match what I would like.

I have a table tblTimetable that has an autonumber field along with a code (MATGC-?????).

What I would like is a new field that increments based on the first five characters of the below code field. So for example,

Autonumber Code MyIncrementNumber
1 MATGC-PPPPP 1
2 ILKS2-PPPPP 1
3 MATGC-SSSSS 2
4 MATGC-TTTTT 3
5 ILKS2-EEEEE 2

MyIncrementNumber increments +1 based on the first five characters of the course code.

Ideally I would like this be generated at run time i.e. when the user types data into the table. But I will settle for a query to update the whole table on a regular basis.

Does anyone know if this can be done.

Many Thanks

Dawn

 

[FoFa]

Yes, but your best option IMO is to keep these fields spererate, than every time a new course code is entered (based on whatever criteria) than get the max or the count of those already existing, and add 1 to it. You just have to use the same criteria you are using in the add. So using your example if another MATGC was added, you could LongVar1=DCOUNT("CourseCode","mytable","somecriteria") and it would return 3, than you can add 1 to that for the new one of 4. Anytime you need to display this whole code, just put it together. If count won't work, you could use DMAX instead, but the advantage to count is if one does not exist, it returns zero so you add 1 and get 1.

Just a thought.

 

[mous]

Thanks for your reply. I'm not very good a VB. This is my code I have created.

Me.CelNotes = DCount("[CelCourse Code]", "tblTimetable", _
"Left([CelCourse Code],5) = 'Left(Forms![frmCourse]![FindCourseCode],5) ' ")
Me.CelNotes = Me.CelNotes + 1

where CelNotes is the field I want to update with my incrementing value.

All it does is place a 1 in the CelNotes field. It does not increment.

Regards

Dawn