Find the greatest value in a recordset and prevent the user to enter the wrong number

Question

I have a form that operators key details of insurance policies in it. The insurance policies have serial number and should key in one after the other in the correct order but sometimes the operator forget a number and enter the next one. I want to write a code and prevent the user to forget a serial number and alarm him that a number is forgotten and he /she should enter the correct one.

The insurance numbers have a complex structure. Their structure is as follow:

The agent number+Insurance Type Number/Year/Serial Number like this

3121520/2016/120

I want that for example when the last Fire insurance policy that keyed in table is 3121520/2016/120 and the operator want to key in 3121520/2016/122 the application alarm and say that you forgot to enter 3121520/2016/121.

How can I do that?

---

Karim Vaziri Regards,

Answer 1

Hi Karim,

You can typically use the DMax() function for something like this but it will be easier if the Serial Number is stored in a separate field. For example:

DMax("SerialNumber", "TableName")

However, it might still be possible to do it with maybe something like the following:

DMax("Right([FieldName],3)", "TableName")

Hope it helps...

Answer 2

3121520/2016/120

I want that for example when the last Fire insurance policy that keyed in table is 3121520/2016/120 and the operator want to key in 3121520/2016/122 the application alarm and say that you forgot to enter 3121520/2016/121.

Hi Karim,

An alternate approach could be to store the information for the Insurance policy in separate fields, viz.  client number(?) 3121520, year, sequence number 120.

In any report or form you can display the whole number by concatenation using "/" as connecting character.

Imb.

Answer 3

The simple solution is: don't let the operator enter it at all. Since the number is predictable, you can calculate it in code using the "DMax+1" technique. This is a FAQ, so you should not have any problem locating relevant articles with your search engine.

---

-Tom. Microsoft Access MVP

Answer 4

I assume that the serial numbers are distinct per year per insurance type, not overall.  In which case I would recommend that, for a structured key like this, the individual elements are stored in three separate columns, either defined as the composite primary key or otherwise included in a single unique index.  You can then compute the serial number element when a row is inserted via a form.  In a multi-user environment you need to cater for possible conflicts if two or more users are inserting a row simultaneously.

You'll find an example of how to do this in CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the option for 'Sequential Numbering by Group' is the appropriate model to follow.  My demo numbers rows sequentially by a single column Gender, by calling the following function:

Private Function GetNameID(strGender As String)

    strCriteria = "Gender = """ & strGender & """"
    
    GetNameID = Nz(DMax("NameID", "NamesByGender", strCriteria), 0) + 1
    
End Function

In your case you would need to pass the values of the insurance type and year elements into the function as two arguments, and build a string expression which includes both in a Boolean AND operation as the value for the module level strCriteria variable.

Any conflict between users in generating the number is handled and corrected in the form's Error event procedure.

---

Ken Sheridan, Stafford, England