Last Record plus One

[Guest]

Hello.
I have created a two tables with a "One To Many" retationship and I have
created A Form with a subform. On the Parent table I have the fields
"Vendor" and "VendorNumber" and On the Child table I have "VendorNumber" and
"SPC#"

On the Parent Folder I have it set to Single and on the Subform I have it
set to Datasheet.

When I create a new record in the Subform I need the "SPC#" field to be
sequentially greater by 1.
For Example whether I create a new subform record for Vendor ABC or CDE
(currently the biggest number in the Child Table is 1159187) I need the new
record to have the SPC# default to 1159188.

How would you do it?

Iram/mcp

 

[Guest]

Use the SubForm OnCurrent event to check if you are entering a new record, if
so, check what is the last record and add 1 to it.

Something like

f Me.NewRecord Then
Me.[SPC#] = DMax("[SPC#]", "ChildTableName") + 1
End If

 

[John W. Vinson]

Hello.
I have created a two tables with a "One To Many" retationship and I have
created A Form with a subform. On the Parent table I have the fields
"Vendor" and "VendorNumber" and On the Child table I have "VendorNumber" and
"SPC#"

On the Parent Folder I have it set to Single and on the Subform I have it
set to Datasheet.

When I create a new record in the Subform I need the "SPC#" field to be
sequentially greater by 1.
For Example whether I create a new subform record for Vendor ABC or CDE
(currently the biggest number in the Child Table is 1159187) I need the new
record to have the SPC# default to 1159188.

I'd use the subform's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.[SPC#] = NZ(DMax("[SPC#]", "tablename")) + 1
End Sub

If this is a multiuser application this runs a certain amount of risk that two
users might have the form open simultaneously and get duplicate numbers; if
there are no other required fields in the child table, you can add a line

Me.Dirty = False

after the Me.[SPC#] line to force an immediate write to disk.

John W. Vinson [MVP]