combobox rowsource

[DaveE]

I have a combo box whos control source is stored in a table, products. Its
row source is a query against the vendors table that filters records by a
field called "Active". When entering data, the combo box lists all vendors
that are active. Works fine. After changing a vendors status to inactive,
viewing old records for that vendor yields an empty entry in the combo box.
Not good. I need to allow users to see the old records vendor value but not
let them pick inactive vendors in the combo box during data entry.

I hope I explained that well enough. Any help would be greatly appreciated.

 

[Dirk Goldgar]

I have a combo box whos control source is stored in a table, products. Its
row source is a query against the vendors table that filters records by a
field called "Active". When entering data, the combo box lists all
vendors
that are active. Works fine. After changing a vendors status to
inactive,
viewing old records for that vendor yields an empty entry in the combo
box.
Not good. I need to allow users to see the old records vendor value but
not
let them pick inactive vendors in the combo box during data entry.

One way to do this, *if* the combo's Bound Column is the visible and
displayed column, is to set the combo box's LimitToList property to No (so
the user can theoretically enter values not in the list, and the combo will
show all existing values), but use the combo's BeforeUpdate event to check
whether any entry is actually in the list, and cancel the update if it
isn't. If the value entered in the combo box is not in the list, the combo
box's .ListIndex property will have a value of -1. So you could have a
BeforeUpdate event procedure like this:

'------ start of example code ------
Private Sub cboVendor_BeforeUpdate(Cancel As Integer)

If Me!cboVender.ListIndex < 0 Then

MsgBox _
"You entered a vendor that is not in the list. " & _
"Please choose an active vendor from the list.", _
vbExclamation, _
"Invalid Vendor"

Cancel = True

End If

End Sub
'------ end of example code ------

Now, that won't work if the combo box's bound column is, say, the VendorID,
but it displays some other column such as the vendor's name. In such a
case, there are a couple of other alternatives. One is to arrange the
combo's rowsource and columns so that it includes the Active field, but
selects all vendors, active or no. It should probably go ahead and sort all
the inactive vendors to the bottom of the list. Then use the combo's
BeforeUpdate event to check whether the user has chosen an inactive vendor,
and if so, display a suitable message and cancel the update.

If you decide you really have to keep the inactive vendors out of the list
entirely, then you need to fudge by modifying the form's recordsource to
pick up the vendor name from wherever it resides, and have a text box bound
to the vendor name on the form, positioned so that it overlays the text
portion of the combo box. Make sure the text box is on top (Format -> Bring
to Front), and set its Disabled and Locked properties both to True. When
the user tabs through the fields on the form, the focus will go to the combo
box, which will be blank if the vendor is not in the list, but but when the
focus is not in the combo box, the text box will display the vendor name.

 

[Arvin Meyer [MVP]]

In the Current event of the form, change the rowsource, something like:

Sub Form_Current()
If Me.NewRecord = True Then
Me.cboComboWhatever.RowSource = "Query1"
Else
Me.cboComboWhatever.RowSource = "Query2"
End If
End Sub

 

[DaveE]

Thank you both for responding. Two good ideas.

One way to do this, *if* the combo's Bound Column is the visible and
displayed column, is to set the combo box's LimitToList property to No (so
the user can theoretically enter values not in the list, and the combo will
show all existing values), but use the combo's BeforeUpdate event to check
whether any entry is actually in the list, and cancel the update if it
isn't. If the value entered in the combo box is not in the list, the combo
box's .ListIndex property will have a value of -1. So you could have a
BeforeUpdate event procedure like this:

'------ start of example code ------
Private Sub cboVendor_BeforeUpdate(Cancel As Integer)

If Me!cboVender.ListIndex < 0 Then

MsgBox _
"You entered a vendor that is not in the list. " & _
"Please choose an active vendor from the list.", _
vbExclamation, _
"Invalid Vendor"

Cancel = True

End If

End Sub
'------ end of example code ------

Now, that won't work if the combo box's bound column is, say, the VendorID,
but it displays some other column such as the vendor's name. In such a
case, there are a couple of other alternatives. One is to arrange the
combo's rowsource and columns so that it includes the Active field, but
selects all vendors, active or no. It should probably go ahead and sort all
the inactive vendors to the bottom of the list. Then use the combo's
BeforeUpdate event to check whether the user has chosen an inactive vendor,
and if so, display a suitable message and cancel the update.

If you decide you really have to keep the inactive vendors out of the list
entirely, then you need to fudge by modifying the form's recordsource to
pick up the vendor name from wherever it resides, and have a text box bound
to the vendor name on the form, positioned so that it overlays the text
portion of the combo box. Make sure the text box is on top (Format -> Bring
to Front), and set its Disabled and Locked properties both to True. When
the user tabs through the fields on the form, the focus will go to the combo
box, which will be blank if the vendor is not in the list, but but when the
focus is not in the combo box, the text box will display the vendor name.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)