Microsoft Access - Showing all record numbers, then increment by 1 for new row

Asked By Desiree on 22-Aug-12 08:24 PM
I have a form and a sub form.  Parent Child relationship is on record_id.  Form table name is t_responses, subform table name is t_responses_choices.  When I type in an employee ID on the main form, the sub form brings up all the choices an employee has made.  Each record has a choice_id associated to each choice.  There may be many choices or zero choices.  I would like each choice shown in the sub form with their respective choice_id, then when a new choice is entered the choice_id auto increments by 1.  So if the employee has 6 choices, all 6 will show on the sub form numbered 1, 2, 3, 4, 5, 6, then a new record will auto populate choice_id with 7 and so on.
Pat Hartman replied to Desiree on 22-Aug-12 10:26 PM

When you assign your own sequence number, you need to use a two-part PK.  Something to indicate the "group" and the second field is the sequence number.  To assign the next available sequence number, use something like the following in the form's BeforeUpdate event.  In your situation, the FK could be the first part of the child table's PK.

If Me.NewRecord Then
  Me.SeqNum = Nz(DMax("SeqNum", "YourTable", "TheGroupID = " & Me.TheGroupID),0) + 1
End If


If you want the sequence number to appear as soon as someone starts typing, you can put the code in the BeforeInsert event.  This is more risky though since there is more of a chance of two forms generating the same sequence number if you have a busy environment.  If there is any real risk of conflict, you will need to trap the duplicate key error, increment the sequence number and attempt to reinsert the row.  Having the number generated in the BeforeUpdate event puts it closer to the actual time when it will be physically added and so reduces the potential for conflict although it doesn't eliminate it.