Using Dmax For Numbering

bama4954

New Member
Joined
Oct 31, 2018
Messages
35
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hello,

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

HTH
 
Upvote 0
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:

Code:
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.
 
Upvote 0
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
No problem. 30499000-01 is definitely not a number (unless its 30498999) so I guess I remain uncertain.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top