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.

[Albert D. Kallal]

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.