Combo: disable or grey out items in the list?

[Kevin Wilcox]

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

[FishVal]

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 =

SELECT tblItemNames.txtItemName FROM tblItemNames LEFT JOIN tblItems ON tblItemNames.txtItemName=tblItems.txtItemName WHERE tblItems.keyItemID Is Null; 

txtItemName.LimitToList = No

Form module:

Private Sub Form_AfterUpdate()
    Me.txtItemName.Requery
End Sub
 

[FishVal]

Accidently posted.

[Kevin Wilcox]

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 =

SELECT tblItemNames.txtItemName FROM tblItemNames LEFT JOIN tblItems ON tblItemNames.txtItemName=tblItems.txtItemName WHERE tblItems.keyItemID Is Null; 

txtItemName.LimitToList = No

Form module:

Private Sub Form_AfterUpdate()
    Me.txtItemName.Requery
End Sub
 

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.

[Kevin Wilcox]

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;

Option Compare Database
Option Explicit
 
Private Sub cboActionID_Enter()
 
    Dim MySQL As String
    Dim CurrAction As Integer
    Dim CurrName As String
 
    CurrName = Me.txtActionName
    CurrAction = DLookup("[ActionID]", "tblActions", "[ActionName] = '" & CurrName & "'")
 
    MySQL = "SELECT tblActions.ActionID, tblActions.ActionName " & _
    "FROM tblActions " & _
    "WHERE (((tblActions.ActionID)=34 Or (tblActions.ActionID)=35 " & _
    "Or (tblActions.ActionID)=36 Or (tblActions.ActionID)=39)) " & _
    "UNION SELECT """ & CurrAction & """ AS ActionID, """ & CurrName & """ AS ActionName " & _
    "FROM tblData WHERE ([tblData]![TableID]=[Forms]![frmData]![TableID]);"
 
    Me.cboActionID.RowSource = MySQL
 
End Sub
 
Private Sub cboActionID_Exit(Cancel As Integer)
 
    Me.cboActionID.RowSource = "qryComboSource"
    Me.cboActionID.Requery
 
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.

[FishVal]

Hi, Kevin.

Another possible solution may be this.
Dynamically generated form input fields