incrementing a compound primary key field in a subform

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I have a subform based on a table that has of a two-field primary key
(LicenseeID and AgentID).

The subform is linked to the main form through the LicenseeID fields. My
question concerns how to handle the AgentID field when I add a new record in
the subform.

When I add a new record in the subform, the Licensee ID field will
automatically be set to the Licensee ID in the main form. For the AgentID
field, I would like to set it equal to the highest existing AgentID field
plus 1. I tried putting the formula "=Max("AgentID")+1" in the Default
Value property of the AgentID field, but that didn't work, because when I
add a new record, the AgentID field continues to display the value that was
in the last record, and doesn't add the 1.

So how do I get that field to take the value of the maximum existing AgentID
field + 1 when I add a new record in my form?

(If possible, I'd prefer to do it with a simple formula rather than using
DAO or ADO to accomplish this).

Thank you in advance.
 
Instead of Default Value, assign a value to the AgentID in the form's
BeforeUpdate event. This is the last possible moment the value can be
assigned before the record is saved, and so will help reduce concurrency
issues if you have mulitple users entering records.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord Then
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Me.Undo
Else
strWhere = "[Licensee ID] = " & Me.Parent![Licensee ID]
Me.AgentID = Nz(DMax("AgentID", "MySubformTable", strWhere), 0)
+ 1
End If
End If
End Sub
 
Thank you again, Allen.

Another question - Larry Linson pointed out to me in another thread that the
After Update event will fire off whenever I update a record. In this case,
I only want it to run when I add a record for the first time. So I would
think that I should use the code you wrote with the Before Insert event.
Would you agree with that?

Paul
 
No: The code I suggested only runs if it is a new record:
If Me.NewRecord

You want to delay the assignment to the last possible momemt, in case 2
users are adding records at the same time. Form_BeforeInsert fires at the
beginning of the insert process: if a user begins a record, and gets
distracted by a phone call before she saves, another user will get the same
value. Then when the original user resumes, their record cannot be saved.
 
Ok, so does that mean I could use the After Insert event?

(Again, what I'm trying to do is to run a procedure if and only if I add a
record to the table that's the record source for the form).
 
Paul James said:
Ok, so does that mean I could use the After Insert event?

(Again, what I'm trying to do is to run a procedure if and only if I add a
record to the table that's the record source for the form).
Click to expand...

AfterInsert is too late. The record has already been saved at that point.
 
My thanks to Allen and Rick for their help and suggestions on this
situation.

Paul
 
Back
Top