Because you're fetching maximum among where BL_Lno is from provided value.
and in the your scenario you need to find MAX from all record.
See if
BL_Lno
having a value of
9 and you're incrementing it with
10, But do you know that there's alerady a record for ID
9 for other
BL_LnoCombo
?
If you want to generate the incremental no then don't go with custom generation, USE
Auto Increment column[
^] that will take care itself.