469,946 Members | 1,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Combo: disable or grey out items in the list?

In Access 2003 I have a continuous form with a combo. I'm trying to find a way to change the displayed values of the combo depending on whatever value was selected in the combo in the previous record, whilst also displaying whatever value was seelcted in previous records, i.e. record one might have 5 as the value, which means the combo in the next record would include items 3,4 and 6; if the user then selects item 6, the next record would allow a choice of say 4, 6 and 10. I can't see a way to do this with the rowsource as any changes on one record are reflected in others, i.e. if I remove item 5 in record 2, then the field appears blank in record 1 (though the data is of course still there). I'm not too keen to work around it by placing a txt box on top of the combo as user loses the facility to go to an item by typing the first one or two characters; so I'm wondering whether anyone knows how to dim/grey out items in a combo as can be done with menus?

Many thanks
Sep 25 '07 #1
5 8557
FishVal
2,653 Expert 2GB
Hi, Kevin.

The solution may be the following.
Its rather simple but however it seems to work with combos where BoundColumn = Visible column only as it requires to set LimitToList property to No.

Tables:

tblItemNames
txtItemName Text, PK

tblItems
keyItemID AutoNumber, PK
txtItemName FK


Form:

RecordSource = "tblItems"

contains ComboBox [txtItemName]
txtItemName.RowSource =
Expand|Select|Wrap|Line Numbers
  1. SELECT tblItemNames.txtItemName FROM tblItemNames LEFT JOIN tblItems ON tblItemNames.txtItemName=tblItems.txtItemName WHERE tblItems.keyItemID Is Null; 
txtItemName.LimitToList = No

Form module:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.     Me.txtItemName.Requery
  3. End Sub
  4.  
Sep 25 '07 #2
FishVal
2,653 Expert 2GB
Accidently posted.
Sep 25 '07 #3
Hi, Kevin.

The solution may be the following.
Its rather simple but however it seems to work with combos where BoundColumn = Visible column only as it requires to set LimitToList property to No.

Tables:

tblItemNames
txtItemName Text, PK

tblItems
keyItemID AutoNumber, PK
txtItemName FK


Form:

RecordSource = "tblItems"

contains ComboBox [txtItemName]
txtItemName.RowSource =
Expand|Select|Wrap|Line Numbers
  1. SELECT tblItemNames.txtItemName FROM tblItemNames LEFT JOIN tblItems ON tblItemNames.txtItemName=tblItems.txtItemName WHERE tblItems.keyItemID Is Null; 
txtItemName.LimitToList = No

Form module:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.     Me.txtItemName.Requery
  3. End Sub
  4.  


Thanks FishVal,

Simple but clever, I wouldn't have thought of this. I'll see if I can think of some way to apply the principle so that I can still store the integer in my bound column whilst displaying my text value, one way or another.
Sep 25 '07 #4
Iíve come up with another solution which might be of interest to others, as this allows me to capture both the integer value in the field as well as the associated name string (and other fields when I use for real); and display the current name string value within each instance of the form, but change the row source for the combo control for each instance without visually disturbing the other records. I did have to resort to using a text box control as well as the combo control, but for my purposes that's better than storing a text string instead of an integer. Hereís what I did;

I placed a combo control with the following key properties;

Name: cboActionID
Control Source: ActionID (in my tblData)
Row Source: qryComboSource
Column Count: 2
Column Widths: 0cm;4.602cm
List Width:4.6cm
Width: 0.487cm
Left: 4.286cm
Scroll Bar Align: Left

I added a textbox control with the following key properties;
Name: txtActionName
Control Source: ActionName (in my tblData)
Left: 0.2cm
Width: 4.053cm

This simulates an ordinary combo control, so that the list drops down to the left, below the text box control.

I added code to the combo control as follows;

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cboActionID_Enter()
  5.  
  6.     Dim MySQL As String
  7.     Dim CurrAction As Integer
  8.     Dim CurrName As String
  9.  
  10.     CurrName = Me.txtActionName
  11.     CurrAction = DLookup("[ActionID]", "tblActions", "[ActionName] = '" & CurrName & "'")
  12.  
  13.     MySQL = "SELECT tblActions.ActionID, tblActions.ActionName " & _
  14.     "FROM tblActions " & _
  15.     "WHERE (((tblActions.ActionID)=34 Or (tblActions.ActionID)=35 " & _
  16.     "Or (tblActions.ActionID)=36 Or (tblActions.ActionID)=39)) " & _
  17.     "UNION SELECT """ & CurrAction & """ AS ActionID, """ & CurrName & """ AS ActionName " & _
  18.     "FROM tblData WHERE ([tblData]![TableID]=[Forms]![frmData]![TableID]);"
  19.  
  20.     Me.cboActionID.RowSource = MySQL
  21.  
  22. End Sub
  23.  
  24. Private Sub cboActionID_Exit(Cancel As Integer)
  25.  
  26.     Me.cboActionID.RowSource = "qryComboSource"
  27.     Me.cboActionID.Requery
  28.  
  29. End Sub
Iíll need to finesse the way users interact with the text box so that this is in keeping with a combo, and also control for a null value, but otherwise I think this will do the job. Thanks again for your suggestion FishVal, it made me relook at an SQL solution rather than purely VBA, and stop thinking this had to be terribly complicated.
Sep 27 '07 #5
FishVal
2,653 Expert 2GB
Hi, Kevin.

Another possible solution may be this.
Dynamically generated form input fields
Oct 1 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Brad Allison | last post: by
2 posts views Thread by Robert | last post: by
2 posts views Thread by G .Net | last post: by
2 posts views Thread by Claudia Fong | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.