vba - Need SQL to update table with data from another table IF numbers match -

i have function below letting user choose excel file , imports data table(mytable). single column excel file. table imports contains 2 columns(f1,f2).

i need docmd.runsql command following info 2nd column.

mytable.f1 oem part number need take number , compare 2 columns (oempartnumer, oemsub) in (jdsubs) table have setup if finds match need compare 2 matches (jdsubs) table , try find in (ami) table in column (oemitem) if finds match need return value column (item) table (ami) , insert (mytable) column (f2)

table contents example

mytable ---------------- f1       | f2 ar77530  |  ar12345  |  jdsubs --------------------------- oempartnumer    | oemsub ar65123         | ar77530 ar12345         | ar56242  ami --------------------------- item            | oemitem amar77530       | ar77530 amar56242       | ar12345 

so number being imported excel file 1 of 2 numbers(sometimes there no sub number)

i need match companies part number (ami) oem number

here function importing worksheet mytable need f2 column filled matching ami numbers , export out

 sub import()     dim fdialog office.filedialog     dim varfile variant     dim customerfile string     dim luser string      set fdialog = application.filedialog(msofiledialogfilepicker)     fdialog     .allowmultiselect = false     .title = "please select oem part number file."     .filters.clear     .filters.add "excel spreadsheets", "*.xlsx"     .filters.add "excel spreadsheets", "*.xls"     .initialfilename = "c:\users\" & luser & "\desktop"     if .show = true           'loop through each file selected , add list box.           each varfile in .selecteditems              customerfile = varfile           next     end if     end     docmd.transferspreadsheet acimport, acspreadsheettypeexcel9, "mytable", customerfile, false, "sheet1!a:a"     docmd.runsql ?????       exit sub  end sub 

also, have form setup user can 1 number @ time. here function it. need have automated process it

function fnsearchandpopulate() boolean dim d dao.database, r dao.recordset, strsql string set d = currentdb if me.txtenternumber.value = ""     msgbox "please enter number", , "error"     exit function end if strsql = " select * jdsubs inner join ami on " & _          " ami.oemitem=jdsubs.oempartnumber " & _          " jdsubs.oempartnumber= '" & txtenternumber.value & "' or " & _          " jdsubs.oemsub= '" & txtenternumber.value & " debug.print strsql set r = d.openrecordset(strsql) if r.eof    msgbox "oem # " & me.txtenternumber & " not exist!", , "no ami #"   set d = nothing   exit function end if 'get here if there record r.movefirst 'populate whatever textboxes me.txtaminumber = r!item me.txtdescription = r!description me.txtoemsubnumber = r!oemsub  set d = nothing exit function end function 

this can solved 2 update queries different joins. also, recommend using currentdb.execute instead of docmd.runsql because execute doesn't throw warning pop boxes:

currentdb.execute      "update      (mytable inner join jdsubs on mytable.f1 = jdsubs.oempartnumber)      inner join ami on jdsubs.oempartnumber = ami.oemitem      set mytable.f2 = [ami].[item];"  currentdb.execute     "update      (mytable inner join jdsubs on mytable.f1 = jdsubs.oemsub)      inner join ami on jdsubs.oemsub = ami.oemitem     set mytable.f2 = [ami].[item];" 

does work?