Incremental serial number with criteria

[mlozano]

Hi
I have a table with two fields
NUMERO: which is a numeric field
LINEA: a text field to specify the line of business, such as RE, DA, MO, MF
NUMSP: a text field which is the primary key for this table.-

I also have a data entry form for this table, where the user first inputs the LINEA data via a dropdown list.

I want the form to set the value of NUMERO to the highest existing number +1 considering ONLY the records where the LINEA equals the LINEA the user has just selected.

I have set the value of NUMERO though a DMax function on the BeforeInsert event of the form, using the following code:

NUMERO = Nz(DMax("NUMERO", "CAB SOLICITUD") + 1)

... but of course this does not restrict the search of the DMax to only the records where LINEA equals the value of LINEA in the form. It does not matter that NUMERO will have repeated values as the primary key will be NUMSP which will include the LINEA value, so there will not be repeated values for this field

Can anybody help me to introduce the DMax criteria?

Many thanks

 

[pbaldy]

This should help you add a criteria:

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