Auto Numbering on SubForms 1

[peterpcu]

Hi,
I have developed a small database to hold contracts information. There is a main form named 'Contracts' which has several tabs. One of the Tabs is named 'Documentation' and it contains a subform (named Documentation SubForm) to hold all the documentation associated with a contract - the number of records in the subform varies between contracts.

At present the autonumber on the subform simply increments each time a new document is added. I would like to start the document auto numbering from '1' for each contract.

I have tried the before update event with:
Me.DocInc =Nz(DMax("[DocInc]","[tblDocumentation]","[DocID] = " & Forms!Contracts!DocInc),0)+1
but there seems to be something wrong with the way I am naming the controls on the subform

Any Ideas?

Regards

Pete

 

[TheAceMan1]

How are ya peterpcu . . .

[blue]I would like to start the document auto numbering from '1' for each contract.[/blue]

You can't use Access autonumber for this, and [blue]DocID can't be a primarykey[/blue] (duplicate key violations)!

If [blue]DocID[/blue] is a primarykey, you'll have to change it's Data Type to [blue]Number[/blue] & Field Size to [blue]Long Integer[/blue]. This done, the table still needs a primarykey . . . so add one. Be aware: anywhere you reference [blue]DocID[/blue] [purple]as the primarykey[/purple], you'll have to switch to the newly added.

With the above taken care of, copy/paste the following to the [blue]Before Update[/blue] event of form [blue]Documentation SubForm[/blue]:

[blue]   Dim Cri As String
   
   Cri = "[DocInc] = " & Forms!Contracts!DocInc
   
   If Me.NewRecord Then
      Me.DocID = Nz(DMax("[DocID]", "[tblDocumentation]", Cri), 0) + 1
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886