incrementing a field on a continuous form

[bkberg05]

Hi - I have a field called Sequence on a continuous subform. The Sequence field belongs to a table called Project_Draw. This table has many 'Draw' records for each 'Project' record. So then the subform is tied to the main form by Project_ID.

I want the Sequence field to automatically increment each time I add a new record. I can't use the autonumber as I need to start a new sequence for each new Project_ID.

I've tried putting a max([Sequence]) in the subform's footer and that gives me the max sequence used so far. Then on the Current Record event, I've tried referencing this and adding 1 to it and putting that result in the Sequence field. That works fine if I've come to the record for the first time, but if I'm entering one record after another, the max([Sequence]) field in the footer doesn't seem to refresh in time, so I don't get an incremental value. I've also tried requerying the max field, but again with no luck.

I'm sure there must be an easier way to do this, but I can't figure it out.

Thanks in advance for your help.

Bill

[NeoPa]

Try using DMax() instead Bill. Let us know if you need more precise assistance (It sounds like you're pretty well up on the concepts actually).

[bkberg05]

Hey NeoPa and thanks. I'm pretty bad with syntax. I tried the dmax, but am not getting the syntax right.

The first argument is the field name, which I put in brackets as [Sequence]

The second argument is the domain, which I took to be the table. The table is called Project_Draws, but I don't know how to do the syntax for this.

The third argument is the condition. I thought it would have to be the maximum sequence where the Project_ID of the subform was equal to the Project_ID in the Project_Draws table. But again, I'm not sure how to write that.

Also, am curious why dmax might work where max wouldn't.

Thanks,
Bill

[NeoPa]

DMax() stands for Domain Max. It's a Domain Aggregate function. You can specify the domain (including the criteria) within which it is aggregating. Max() simply aggregates across the domain already specified. It's used in various different situations and always refers to that situation exclusively. On a form it refers to the Max found within a specified control (not field I believe) on that form. It can be referenced in a control formula, but I'm not sure it can be within the VBA.

I know you have a table :
Table=[Project_Draw]

Field       Type    Index
Project_ID  Number  Composite PK
Sequence    Number  Composite PK
...

I assume you also have a controls called [Project_ID] and [Sequence] on the form in your SubForm control. Read that last bit carefully. It is how sub-forms work. The main form has a SubForm control, within which is held a form. I suggest for your form's (that is the form within the SubForm control) AfterUpdate event some code similar to the following :

Private Sub Form_AfterUpdate()
    With Me
        .Sequence.Default = DMax(Expr:="[Sequence]", _
                                 Domain:="[Project_Draw]", _
                                 Criteria:="[Project_ID]=" & .Project_ID) + 1
    End With
End Sub