Limit to list?

[Guest]

I have a combo box (on a continuous form) that has two columns: Column1, zero
length, bound to a field in a table, & Column2, unbound but visible. Limit to
List is True.

Now I want to filter the RowSource to only active records (Active is a
Yes/No field in the table). The filter is easy enough (Where Not Active), but
then, of course, the box is empty for any records for which that entry is not
Active.

Where I have this issue on a Single Form, I just omit the Where Not Active
clause if Active is false for the current record. I can't think of a clever
way to do this on a continous form, though.

Is the only solution to set LimitToList to False and then either:

1. Add code to the combo box's BeforeUpdate to cancel if the entry is
invalid or
2. Deal with the error generated before the form is updated (since this
field in the form's RecordSource has an enforced relationship with the
cognate field in the RowSource table)?

I know it is inconsistent with LimitToList, but is there some way to show
the field contents of an existing record even when it is filtered out of the
RowSource?

 

[Joel Padmore]

Brian
Set the RecordSource of the subform to exclude the inactive records.

 

[Guest]

Ah, that it were so simple. Let me clarify the situation. I have several
fields: Driver, Location, Vehicle, TimeIn, TimeOut, etc. The field in
question is Driver, although the same applies to the vehicle field. This is a
continuous form, since one line can easily represent the eight or nine fields
in the table. There is no subform; these are just combo boxes bound to fields
in the form's record source.

I want to prevent users from inadvertently selecting an inactive driver, but
I also don't want them to be confused by having missing records (i.e.
filtered out) or empty controls (due to LimitToList) just because the driver
or vehicle (or some other entity represented by a non-primary-key field) has
subsequently been deactivated. I know I could explain all that to the users,
but I really feel like I would be accomodating the platform (i.e. Access)
instead of having the platform work for me.

 

[tina]

i set up some code to handle "discontinued selections" in combo box
droplists, awhile back. to use it, you have to add a Yes/No field to the
"lookup" table - i called it Discontinued; a Yes value = the selection is no
longer available to new records, but needs to remain in the table as a
reference for existing records.

try this: add the following procedure to a standard module (make sure you
give the *module* a different name than the procedure), as

Public Function isCheckDiscontinued(ByVal blnDiscontinued As Boolean, _
ByVal frm As Form) As Boolean

isCheckDiscontinued = False

If blnDiscontinued Then
If frm.NewRecord Then
MsgBox "This selection has been discontinued. " _
& "Please choose another " _
& "selection from the droplist."
isCheckDiscontinued = True
Else
If MsgBox("This selection has been discontinued." _
& vbCr & vbCr & "Click OK to assign the " _
& "discontinued selection anyway, or click Cancel " _
& "to choose an active selection from the droplist.", _
vbOKCancel + vbDefaultButton2 + vbExclamation) = vbCancel
Then
isCheckDiscontinued = True
End If
End If
End If

End Function

(watch for unintended line-wrap in the code above!)
in the form's combo box control, make sure the Discontinued field is
included in the RowSource, and that it is also visible in the dropdown list.
add the calling code to the control's BeforeUpdate event procedure. in the
following example, the Discontinued field is the third column, as

With Me!ComboboxControlName
If isCheckDiscontinued(.Column(2), Me) = True Then
Cancel = True
.Undo
.Dropdown
End If
End With

hth

 

[Guest]

Thanks, Tina.

That's a clever solution, and a little simpler than setting LimitToList to
False and dealing with all its consequences. I just need to find out if
management can live with the inactive items being visible in the combo box or
if their primary concern is just preventing selection of inactive items.

I suspect the latter. The RowSource of the affected fields is an ODBC link
to a DB2 table, so I have no ability to add a field; however, each table has
a field that indicates whether the entry is active or inactive, so I can
implement this solution by referencing the existing Active/Inactive field.

 

[tina]

you're welcome; hope it works out for you, or perhaps gives you ideas that
will lead to a solution that fits your needs.