CODEWITHSUNDEEP
ms-accesscombobox
ralbatross
ralbatross

Reputation: 2488

Hide items in Access 2007 combo box without also clearing the text for records that refer to the hidden items

Say I have two tables in Access 2007:

Table 'Person:
  Id : Autonumber
  Name : Text
  IsActive : Yes/No

Table 'Note':
  Id : Autonumber
  PersonId : Number (foreign key, Person.Id)
  Note : Memo

I have a form that lists all entries in the Note table, and allows users to insert/update records. The control for the Person field on the form is a combo box with 

Control Source = PersonId
Row Source = SELECT Person.Name, Person.Id FROM Person;
Bound Column = 2
Column Count = 2
Column Widths = 3cm;0cm

Now I want to limit the combo box so it only allows active persons to be selected, but when I add a 'WHERE Person.IsActive' to the RowSource query, the combo box for all records which refer to inactive people show up blank. I thought that setting the 'Limit To List' option to 'No' would fix this, but when I do Access forces me to change the displayed column so that the combo box now displays the Person ids rather than the Person names, which is not helpful.

Is there a good way to have the best of both worlds? I want to be able to hide inactive people from the combo box list, but still have inactive people show up as the value for the combo box in records that were created while currently-inactive people were still active.

Edit: related question: Custom row source for combo box in continuous form in Access

Upvotes: 1

Views: 4978

Answers (2)

ChrisPadgham
ChrisPadgham

Reputation: 870

if you add the isactive column to the Row Source and display it so the user can see which people are active

Row Source = SELECT Person.Name, Person.id, Person.IsActive FROM Person

Column Count = 3

Column Widths = 3cm;0cm;1cm

then in the before update event of the combo box

Private Sub Combo10_BeforeUpdate(Cancel As Integer)
If Not Me.Combo10.Column(2) Then
  MsgBox "You can only pick active people"
  Cancel = True
End If

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

You can add code to the current event that either changes the row source according to whether the person is active or inactive or you can show a textbox and hide the combobox on the same criterion.

Upvotes: 1

Related Questions