Increment a numeric text box by 1

[Skip Bisconer]

My form is based on a table.


The form updates table fields by user as they assign a Route ID and a Delivery Sequence to each order selected.
I have one text box that updates a route field with a letter A through F, and one text box that is currently manually entering in a sequence number for each order number selected or entered by the user. The record (Order Number) is selected through a combo box with the row source from the table field Order Number.

This works well, but what I would like to see happen is the Delivery Sequence number to automatically advance 1 number from its last number for every change in the combo box, to eliminate the manual entry, then reset back to 1 when the Route ID letter is changed then begin the process again.

Can someone give me an idea of how to accomplish this?

 

[pbaldy]

One way might be a DMax, with order number and route ID as criteria. Wrap that in an Nz function to account for the first one, and add one to the result.

 

[Skip Bisconer]

Thank Paul.
I'm sorry I am so dense but I have viewed the Dmax before and I can't understand now to apply it. Could you give me an example on how to use it in this situation?

 

[pbaldy]

At the appropriate point (perhaps after the route ID is entered?):

Me.IncrementingField = Nz(DMax(...), 0) + 1

Here's a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[Skip Bisconer]

Thanks for the help Paul,

So far I can't Dmax to work for me here as I have to reset the value to 0 when the Route letter changes and its all part of the same table. I don't know if there is a way to make Dmax look at the Route letter to find the Dmax of the Sequence number?

 

[WayneRyan]

Skip,

On the BeforeInsert event of your form:

Me.Sequence = Nz(DMax("[Sequence]", "YourTable", "[RouteNumber] = " & Me.RouteNumber), 0) + 1

Wayne

 

[pbaldy]

What have you got so far? The link should show you how to add the criteria to the function.

 

[Skip Bisconer]

Thanks Wayne
I get "Wrong number of arguments" when I run this code.

PegRoute is an unbound text box on my form.
PegDelSeq is an unbound text box on my form.
DeliverySeq is the sequence number on my table
RouteId is the Route letter on my table.

I have additional code in the afterUpdate for the selected order number that makes the RouteID and DeliverySeq = the PegRoute and PegDelSeq.

Private Sub PegRoute_AfterUpdate()
PegRoute = Proper(PegRoute)
Me.PegDelSeq = Nz(DMax("DeliverySeq", "tblAssignedDailyOrders", "RouteID = " & Me.PegRoute, 0)) + 1

 

[pbaldy]

You've got the second argument of the Nz function inside the DMax function, plus if RouteID is a letter you have to change the DMax a little, as detailed in the link I gave you.

 

[Skip Bisconer]

Using this statement appears to do nothing. I don't get a result. The order gets assigned a Route letter but no sequence number. Do I have something out of place?

Private Sub Combo35_AfterUpdate()
On Error Resume Next

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[OEOO_ORDR] = " & Me.Combo35
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

'Have RouteID always retain the value of PegRoute
If Len(Me.RouteID & "") = 0 Then

Me.RouteID = Me.PegRoute

' Have DelDate always retain the value of PegDelDate
Me.DelDate = Me.PegDelDate

'Find last sequence number given and increment it by one.
Me.PegDelSeq = Nz(DMax("DeliverySeq", "tblAssignedDailyOrders", "RouteID = " & Me.PegRoute), 0) + 1
'Me.PegDelSeq = Me.PegDelSeq + 1 (Old way of doing it.)


Combo35.Requery

End If
' Find the record that matches the control.


End Sub

 

[pbaldy]

Can you post a sample db?

 

[Skip Bisconer]

Paul
I'll see if I can get the essentials small enough.

 

[Skip Bisconer]

Thanks for working with me on this.

I can't get the form and code down small enough to post. Have the increment working as I indicated in my code posting but is doesn't cover all the possible scenarios, such as, the user tries to add an order to a route A, as an example, if there has been entries to Route B. The user now will have to go back and manually enter the last Sequence number used for Route A. It sure would be nice to have the ability to look at the greatest sequence number for Route A or B etc which ever is pertinent without the user looking it up.

Thanks for you help so far. I know it is my lack of understanding the NZ and Dmax functions that are the cause. I think it is in the criterea for the NZ function that is my problem but I don't know how to state it.

My desire is this. When I enter a Route letter in me.PegRoute I need it to look at the table for the corresponding letter in

.[RouteID] column and give me the max sequence number in

.[DeliverySeq] column. Any suggestions would be appreciated.