• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

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
0
spaced45
Asked:
spaced45
  • 2
  • 2
1 Solution
 
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
 
Rey Obrero (Capricorn1)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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now