472,807 Members | 3,161 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,807 software developers and data experts.

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?
Jan 19 '06 #1
1 1811
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?
Jan 20 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
2
by: Luther | last post by:
I want to create a form that searches a table. The hard part is this, I'd like to have the available records filtered based on combobox selections. For example, if this were a vehicle database, I...
19
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
1
by: hackerslacker | last post by:
I have an ordering form that use two combo boxes to filter down the records of a Products table. This worked fine with the after_Update of the first filtering the records and creating the...
4
by: Dave | last post by:
I am having difficulty filtering a form as the user types in a onchange event here is my code strFilter = cboCriteria.Value & " LIKE '" & Me!txtCriteria.text & "*" & "'" If Len(strFilter ) 0...
4
by: novoselent | last post by:
This seems like it should be an easy thing, but I think I'm missing something simple here...or I'm just going about it all wrong... Using Access 2003 I have a form that lists vehicle service...
3
by: flymo | last post by:
Hello All, I've bee trying out access 2007 and have a weird issue and would like to see if I'm issing something really basic. I have a form based on a query, I create a combo to look for records...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.