Using Dmax For Numbering

[bama4954]

Hello, I have a subform that I want to enter a number each time I enter a new record. I have it working i.e. say on 4098544.02 it will number 01, 02, 02, etc. However if I change to a different record say 30499000-01 then I want it to restart the numbering sequence again from number 01. This is what I have so far, I am just starting out in VBA. Please be patient with me lol

Private Sub cmdSubAddNew_Click()
DoCmd.GoToRecord , , acNewRec
Dim WorkOrderNbr As String
WorkOrderNbr = Form_frmIWLEntry.WorkOrderID
If Me.NewRecord Then
Me.ItemNumber = Nz(DMax("ItemNumber", "tblIWL"), 0) + 1
End If

End Sub

 

[James006]

Hello,

You should probably post your customized Function named : Nz()

HTH

 

[stumac]

You are going to a New record every time, so you can take out the if statement. You also need to add in criteria to your DMAX to only find item numbers relating to the parent record, maybe something like:

Private Sub cmdSubAddNew_Click()
DoCmd.GoToRecord , , acNewRec
Dim WorkOrderNbr As String
WorkOrderNbr = Form_frmIWLEntry.WorkOrderID
Me.ItemNumber = Nz(DMax("ItemNumber", "tblIWL", "WorkOrderID = '" & WorkOrderNbr & "'" ), 0) + 1
End Sub

You are right to use the NZ function for this as it would error if no child record exists.

 

[xenou]

You show two different types of values in your first post:

4098544.02
30499000-01

Are you using decimal points or hyphens?

Are your values text or numeric?

Many issues here - max works differently for text or numbers. You cannot use "DMax(...) + 1" if this is Text (addition is for numbers, not strings). Decimals are not good for ID's. If you have only 01, 02, ... 99 you are limited to 99 possible new "numbers".

(@James006 note that Nz() is a system function in MSAccess)

 

[stumac]

I assumed ItemNumber is a number and WorkOrderID is a string (due to the '.' & '-' being shown as example data).

It looks like each work order has a number of associated items on the subform.

 

[xenou]

No problem. 30499000-01 is definitely not a number (unless its 30498999) so I guess I remain uncertain.

 

[stumac]

30499000-01 is a work order ID - which I treated as a string.

Either way it does seem strange having 2 formats if it is a string, perhaps a user entered value... if that is the case I would consider adding an Auto number PK field and using that to link to the child records.

 

[bama4954]

WOW! You totally nailed it. Works like a charm! Thanks for your help

 

[bama4954]

Thanks everyone for your help. Looks like stumac nailed it!

 

[xenou]