Open a query based on filtered form

[Neil Chinneck]

Hi everyone,

I'm looking for some help please. I have a continuous form, with several fields which can be filtered using combo boxes. Once the user has filtered the form, I want them to enter values into a series of text boxes and then use these values to update the filtered records, and only the filtered records.

I have an update query, but I'm struggling to get the query to match up to the filtered form i.e. show the same records.

Within the query, for each field that relates to one of the filter combo boxes on the form, I have the following parameter: (qryEnterCosts is the query that the frmEnterCosts and the update query are based on)

IIf([Forms]![frmEnterCosts]![cboFilterProduc t].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterProduc t],[qryEnterCosts]![FullProdCode])

This works to a point, except that if the combo box is empty, I want it to match all values including null ones (as it does on the form filter). Instead, it matches all values, but not null ones.

So basically, how do I set the query parameters to return all values including null ones if the combo box is empty, and then only values matching the combo box, if it is populated?

Many thanks

Neil

[colintis]

Would you mind if you can provide the query in [qryEnterCosts]? So we can help you with your problem.

[Neil Chinneck]

Hi,

Thanks for your response, here's the query [qryEnterCosts]

SELECT qryJobs.JobID, qryJobs.AdHocJo b, qryJobs.JobNumb er, qryJobs.Country , qryJobs.Destina tion, qryJobs.DelDest ID, qryJobs.COMPANY , qryJobs.Supplie rID, qryJobs.Supplie rName, qryJobs.JobType Name, qryJobs.FullPro dCode, qryJobs.QtyCopi es, qryJobs.[Total Weight], tblSupplierInvo ices.InvoiceNum ber, tblSupplierInvo ices.InvoiceAmo unt, qryJobs.SE_Coll ectionCharge, qryJobs.SE_PPCo st, qryJobs.SE_UKto Port, qryJobs.SE_Port ToDoor, qryJobs.SE_Door ToDoor, qryJobs.SE_Tota lCalc, qryJobs.Packing Date, tblSupplierInvo ices.JobID AS SI_JobID, tblSupplierInvo ices.Descriptio n AS SI_Description, tblSupplierInvo ices.InvoiceDat e, tblSupplierInvo ices.GRN, tblSupplierInvo ices.PONumber, tblSupplierInvo ices.SupplierID AS SI_SupplierID, qryJobs.[Product Code], qryJobs.Edition , qryJobs.PubYear
FROM qryJobs LEFT JOIN tblSupplierInvo ices ON qryJobs.JobID = tblSupplierInvo ices.JobID;


And here's the update query which is based on [qryEnterCosts], its called [qryEnterCostsqu pd]

PARAMETERS [Forms]![frmEnterCosts]![cboFilterPackin gDate] Value;
UPDATE qryEnterCosts SET qryEnterCosts.S E_CollectionCha rge = 0, qryEnterCosts.S E_PPCost = 0, qryEnterCosts.S E_UKtoPort = 0, qryEnterCosts.S E_PortToDoor = 0, qryEnterCosts.S E_DoorToDoor = ([Total Weight]/Forms!frmEnterC osts!txtSumWeig ht)*Forms!frmEn terCosts!txtInv oiceAmount, qryEnterCosts.I nvoiceNumber = Forms!frmEnterC osts!txtInvoice Number, qryEnterCosts.I nvoiceAmount = ([Total Weight]/Forms!frmEnterC osts!txtSumWeig ht)*Forms!frmEn terCosts!txtInv oiceAmount, qryEnterCosts.I nvoiceDate = Forms!frmEnterC osts!txtInvoice Date, qryEnterCosts.S I_JobID = qryEnterCosts!J obID, qryEnterCosts.S I_SupplierID = Forms!frmEnterC osts!cboFilterS upplier, qryEnterCosts.P ONumber = Forms!frmEnterC osts!cboPONumbe r, qryEnterCosts.G RN = Forms!frmEnterC osts!txtGRN, qryEnterCosts.S I_Description = Forms!frmEnterC osts!txtInvoice Description
WHERE (((qryEnterCost s.InvoiceNumber ) Is Null) AND ((qryEnterCosts .AdHocJob)=IIf([Forms]![frmEnterCosts]![cboFilterAdHoc].[ListIndex]>-1,IIf([Forms]![frmEnterCosts]![cboFilterAdHoc]="Yes",-1,0),[qryEnterCosts]![AdHocJob])) AND ((qryEnterCosts .Country)=IIf([Forms]![frmEnterCosts]![cboFilterCountr y].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterCountr y],[qryEnterCosts]![Country])) AND ((qryEnterCosts .Destination)=I If([Forms]![frmEnterCosts]![cboFilterDestin ation].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterDestin ation],[qryEnterCosts]![Destination])) AND ((qryEnterCosts .COMPANY)=IIf([Forms]![frmEnterCosts]![cboFilterCompan y].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterCompan y],[qryEnterCosts]![Company])) AND ((qryEnterCosts .SupplierID)=II f([Forms]![frmEnterCosts]![cboFilterSuppli er].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterSuppli er],[qryEnterCosts]![SupplierID])) AND ((qryEnterCosts .PackingDate)=I If([Forms]![frmEnterCosts]![cboFilterPackin gDate].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterPackin gDate],[qryEnterCosts]![PackingDate])) AND ((qryEnterCosts .[Product Code])=IIf([Forms]![frmEnterCosts]![cboFilterProduc tCode].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterProduc tCode],[qryEnterCosts]![Product Code])) AND ((qryEnterCosts .Edition)=IIf([Forms]![frmEnterCosts]![cboFilterEditio n].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterEditio n],[qryEnterCosts]![Edition])) AND ((qryEnterCosts .PubYear)=IIf([Forms]![frmEnterCosts]![cboFilterPubYea r].[ListIndex]>-1,[Forms]![frmEnterCosts]![cboFilterPubYea r],[qryEnterCosts]![PubYear])));

[colintis]

You can try putting a WHERE clause to your qryEnterCost, something like this

[FullProdCode] Like "*" & [Forms]![frmEnterCosts]![cboFilterProduct] & "*" or [Forms]![frmEnterCosts]![cboFilterProduct] Is Null

But as the criteria requires the field from the form, therefore it may be best to duplicate qryEnterCost and make a test on it first. And leave it duplicate as the update query may show error for reading that.