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

Post a Comment

Popular posts from this blog

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

have researched death , cannot find answer. i have actionbar using appcompat. supporting api v7 (api v8 do). actionbar works api v11. api < v11 has problem of removing icon (and feature) actionbar without supplying overflow. big picture have app logo, app title, , 3 icons squidging same actionbar on low end phone. trying make room! i happy 1 of 2 solutions. either: a method of getting overflow functionality can retrieved pull down. (preferred) method of removing icon , text actionbar below current xml api 11+: <resources> <!-- base application theme api 11+. theme replaces appbasetheme res/values/styles.xml on api 11+ devices. --> <style name="appbasetheme" parent="theme.appcompat.light.darkactionbar"> <item name="android:actionbarstyle">@style/liteactionbarstyle</item> <item name="actionbarstyle">@style/liteactionbarstyle</item> </st...
Read more

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

i have border below li elements in wordpress widget types appropriate. element inside set block gives them nice easy click full width. the problem, when choose option "show post count" when adding widget such categories or archives displays post count plain text after tag , because tag block pushes post number text line below. whether choose show post count or not show post count still gives exact same element identifyer example "widget_categories". how style post count not on next line still keep links non post count list items full width? here's example on sidebar www.bbmthemes.com/themes/modular/blog-standard/ in order solve problem need remove display:block; for ul.widgets ul a , add in it's place line-height: 33px; what going achieve have same visual effect. what going lose doing links going work on text , not on "box". the other way out of find post count coming , make changes this. example change text count i...
Read more

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

i have question urlrewritefilter , until not find in net. i want redirect http post in tomcat7. here example... the call http post ulr http://localhost:8080/oldapplication/example?a=123&b=2 this call contains content either xml or json. filter configured works , urlrewrite.xml contains: <?xml version="1.0" encoding="utf-8"?> <!doctype urlrewrite public "-//tuckey.org//dtd urlrewrite 4.0//en" "http://www.tuckey.org/res/dtds/urlrewrite4.0.dtd"> <urlrewrite use-query-string="true"> <rule> <condition type="method">post</condition> <from>^(.*)$</from> <to type="redirect">/newapplication$1</to> </rule> </urlrewrite> in access log can see call http://localhost:8080/oldapplication/example?a=123&b=2 gets redirected http://localhost:8080/newapplication/example?a=123&b=2 fine until now. pr...
Read more