Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Limit Drop Down Values Based on Field Values in Access 2007

Posted on 2013-12-03
4
Medium Priority
?
865 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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