Simplest recommendation for filtering records returned in a combo box on a form?

[MLH]

I have a form (xxxxxxxxxxxxxx) with a combo-box control
(yyyyyyyyyyyyyy). The rowsource property for that combo box is as
follows:

SELECT DISTINCTROW [qryVehicleList].[Vehicle],
[qryVehicleList].[VehicleJobID] FROM [qryVehicleList];

The SQL for qryVehicleList is ...
SELECT [VColor] & " " & [VehicleYear] & " " & [VehicleMake] & " [" &
[SerialNum] & "]" AS Vehicle, tblVehicleJobs.VehicleJobID,
tblVehicleJobs.OwnerID, tblVehicleJobs.AuthID, tblVehicleJobs.TowJob,
tblVehicleJobs.VehicleMake, tblVehicleJobs.VehicleYear,
tblVehicleJobs.MotorNum, tblVehicleJobs.SerialNum,
tblVehicleJobs.TagNum, tblVehicleJobs.TagYear,
tblVehicleJobs.TagState, tblVehicleJobs.TagCounty,
tblVehicleJobs.BodyType, tblVehicleJobs.VehicleValue,
tblVehicleJobs.InspStickNum, tblVehicleJobs.InspStickState,
tblVehicleJobs.InspStickExpDate, tblVehicleJobs.ReasonLeft4,
tblVehicleJobs.DateLeft, tblVehicleJobs.VehicleLocationName,
tblVehicleJobs.LocNameAddr, tblVehicleJobs.LocCity,
tblVehicleJobs.LocState, tblVehicleJobs.LocZip,
tblVehicleJobs.LocPhone, tblVehicleJobs.LocCounty,
tblVehicleJobs.VOEvidence, tblVehicleJobs.VehicleRunning,
tblVehicleJobs.VehicleWrecked, tblVehicleJobs.VehRemark,
tblVehicleJobs.620FENWritten, tblVehicleJobs.622FENWritten,
tblVehicleJobs.Reclaimed, tblVehicleJobs.VehicleJobTDstamp,
tblVehicleJobs.UserID, tblVehicleJobs.DMVFileNumber,
tblVehicleJobs.SaleType, tblVehicleJobs.Odo, tblVehicleJobs.VColor,
tblVehicleJobs.TowedFromLocation, tblVehicleJobs.StorageRate,
tblVehicleJobs.TowFee, tblVehicleJobs.CraneSvc,
tblVehicleJobs.TowStart, tblVehicleJobs.TowStop,
tblVehicleJobs.AltDeliveryDescrip, tblVehicleJobs.AltDeliveryFee,
tblVehicleJobs.ReTowDescrip, tblVehicleJobs.ReTowFee,
tblVehicleJobs.TicketNum, tblVehicleJobs.DriverID,
tblVehicleJobs.TTruckID, [VColor] & " " & [VehicleYear] & " " &
[VehicleMake] AS VColorYrMake
FROM tblVehicleJobs;

tblVehicleJobs.SerialNum contains a 17-char VIN as defined by the U.S.
National highway Traffic Safety Administration (USDOT). I'm seeking an
easy way for user to filter the records returned by the combobox to
say something like ONLY those VIN's beginning with "1GH". What code
behind a button would accomplish that in the simplest, most
straightforward manner?

[David Lloyd]

The following sample code shows one alternative for filtering the combo box
based on the value of a textbox. My test form contained two command
buttons, one to filter and one to show all records in the combo box. You
could also use one button to accomplish this same functionality. The combo
box is named cmbVehicle, the filter textbox, txtFilter.

Private Sub cmdAll_Click()
Dim sSQL As String

sSQL = "SELECT DISTINCTROW [qryVehicleList].[Vehicle],
[qryVehicleList].[VehicleJobID] FROM [qryVehicleList]"
cmbVehicle = ""
cmbVehicle.RowSource = sSQL
End Sub

Private Sub cmdFilter_Click()
Dim sSQL As String
Dim sWhere As String

sSQL = "SELECT DISTINCTROW [qryVehicleList].[Vehicle],
[qryVehicleList].[VehicleJobID] FROM [qryVehicleList]"
sWhere = " WHERE [qryVehicleList].SerialNum LIKE '" & txtFilter & "*'"
sSQL = sSQL & sWhere
cmbVehicle = ""
cmbVehicle.RowSource = sSQL

End Sub
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"MLH" <CR**@NorthState.net> wrote in message
news:j8********************************@4ax.com...
I have a form (xxxxxxxxxxxxxx) with a combo-box control
(yyyyyyyyyyyyyy). The rowsource property for that combo box is as
follows:

SELECT DISTINCTROW [qryVehicleList].[Vehicle],
[qryVehicleList].[VehicleJobID] FROM [qryVehicleList];

The SQL for qryVehicleList is ...
SELECT [VColor] & " " & [VehicleYear] & " " & [VehicleMake] & " [" &
[SerialNum] & "]" AS Vehicle, tblVehicleJobs.VehicleJobID,
tblVehicleJobs.OwnerID, tblVehicleJobs.AuthID, tblVehicleJobs.TowJob,
tblVehicleJobs.VehicleMake, tblVehicleJobs.VehicleYear,
tblVehicleJobs.MotorNum, tblVehicleJobs.SerialNum,
tblVehicleJobs.TagNum, tblVehicleJobs.TagYear,
tblVehicleJobs.TagState, tblVehicleJobs.TagCounty,
tblVehicleJobs.BodyType, tblVehicleJobs.VehicleValue,
tblVehicleJobs.InspStickNum, tblVehicleJobs.InspStickState,
tblVehicleJobs.InspStickExpDate, tblVehicleJobs.ReasonLeft4,
tblVehicleJobs.DateLeft, tblVehicleJobs.VehicleLocationName,
tblVehicleJobs.LocNameAddr, tblVehicleJobs.LocCity,
tblVehicleJobs.LocState, tblVehicleJobs.LocZip,
tblVehicleJobs.LocPhone, tblVehicleJobs.LocCounty,
tblVehicleJobs.VOEvidence, tblVehicleJobs.VehicleRunning,
tblVehicleJobs.VehicleWrecked, tblVehicleJobs.VehRemark,
tblVehicleJobs.620FENWritten, tblVehicleJobs.622FENWritten,
tblVehicleJobs.Reclaimed, tblVehicleJobs.VehicleJobTDstamp,
tblVehicleJobs.UserID, tblVehicleJobs.DMVFileNumber,
tblVehicleJobs.SaleType, tblVehicleJobs.Odo, tblVehicleJobs.VColor,
tblVehicleJobs.TowedFromLocation, tblVehicleJobs.StorageRate,
tblVehicleJobs.TowFee, tblVehicleJobs.CraneSvc,
tblVehicleJobs.TowStart, tblVehicleJobs.TowStop,
tblVehicleJobs.AltDeliveryDescrip, tblVehicleJobs.AltDeliveryFee,
tblVehicleJobs.ReTowDescrip, tblVehicleJobs.ReTowFee,
tblVehicleJobs.TicketNum, tblVehicleJobs.DriverID,
tblVehicleJobs.TTruckID, [VColor] & " " & [VehicleYear] & " " &
[VehicleMake] AS VColorYrMake
FROM tblVehicleJobs;

tblVehicleJobs.SerialNum contains a 17-char VIN as defined by the U.S.
National highway Traffic Safety Administration (USDOT). I'm seeking an
easy way for user to filter the records returned by the combobox to
say something like ONLY those VIN's beginning with "1GH". What code
behind a button would accomplish that in the simplest, most
straightforward manner?