vba - In Access 2010 how would I find all of the null fields in my table and replace them with user supplied data? -


i have access 2010 database going import data excel sheet , append data proper table. issue having once imported, data not have customer id attached cust_id field in table null. need macro ask user customer each import , find customer's id , put value of null fields. have made lot of different attempts @ (mostly though macro builder don't know start vba this) none of have shown results. think latest 1 close reason isn't running after import. have "after insert" macro, , idea prompt user [customer name] look-up custid tbl_customers insert each of null fields. unfortunately after import data nothing happens , i'm not sure why.

parameters name: customer name  record in tbl_customers condition =[tbl_customers].[fld_customer_name]=[customer name] alias custname  record in tbl_customers     condition =[tbl_customers].[fld_customer_pk_id]=[custname]     alias custid  each record in tbl_contacts condition =isnull([fld_contact_fk_customer_id])  editrecord     setfield         name fld_contact_fk_customer_id         value =[custid] end editrecord  

another possibility thinking have column in excel sheet customer's name , access have cust_id based on column. issue there don't know how tell access import customer name excel sheet find customer's id , insert id proper table, why leaning toward first option.

as said in comments i've tried lot of different things i'm new vba/access , running out of ideas try. not sure easier/better ideas/suggestions/recommendations appreciated.

here sample of might going in right direction. in case had similar situation, importing excel, 1 piece of data missing. did had "users" save excel docs subfolders customer id's part of folder names. using scripting library can recurse through subfolders, , use custom findcustomerid function parse id looking path name. once import happens, customer id can added in.

function importblah(importdir string, tbl string)  dim importpath string dim fs scripting.filesystemobject dim fo, x folder dim fi file dim custid string  set fs = createobject("scripting.filesystemobject")   fs     set fo = .getfolder(importdir)     each x in fo.subfolders         each fi in x.files             if not (fi.attributes mod 8 >= 4) 'if not system file                 docmd.transfertext acimportdelim, "import_specs", tbl, fi, true                 custid = findcustomerid(x.path)                 currentdb.execute "update " & tbl & " set filename = '" _                     & fi.parentfolder.name & "\" & fi.name & "' filename null", _                     dbfailonerror 'add filename filename column                 currentdb.execute "update " & tbl & " set custid = '" & _                     custid & "' custid null", dbfailonerror 'add custid custid column                 .movefile fi, x & "\imported\"             end if         next     next end     end function 

this code requires reference microsoft scripting runtime enabled.


Comments

Popular posts from this blog

How to remove text and logo OR add Overflow on Android ActionBar using AppCompat on API 8? -

html - How to style widget with post count different than without post count -

url rewriting - How to redirect a http POST with urlrewritefilter -