I will explain the procedure of open and save invoice
first
Myrec1.Open "Select * From Bill order by invoiceno", DB, adOpenStatic, adLockOptimistic


so after that the user will click new invoice and after he finsh he click save and it willbe like these

Myrec1.AddNew
End If
Myrec1!Type = 3
.
.
.
Myrec1!invoiceno= GetMaxNo("bill", 3, "Invoiceno", Cur_Branch)
myrec1.update



---------------------
Function GetMaxNo(TbName$, type1$, Fname$, comb As String) As Long
If LastNo.State = 1 Then LastNo.Close
LastNo.Open "select Max(" & Fname$ & ") From " & TbName$ & " where left(hdate,4)=" & Format(Cur_Date_H, "yyyy") & " and branchid='" & comb & "' and type=" & type1, DB, adOpenDynamic, adLockOptimistic

GetMaxNo = Cur_Branch_Code & "" & Mid(Cur_Date_H, 3, 2) & "000001"
If LastNo.RecordCount > 0 Then
If Not IsNull(LastNo.Fields(0)) Then
GetMaxNo = Val(LastNo.Fields(0)) + 1
End If
End If
Set LastNo = Nothing
End Function
------------------------------------
So, these what i use and its work fine in 90% but if there many users enter new invoice make these probelm