DMax with Criteria

[WallT]

I have a SubForm that I am trying to use DMax to automatically generate multple consecutive locationNumbers on the SubForm for each OrderNumber on the MainForm.

Private Sub Form_BeforeInsert(CANCEL As Integer)

Me.LocationNumber = DMax("[LocationNumber]", "TBLLocations", "[OrderNumber] =" & [OrderNumber]) + 1

End Sub

I keep getting an invalid syntax error...where does it appear I am going wrong on this.

 

[WallT]

Okay I think I am almost there if I can just get a little help. I changed it to the following, but when I am puting in a brand new record, it places 1 as the first location, but then when I tab to another field it make makes my LocationNumber Null.???

Private Sub Form_BeforeInsert(CANCEL As Integer)

Me.LocationNumber =
Nz(DMax("[LocationNumber]", "TBLLocations", "[OrderNumber] =" & [OrderNumber]), 0) + 1

End Sub

 

[WallT]

Wow, I did this one myself...never mind. I had contradictory code in another field I had tried earlier and never deleted...oops!