Solved

Limit Drop Down Values Based on Field Values in Access 2007

Posted on 2013-12-03
4
848 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Finding a closest match in Excel 7 38
Modal form 11 29
Access Crosstab Query with Multiple Values 4 32
unable to create table-based data macro in MS ACCESS 2013 11 23
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now