Changing validation rule in an Access form, based on user input? -

• using access 2010
• i know minimal amount of vb

i have table called tblrawmaterials contains 2 important fields: partnumber , diameter.

i using form fill in different table various information. important information being: partnumber , tensilestrength

in form allow user pick partnumber combo box row source tblrawmaterials. must enter tensile strength standard text box.

i need effect of validation rule, changes acceptable range (in form) tensilestrength based on diameter of part number selected in combo box.

eg: user selects partnumber 000001 diameter of 2" , acceptable tensile strength >150. user selects partnumber 000002 diameter of 6" , acceptable tensile strength >130

i cannot use cascading combo boxes because user needs enter decimal data tensilestrength box. have tried using dlookup() in expression builder , creating macros, keep getting stuck. help.

try adding column combobox diameter. be sure update column count , column widths accordingly. (1 column count , 1;0 widths)

be sure ts textbox formatted general number access can handle non numeric issues.

i wouldn't waste time on afterupdate, change, etc event validation ts textbox since may not enter @ time save record.

do validation right before code tries save data different table. i'd suggest creating validation function returns boolean. can check ts null value @ time. can refer new column in combo box.

function savetherecord() long    if dataisvalid = false exit function    'your current code save record other table  end function  function dataisvalid() boolean    dataisvalid = false 'default false can jump out of function if criteria not met    if isnull(txtts)     msgbox "please enter valid tensile strength", vbexclamation, "validation error"     exit function   end if    'txtts not null @ point    '1st senerio   if mycombo.column(1) < 2 'diameter < 2" (this new column diameter)     if txtts < 150       msgbox "please enter tensile strength >= 150", vbexclamation, "validation error"       exit function     end if   end if    'check other scenerios    dataisvalid = true 'if we've reached point, everyting must ok  end function 

you might want create label next ts textbox inform user of range limmitations they'll know before try save. can on combobox's afterupdate event. general example give idea.

private sub mycombo_afterupdate()   call showpartrange end sub  function showpartrange() long    'if logics based on mycombo.column(1)   lblrange.caption = "must > 150"   'etc  end function