Solved

Limit Drop Down Values Based on Field Values in Access 2007

Posted on 2013-12-03
4
853 Views
Last Modified: 2013-12-03
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
Comment
Question by:spaced45
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39694536
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39694552
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
 
LVL 1

Author Comment

by:spaced45
ID: 39694596
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
 
LVL 1

Author Comment

by:spaced45
ID: 39694643
Nevermind, I added it to the On Current Event and worked like a charm. Thank you
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question