Problem with ComboBoxes data bound to lookup tables

I've been working with VB .NET for less than a year and this is the
first time I've posted on one of these groups, so let me apologize
beforehand if I'm being unclear, not posting my issue correctly,
posting to the wrong forum, or committing some other sort of faux pas.

My team is developing a Windows Forms application using VS 2005 with
SQL Server 2005 on the back end and we're having a problem using
ComboBoxes data bound to lookup tables.

I've created a simplified example to illustrate the problem:

Consider the following SQL Server tables:

AgeRange:

ageRangeID description active
1 under 18 FALSE
2 18 - 25 TRUE
3 26 - 30 TRUE
4 30 or over TRUE

Customer:

customerID firstName lastName ageRangeID
9368 John Smith 1
9369 Jill Johnson 3

A Foreign Key relationship between the tables: Customer.ageRangeID =
AgeRange.ageRangeID

These two tables exist in a DataSet (MyApplicationDataSet) within the
project. Each table has a corresponding TableAdapter.

Each TableAdapter has one custom query (in addition to the standard set
of queries generated by the creation of any TableAdapter).

AgeRangeTableAdapter.FillByActive: SELECT a.ageRangeID, a.description,
a.active FROM AgeRange a WHERE a.active = 'TRUE'

CustomerTableAdapter.FillByCustomerID: SELECT c.customerID,
c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM Customer c
WHERE c.customerID = @customerID

Consider the following form:

Name: frmActiveTest
Controls: 3 TextBoxes (txtCustomerID, txtFirstName, txtLastName)
1 ComboBox (cboAgeRange)
1 Button (btnSave)

Form code:

Public Class frmActiveTest
'I know most of what you're about to see can be done in the
Designer...I'm just coding by hand to illustrate my problem
'Instance of DataSet
Private WithEvents dsMyApp As New MyApplicationDataSet
'BindingSources
Private WithEvents bsCustomer As New BindingSource
Private WithEvents bsAgeRange As New BindingSource
'Instances of TableAdapters
Private WithEvents taCustomer As New
MyApplicationDataSetTableAdapters.CustomerTableAdapter
Private WithEvents taAgeRange As New
MyApplicationDataSetTableAdapters.AgeRangeTableAdapter
'Variable for CustomerNum property
Private intCustomerNum As Integer
Public Property CustomerNum() As Integer
Get
Return Me.intCustomerNum
End Get
Set(ByVal value As Integer)
Me.intCustomerNum = value
End Set
End Property
Private Sub frmActiveTest_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
'Change this value to bring up the Customer record you wish to
view
Me.CustomerNum = 9368
'Set up binding sources
Me.bsCustomer.DataSource = dsMyApp
Me.bsCustomer.DataMember = "Customer"
Me.bsAgeRange.DataSource = dsMyApp
Me.bsAgeRange.DataMember = "AgeRange"
'Data bind Customer textboxes
Me.txtCustomerID.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "customerID",
True))
Me.txtFirstName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "firstName", True))
Me.txtLastName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "lastName", True))
'Data bind AgeRange combobox
Me.cboAgeRange.DataSource = Me.bsAgeRange
Me.cboAgeRange.DisplayMember = "description"
Me.cboAgeRange.ValueMember = "ageRangeID"
Me.cboAgeRange.DataBindings.Add(New
System.Windows.Forms.Binding("SelectedValue", Me.bsCustomer,
"ageRangeID", True))
'Load AgeRange data. FillByActive Query = SELECT a.ageRangeID,
a.description, a.active FROM AgeRange a WHERE a.active = 'TRUE'
Me.taAgeRange.FillByActive(Me.dsMyApp.AgeRange)
'Load Customer data. FillByCustomerNum Query = SELECT
c.customerID, c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM
Customer c WHERE c.customerNum = @customerNum
Me.taCustomer.FillByCustomerNum(Me.dsMyApp.Customer,
Me.CustomerNum)
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
'If everything on form validates, send any updates to the
Customer table
If Me.ValidateChildren = True Then
Me.bsCustomer.EndEdit()
Me.taCustomer.Update(Me.dsMyApp.Customer)
End If
End Sub
End Class

Here is my problem:

I created the record for customerID 9368 on 01/01/06 with ageRangeID =
1 (under 18). On 01/05/06 I set the value of AgeRange.active to FALSE
for ageRangeID = 1 (under 18). So from 01/05/06 onward, no new
Customer records can be created with an ageRangeID = 1 (under 18).
That's fine, that's what I want.

However, now on 01/06/06 when I pull up customerID 9368, the AgeRange
ComboBox shows as empty (cboAgeRange.SelectedIndex = -1). That makes
sense because the Customer.ageRangeID value (1) is not in the results
returned by the query I am using to fill the AgeRange table since the
FillByActive query only looks for those AgeRange records with an
AgeRange.active value of TRUE.

That's my problem. How can I fill a ComboBox with only those records
whose AgeRange.active value is TRUE, yet make sure I ALWAYS include the
AgeRange record that relates to the current Customer record, even if
its active value is FALSE?

I found one work-around, but it is not very elegant and I feel like
it's a lot of code for what I think would be a simple and common
problem.

My work-around was to add the following custom query to the AgeRange
TableAdapter:

AgeRange.FillByActiveUnion = SELECT a.ageRangeID, a.description,
a.active FROM AgeRange a WHERE a.active = 'TRUE' UNION SELECT
b.ageRangeID, b.description, b.active FROM AgeRange b WHERE
b.ageRangeID = @ageRangeID

Then make the following changes to my form Load event:

Private Sub frmActiveTest_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim boolRefill As Boolean = False
'Data bind Customer textboxes
Me.txtCustomerID.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "customerID",
True))
Me.txtFirstName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "firstName", True))
Me.txtLastName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.bsCustomer, "lastName", True))
'Data bind AgeRange combobox
Me.cboAgeRange.DataSource = Me.bsAgeRange
Me.cboAgeRange.DisplayMember = "description"
Me.cboAgeRange.ValueMember = "ageRangeID"
Me.cboAgeRange.DataBindings.Add(New
System.Windows.Forms.Binding("SelectedValue", Me.bsCustomer,
"ageRangeID", True))
'Load AgeRange data. FillByActive Query = SELECT a.ageRangeID,
a.description, a.active FROM AgeRange a WHERE a.active = 'TRUE'
Me.taAgeRange.FillByActive(Me.dsMyApp.AgeRange)
'Load Customer data. FillByCustomerNum Query = SELECT
c.customerID, c.firstName, c.lastName, c.ageRangeID, c.entryDate FROM
Customer c WHERE c.customerNum = @customerNum
Me.taCustomer.FillByCustomerNum(Me.dsMyApp.Customer,
Me.CustomerNum)
'Work-around code is from here down
For Each drC As dsMyApp.CustomerRow In dsMyApp.Customer
If drC.IsageRangeIDNull = False And
Me.cboAgeRange.SelectedIndex = -1 Then
Me.taAgeRange.FillByActiveUnion(Me.dsMyApp.AgeRange,
drC.ageRangeID)
boolRefill = True
Exit For
End If
Next
If boolRefill = True Then
Me.taCustomer.FillByCustomerNum(Me.dsMyApp.Customer,
Me.CustomerNum)
End If
End Sub

I have to implement this on dozens of ComboBoxes using dozens of
different lookup tables (like AgeRange) throughout my application, so
I'm looking for the fastest, easiest solution to this problem.

Any help I can get from anyone out there is greatly appreciated.

Thanks in advance!

Regards,

MJS