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

Comments

Post a Comment

Popular posts from this blog

python - pip install -U PySide error -

-
does know how avoid following error when running pip install -u pyside per official website instructions here: https://pypi.python.org/pypi/pyside/#installing-pyside-on-a-mac-os-x-system note have done brew install qt successfully. you using pip version 7.0.3, version 7.1.2 available. should consider upgrading via 'pip install --upgrade pip' command. collecting pyside using cached pyside-1.2.4.tar.gz installing collected packages: pyside running setup.py install pyside complete output command /applications/anaconda/bin/python -c "import setuptools, tokenize;__file__='/private/var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-build-cpxemt/pyside/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-0r7hri-record/install-record.txt --single-version-externally-managed --compile: ...
Read more

arrays - C++ error: a brace-enclosed initializer is not allowed here before ‘{’ token -

-
simple question: i'm trying initialize array within c++ class declaration: using namespace std; #include <string> class myclass{ public: string myarray[] = {"a","b","c"}; }; and i'm getting error: error: brace-enclosed initializer not allowed here before ‘{’ token no, without complied c++11 compiler, cannot initialize member array in declaration. have initialize array member in constructor. , don't use open array if know number of elements initialize array.
Read more

cytoscape.js - How to add nodes to Dagre layout with Cytoscape -

-
i trying add nodes dagre graph through cityscape.js. problem nodes not painted. code have: cy.add({ group: "nodes", data: { id:"n0",name:"n0",weight: 75 }, }); $.ajax(url).done(function(jsontree){ var newnodes=[]; for(var i=1;i<6;i++){ //var child = jsontree.result[i]; newnodes.push( { group: "nodes", data: { id: "n"+i, name:"n"+i}}, { group: "edges", data: { id: "e"+i, source: "n0", target: "n"+i } } ); } cy.add(newnodes); any ideas? have used loaded function reload graph not work. not know if have use specific function reload graph. thank in advance. you need add positions new nodes. cytoscape.js docs : it important note positions of newly added nodes must defined when calling cy.add(). nodes can not placed in graph without valid position — otherwise not displayed. yo...
Read more