473,692 Members | 1,895 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Procedure for filtering records displayed in a subform control...

MLH
I have a form with a subform control on it listing
records returned by a saved query named UnbilledVehicle s.
I would like to put command buttons on the main form
to apply dynamic filters to the records displayed in the
subform control. Say, for instance, to list only 2004 model
cars. I'm seeking the simplest approach.

The main form is frmCreateInvoic e, the subform control
is named frmCreateInvoic eSubFormCtl and the actual sub-
form is named frmCreateInvoic eSubForm. The query off
which the subform is based is named UnbilledVehicle s and
here's its SQL view:

SELECT tblVehicleJobs. VehicleJobID, [VColor] & " " & [VehicleYear] & "
" & [VehicleMake] & " [" & [SerialNum] & "] at " & [TowCompany] AS
Vehicle, TempTable.Wanna Bill, tblAdmin.Cluste rID,
tblVehicleJobs. ProcFee
FROM tblClusters INNER JOIN (tblAdmin INNER JOIN (tblVehicleJobs INNER
JOIN TempTable ON tblVehicleJobs. VehicleJobID =
TempTable.Vehic leJobID) ON tblAdmin.TowCoI D = tblVehicleJobs. TowCoID)
ON tblClusters.Clu sterID = tblAdmin.Cluste rID
WHERE (((tblAdmin.Clu sterID)=GetCurr entClusterID()) AND
((tblVehicleJob s.InvoiceNumber ) Is Null) AND
((tblVehicleJob s.InvoicePaid)= No));

'preciate all suggestions.
Aug 19 '06 #1
1 2288
I would create a query, and save it for the sub-form.

So, lets assume you create a query based on
SELECT tblVehicleJobs. VehicleJobID, [VColor] & " " & [VehicleYear] & "
.......

Now, simply have a few drop down boxes on the main form.

Lets just have two boxes (you see how to add more).

We will build two UN-Bound combo boxes on the main form.

cboCarYear cboMake

the above two combo boxes could of coupe get their data from existing table,
but by un-bound, I simply mean that when you select a value from either of
the above combos, they don't save the selection to a field (hence, this
means they are un-bound).

Now, in the after update event of both of the comb boxes, we Weill go

Call SetSubData

For each additional combo box (or even text box) you want to filter by, you
would add the code

Call SetSubData

In the after update event.

Our code for SetSubData would the simply filter the sub-form based on the
options in the combo boxes.

So, we now create a sub (in the forms module also)

Public Sub SetSubData
dim strwhere as string

if issnull(me.CarY ear) = false then
strWhere = "(VehicleYe ar = " & me.cboCarYear & "")
end if

if isnull(me.cboMa ke) = false then

if strWhere <"" then
strWhere = strWhere & " and "
end if

strWhere = "(VehicleMa ke = '" & me.cboMake & "')"
end if

strWhere = "select * from qrySubData where " & strWhere

me.mysubForm.fo rm.RecordSource = strWhere

note how if you leave the combo box blank, then that would mean "all years",
or all makes for example....

Take carefaull note of the 2nd condiatio, in which we surround the text by
quotes.

numbers -- no quotes
text -- surround with quotes
DateField = surround wiht #

So for each additional control (or combo) you add as a filter, simply add
the code snip to the above routine, and don't forget to put a call to this
routine in the after update event of each new filter control you add...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com


"MLH" <CR**@NorthStat e.netwrote in message
news:hi******** *************** *********@4ax.c om...
>I have a form with a subform control on it listing
records returned by a saved query named UnbilledVehicle s.
I would like to put command buttons on the main form
to apply dynamic filters to the records displayed in the
subform control. Say, for instance, to list only 2004 model
cars. I'm seeking the simplest approach.

The main form is frmCreateInvoic e, the subform control
is named frmCreateInvoic eSubFormCtl and the actual sub-
form is named frmCreateInvoic eSubForm. The query off
which the subform is based is named UnbilledVehicle s and
here's its SQL view:

SELECT tblVehicleJobs. VehicleJobID, [VColor] & " " & [VehicleYear] & "
" & [VehicleMake] & " [" & [SerialNum] & "] at " & [TowCompany] AS
Vehicle, TempTable.Wanna Bill, tblAdmin.Cluste rID,
tblVehicleJobs. ProcFee
FROM tblClusters INNER JOIN (tblAdmin INNER JOIN (tblVehicleJobs INNER
JOIN TempTable ON tblVehicleJobs. VehicleJobID =
TempTable.Vehic leJobID) ON tblAdmin.TowCoI D = tblVehicleJobs. TowCoID)
ON tblClusters.Clu sterID = tblAdmin.Cluste rID
WHERE (((tblAdmin.Clu sterID)=GetCurr entClusterID()) AND
((tblVehicleJob s.InvoiceNumber ) Is Null) AND
((tblVehicleJob s.InvoicePaid)= No));

'preciate all suggestions.

Aug 19 '06 #2

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

Similar topics

3
11101
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 have a primary form named TestResults, which is connected to data in a table named TestResults. There are basically two other tables that are related to the TestResults table (and the primary form) named Names-Normalized and SiteAddresses. The...
3
4166
by: Sam Gledhill | last post by:
Hi, I'm hoping someone can help me out. I have a database with a form. This form has no recordsource (I'm using it as a complex MsgBox). It has a single textbox of type Date. It also has three unrelated subform that are filtered according to the date entered in the form's text box. By using after update events, I've been able to allow users to change the date in the textbox and alter records from the day throughout three other tables. ...
19
3545
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 form, the subform returns the records based on the two criteria. The criteria used on the main form are values selected in two list boxes. When the user clicks on the first list box (lstCollege), it returns values in the second list box...
3
2666
by: DD | last post by:
I have a mainform with a subform. > The main form has a dropdown box "chooseMonth", in the afterupdate event > i requery the subform so all records with the same date are viewed. > Now i only want to print the selected records of the selected month > Can any one advise on the below Below is code i am trying to adapt (Thanks Don) 1.This first part is causing me problems so i have deleted it as it strikes me that it is looking for a...
12
19007
by: MLH | last post by:
I have created two forms: frmBrowseNegsMainform and frmBrowseNegsSubform. I put a subform control on the first of these. The SourceObject property for the subform control is, of course, frmBrowseNegsSubform. I would like to perform an ascending or descending sort on any of the 7 columns shown in datasheet view in the subform control. I've been unsuccessful. Is there something tricky about sorting in subform controls?
6
1687
by: Liz Malcolm | last post by:
Hello and Thanks in advance for any help. I am using Access 2000. I have a data entry form that opens a main form (using the On Click event of the combo box ) with tab controls and 1 subform on the last tab control. The main form is opened using a where clause to restrict the contact type. The subform is built on a separate table that lists other contact types that the contact could be a member of. Example a volunteer could also be a...
1
4436
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have created a MainTable (and related form), which has an associated SubForm (popup) along with its underlying, separate Table. The tables' relationship is one to many respectively. The primary key
3
1985
by: Damon Grieves | last post by:
Hi I have a large table which I wanted to filter and then edit the selected record. I'm using a form with several pull down fields linked to lookup tables which correspond to fields in the large table. The user selects a number or category or All from the pull-down and this is used in a query, made up of the main table and the lookup tables with a suitable Iif statement to filter the query. So on the form there are several fields that...
7
14806
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 want to look at that data and filter it based on what is in it. I know that this could have been done with data sets and data views in asp.net 1.1 but how is this done now in asp.net 2.0?
0
8538
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9083
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8797
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7627
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6459
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5818
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4557
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2972
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2237
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.