Creating a Concatenated Code from another field value with unique number In Access VBA -

i generating supplier form, add supplier_name , supplier_code auto-generated taking first 2 characters of supplier_name , adding unique number when 2 suppliers have same first 2 characters e.g.:

• sundriessupplier 1 = su01;
• sugarsupplier 1 = su02

i new vba , tried following doesn't work:

private sub supplier_name_afterupdate()     dim db database     dim rs recordset     dim sql string     dim var1 string      var1 = left(me.supplier_name.value, 2)      sql = "select supplier_id, left(supplier_name,2) charsupplier, count (supplier_name) countsupplier " _         & "from suppliers " _         & "where charsupplier = var1 " _         & "order supplier_id"       set db = currentdb      set rs = db.openrecordset(sql, dbopendynaset)      me.supplier_code = var1 & format$(rs!countsupplier, "00")      end sub 

if can or suggest alternative method great thanks.

edit: think flaw in approach may if on new record, supplier_id not saved table , not available query?

here started based on button called cmdaddnewsupplier. should have enough adapt example needs. (sorry, edited because forgot mention example code using fictitious table called [suppliers] fictitious column name of [supplier_name]; you'll need replace name of table , column.)

private sub cmdaddnewsupplier_click()   'todo: create error handling   'todo: check null value of suppliername textbox; notify user , exit if null   'todo: check less 2 chars supplier name; notify user , exit if it's less 2 chars (seems impossible, can happen)   dim strsupcode string 'the eventual unique id of new supplier    'make sure user wants add supplier if name exists   'all doing utilizing dlookup don't have deal recordset object   if not isnull(dlookup("[supplier_name]", "[suppliers]", "[supplier_name] = " & chr(34) & me.supplier_name & chr(34)))     if msgbox(me.supplier_name & " exists!" & vbcrlf & "are sure want add them?", vbyesnocancel or vbquestion, "please confirm") <> vbyes       exit sub     end if   end if    strsupcode = getsuppliercode(me.supplier_name)    msgbox me.supplier_name & vbcrlf & strsupcode 'test out make sure it's working before doing real end sub  function getsuppliercode(strsuppliername string) string   dim nloop long   dim strcode string    strcode = ucase(left(strsuppliername, 2))    'the supplier name unique or user means add supplier same name   nloop = 1 100 '100 same names unlikely, eh?      'create temp supplier code starting 1 , increment     'the return value being set, have jump out of function when unique found     getsuppliercode = strcode & format(nloop, "00")      'todo: utilize dlookup check existence; leave part you;     'dont forget looking @ code , not name here did in button click function      '      if isnull(dlookup(<enter required parameters>))     '         exit function 'jump out of function because 1 should next unique     '      end if    next  end function