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" <CRCI@NorthState.net> wrote in message
news:j85us1pbgi2tcek1hs0jenku5uc78ji298@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?
