Change a Form's WHERE clause in VBA

[Never Hide]

Hi everyone,

I have a form, and in that forn a subform with a list of products.
This is the Recordsource of the subform:

SELECT DISTINCT ProductSubCategory.ProductCategoryID, Product.ProductSubCategoryID AS SubID, Product.ProductID, Product.ProductCode, Product.Description, Product.VATRate, Product.RetailPrice, Q_ProductSupplyQuant.SupplyQuant, Q_ProductSaleQuant.SaleQuant, nz([SupplyQuant],0)-nz([SaleQuant],0) AS AvailQuant 
FROM ProductSubCategory 
INNER JOIN (((Q_ProductSaleQuant 
RIGHT JOIN Product 
ON Q_ProductSaleQuant.ProductID = Product.ProductID) 
LEFT JOIN Q_ProductSupplyQuant 
ON Product.ProductID = Q_ProductSupplyQuant.ProductID) 
LEFT JOIN ProductBarcode 
ON Product.ProductID = ProductBarcode.ProductID) 
ON ProductSubCategory.ProductSubCategoryID = Product.ProductSubCategoryID 
WHERE (((ProductBarcode.Barcode) Like "*" Or (ProductBarcode.Barcode) Is Null)) 
ORDER BY Product.ProductCode, Product.Description;

I've also attached an screenshot of that recordsource from the query builder

I don't display the "Barcode" in my subform because I can have multiple barcodes for one product and I don't want to have duplicate rows in my display form. I want however to be able to search using a Barcode. At this moment, I just use the Barcode as a WHERE clause for my form.
I have a Search textbox,TB_Search, in my main form and I want to be able to use it to search through my products using a barcode. But since the "Barcode" field isn't actually present in the form, I can't use the Me.Subform.Form.Filter property to search...

Anyone knows if there's a way to change the WHERE clause of the form?
Something like "Me.Subform.Form.Where" ?
Or maybe there is some other approach I could use?
I'm open to any suggestions.

Thanks in advance

 

[pr2-eugin]

You can add that criteria on the Fly and change the Reocrdsource of the SubForm..

Me!Subform1.Form.RecordSource = yourNewSELECT

 

[Never Hide]

You can add that criteria on the Fly and change the Reocrdsource of the SubForm..

Me!Subform1.Form.RecordSource = yourNewSELECT

Thanks a lot man, that did the trick
I couldn't get my head around it :banghead:
I think I'm in desperate need of vacations

 

[pr2-eugin]

Thanks a lot man, that did the trick

You are most Welcome..

I couldn't get my head around it :banghead:
I think I'm in desperate need of vacations

Ha ha, I am off for my vacation in 20 days from today..