Limit Drop Down Values Based on Field Values in Access 2007

I have a form that I would like to include a drop down control but I want to limit the values it displays based on the current record displayed. Each of the records that can be displayed contains a field I have called "DropDownItemMask". The field values all start with the letter "a" and then followed by a bunch of 1's and 0's. I was thinking that I could tie the position of the digit to the drop down values.

For example for the first drop down value if the mask for the current record contains a "1" in the second digit from the left from the "mask" then it would display, if it a "0" then it would not show and so forth.

I apoligize if I am not explaining this very well so to help I have attached and excel sheet showing the concept.  DropDownItemMask.xlsx
Thank you
LVL 1
spaced45Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
sub updateItems(strMask as string)
dim  j as integer, vMask as string
vMask=mid(strMask,2)

for j=1 to len(vmask)
    if mid(vmask,j,1)=1 then
       currentdb.execute "update tblItems set selected=-1 where id=" & j
       else
        currentdb.execute "update tblItems set selected=0 where id=" & j
    end if

next
me.comboBox.requery
end sub
0
 
Rey Obrero (Capricorn1)Commented:
this is doable.. you will need a table with 3 fields, id field autonumber,Item field, a YesNo field, selected and VBA codes. are you ok with that?

the rowsource for the  drop down will be

select [item] from tblItems where selected=true
0
 
spaced45Author Commented:
Capricorn1,

thank you for all the assistance. I do have one question. On what event should I place the vb code? Should it be on the OnCurrent event?
0
 
spaced45Author Commented:
Nevermind, I added it to the On Current Event and worked like a charm. Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.