473,232 Members | 1,596 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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 UnbilledVehicles.
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 frmCreateInvoice, the subform control
is named frmCreateInvoiceSubFormCtl and the actual sub-
form is named frmCreateInvoiceSubForm. The query off
which the subform is based is named UnbilledVehicles and
here's its SQL view:

SELECT tblVehicleJobs.VehicleJobID, [VColor] & " " & [VehicleYear] & "
" & [VehicleMake] & " [" & [SerialNum] & "] at " & [TowCompany] AS
Vehicle, TempTable.WannaBill, tblAdmin.ClusterID,
tblVehicleJobs.ProcFee
FROM tblClusters INNER JOIN (tblAdmin INNER JOIN (tblVehicleJobs INNER
JOIN TempTable ON tblVehicleJobs.VehicleJobID =
TempTable.VehicleJobID) ON tblAdmin.TowCoID = tblVehicleJobs.TowCoID)
ON tblClusters.ClusterID = tblAdmin.ClusterID
WHERE (((tblAdmin.ClusterID)=GetCurrentClusterID()) AND
((tblVehicleJobs.InvoiceNumber) Is Null) AND
((tblVehicleJobs.InvoicePaid)=No));

'preciate all suggestions.
Aug 19 '06 #1
1 2261
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.CarYear) = false then
strWhere = "(VehicleYear = " & me.cboCarYear & "")
end if

if isnull(me.cboMake) = false then

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

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

strWhere = "select * from qrySubData where " & strWhere

me.mysubForm.form.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**@NorthState.netwrote in message
news:hi********************************@4ax.com...
>I have a form with a subform control on it listing
records returned by a saved query named UnbilledVehicles.
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 frmCreateInvoice, the subform control
is named frmCreateInvoiceSubFormCtl and the actual sub-
form is named frmCreateInvoiceSubForm. The query off
which the subform is based is named UnbilledVehicles and
here's its SQL view:

SELECT tblVehicleJobs.VehicleJobID, [VColor] & " " & [VehicleYear] & "
" & [VehicleMake] & " [" & [SerialNum] & "] at " & [TowCompany] AS
Vehicle, TempTable.WannaBill, tblAdmin.ClusterID,
tblVehicleJobs.ProcFee
FROM tblClusters INNER JOIN (tblAdmin INNER JOIN (tblVehicleJobs INNER
JOIN TempTable ON tblVehicleJobs.VehicleJobID =
TempTable.VehicleJobID) ON tblAdmin.TowCoID = tblVehicleJobs.TowCoID)
ON tblClusters.ClusterID = tblAdmin.ClusterID
WHERE (((tblAdmin.ClusterID)=GetCurrentClusterID()) AND
((tblVehicleJobs.InvoiceNumber) Is Null) AND
((tblVehicleJobs.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
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...
3
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...
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...
3
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...
12
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,...
6
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...
1
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...
3
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...
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...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.