Increment by 1 if duplicate text value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rech340 - 23 Jan 2006 21:34 GMT

I have a table with the following:

AutoNumber Course Name (a text field)

I want to add a Section Field that increments beginning with 001 all
the way to 999, for Each UNIQUE Course Name.

For Example:

Let's say I have a course Name in this table of "MS Word 2003 Level 1".
In the table, is there any way for Access to assign a Section #
automatically if the text value is duplicated.

Any suggestions would be greatly appreciated

Reply to this Message

salad - 24 Jan 2006 02:59 GMT

> I have a table with the following:
>
[quoted text clipped - 10 lines]
>
> Any suggestions would be greatly appreciated

I guess. This could be 1 to many. Create another table with the
tableid for the course and the section field number. Set a flag whether
or not it is a new record in the BeforeUpdate event of the form. If so,
use a For/Next loop to go add 999 Section records.

Or, you could change the CourseName field index to allow duplicates.
Then in the BeforeUpdate event for the form, add something like
    If Me.NewRecord Then
        Me.Section = NZ(Dmax("Section","YourTableName" _
            "CourseName = '" & Me.CourseName & "'") + 1
    Endif

Now, if you change a course name, you'd want to update all other course
names to the new course name

Reply to this Message