reference - Editable cross-tab query in access 2010 form (how to improve code) -

currently based on advice experts, in 1 form try present editable cross-tab query form displaying values in several columns flat table (the data financial data various periods = ffi_period each financial statement item =finstmtitem). first denormalize table retrieving required values table onto unbound fields on form (via recordset.findfirst method , assigning value table field onto unbound text field) , save fields (via recordset.edit/.add method) table in normalized form.

one piece of code keeps repeating several times in procedure. here example of retrieving data flat table denormalizing (*prevperitem*x - name of unbound text field on form):

set myr = currentdb.openrecordset("tbl_ffifinancials") myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 1") prevperitem1 = myr.fields("amount").value  myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 2") prevperitem2 = myr.fields("amount").value  myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 3") prevperitem3 = myr.fields("amount").value  '.... [similar codes repeats here no 4 throu 16]............   myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 17") prevperitem17 = myr.fields("amount").value  myr.close  set myr = nothing 

similar code edit , add method (putting normalized data)

 set myr = currentdb.openrecordset("tbl_ffifinancials")  'updating values previous period  msgbox "updating values database period - " & intprevyear & "-q" & byteselectquarter  myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 1") myr.edit myr.fields("amount").value = prevperitem1 myr.update  myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 2") myr.edit myr.fields("amount").value = prevperitem2 myr.update  myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 3") myr.edit myr.fields("amount").value = prevperitem3 myr.update  '....[repeating code values no 4 though 16]  myr.findfirst ("[ffi_period] =" & intffiperiod1 & "and [finstmtitem] = 17") myr.edit myr.fields("amount").value = prevperitem17 myr.update  myr.close  set myr = nothing 

i believe there way of optimizing code using shortcut methods while loop or next methods. novice in vba programming feel hard grasp how it, , have repeat code line several times.

---

update: tried tried 1 achive optimization of code retuns mismatch error on new custom function:

dim myr dao.recordset set myr = currentdb.openrecordset("tbl_ffifinancials", dbopendynaset)  byteitemnumber = 1 17 step 1 myr.findfirst "[ffi_period] =" & intffiperiod1 & "and [finstmtitem] =" & byteitemnumber me.myfieldname(name).value = myr.fields("amount").value  next byteitemnumber 

.... new custom function reference textfield name on current form ....

function myfieldname(name field) dim strname string  srtname = "prevperitem" & byteitemnumber name.name = strname  end function 

found solution elsewhere. states following: field on form can referred 1. me.yourtextboxname.value=myr.fields("amount").va lue

2. me(textbox).value =myr.fields("amount").value

the second 1 useful if have series of textboxes , value assigned dynamically if have 17 textboxes named txtbox1,txtbox2 , txtbox3 ... textbox17 then

for = 1 17    me("txtbox" & i).value=myr.fields("amount").value next