Set incremental value to a control depending on another control's value

[mlozano]

Hi guys


I am running Access 2007
I have an "orders" table with the following fields
linea: text
numero: numeric

I have a form with corresponding names for the controls

I need a piece of code which finds within the table orders the highest value for numero, where the value of linea equals the value selected by the user in the form via a combo box, and then sets the value of the control "numero" to that number +1
I know I have to use DMax but I am completely clueless as to the syntex or wherre to place the code (BeforeUpdate? AfterUpdate?)
I know there are hundreds of articles googling about but I just can't get it to work

Any help would be greatly appreciated

Thanks!

[mlozano]

I would like to add for clarity, that my purpose is simply for the DB to provide incremental order numbers based on tyhe different lines of business of my company
linea is the line of business (RE, DA, MO and so on)
numero is just the order number
Cheers

[Missinglinq]

This kind of thing is usually done in the Form_BeforeUpdate event. This is necessary in Multi-User environments, so that the auto-increnebted number is assigned at the very last nanosecond before the Record is saved. This cuts down the chance that two Records will receive the same number. It also means that the number is not visible until you return to the Record.

If the app is not going to be used by multiple-users, the same code, slightly modified, could be used in the Linea_AfterUpdate event, and it would show immediately.

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If Nz(Me.Linea, "") = "" Then
  
  Cancel = True
  MsgBox "You Must Select a Linea!"
  Linea.SetFocus
  Linea.Dropdown
  
 End If
 
   If DCount("*", "YourTableName", "[Linea] ='" & Me.Linea & "'") < 1 Then
     Me.Numero = 1
   Else
     Me.Numero = DMax("Numero", "YourTableName", "[Linea] ='" & Me.Linea & "'") + 1
   End If

End Sub

Replace YourTableName with the actual name of your Table
and replace the Fields names, if necessary.

Linq ;0)>

[mlozano]

Thanks Linq!

I found something that appears to be working,

Private Sub Form_BeforeUpdate(Cancel As Integer)
numero = DMax("[numero]", "pcabecera", "[linea] = Forms!nuevo!linea") + 1
End Sub

However your solution appear to be much more complete, esp. as it takes care of the "initial" values problem which is a relevant issua as we restart the counters every year. Also reminds the user they must be careful enough to select a "linea" first thing (mind you, that may be asking too much from my avg user

Many thanks again!

[Missinglinq]

Glad you've got it working. FYI, the lines of code

Numero = DMax("Numero", "YourTableName", "[Linea] ='" & Me.Linea & "'") + 1

and

numero = DMax("[numero]", "pcabecera", "[linea] = Forms!nuevo!linea") + 1

are identical in execution!

Me.Linea

is the shortcut method of writing

Forms!nuevo!linea.

Anytime you're referring to a Control on a Form, and you are in the code module of that Form

Me.

takes the place of the

Forms!FormName

Makes it much easier to write code than typing out the entire reference to the Form and Control. Also, if you enter Me. the Access Intellisense feature will drop down and start showing you the appropriate Control names on that Form as well as Properties that are related to the Form.

Linq ;0)>

[mlozano]

There is some further complication
My form contains the order header information and a subform with the order details (the items in the order)
After the users set the values of the order header, they go to the details subform, at that moment the order number is assigned.
However, if they go back to the header and then go back to the details, a new number is assigned, which is a problem because we then get gaps in the order number series.

Is there a way for the numbers to be assigned only if the value of numero is 0?

Many thanks

[Missinglinq]

You need to insure that the number is only incremented if you're dealing with a New Record. So you need to test for that before executing the code:

If Me.NewRecord Then
  'Whatever code you're using goes here
End If

Linq ;0)>