| re: Increment by 1 if duplicate text value
rech340 wrote:
[color=blue]
> 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
>[/color]
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
| 
