SQL, multiple table/field search textbox (1 Viewer)

mjdemaris · Jun 30, 2016

Is it possible to search multiple fields from multiple tables at the same time from the same textbox?

I have a textbox set up to search as one types, but it only searches for the option that is selected, i.e. part number, description, barcode...

My users are crying for a multi-field search ability, but I run into problems with the Where statement or unusable joins.

The search feature is VBA SQL based, which is set as the record source of the datasheet below the search box.

Thoughts?

Mike

Here is the SQL sub:

Code:

Public Sub SearchAllFields(ByRef theform As Form)
    GstrRowSource = "SELECT Items.MasterNum, Items.Item, Items.CategoryID_FK, Items.Attachment, ItemLocations.BinID_FK, ItemLocations.LocID, Items.Discontinued, ItemLocations.WinterLevel, ItemLocations.SummerLevel, Bins.Warehouse, SupplierPartNums.PartNumber, SupplierPartNums.Barcode, ItemLocations.Taken, ItemLocations.Left, ItemLocations.User, ItemLocations.SignoutDate, ItemLocations.LastSignOutDate, ItemLocations.ItemID_FK, ItemLocations.CurrentOnHand " & _
                    "FROM Bins INNER JOIN ((Items INNER JOIN SupplierPartNums ON Items.ItemID = SupplierPartNums.ItemID_FK) INNER JOIN ItemLocations ON Items.ItemID = ItemLocations.ItemID_FK) ON Bins.BinID = ItemLocations.BinID_FK" & _
                    "WHERE (((Items.MasterNum)=2)) OR (((Items.Item)='23') OR ((SupplierPartNums.PartNumber)='52') OR ((SupplierPartNums.Barcode)='25')) " & _
                    "ORDER BY [MasterNum]"
End Sub

The error I get while running this is "JOIN expression not supported, yet, when run in a query, it works fine.

jdraw · Jun 30, 2016

see this article and see if it is relevant to your needs.

good luck.

mjdemaris · Jul 1, 2016

Thanks, J!
I jumped the gun on posting this question, because 15 minutes of tinkering brought me success.
Here is the finished version:

Code:

Public Sub SearchAllFields(ByRef theform As Form)
    GstrRowSource = "SELECT Items.MasterNum, Items.Item, Items.CategoryID_FK, ItemLocations.BinID_FK, ItemLocations.LocID, Items.Discontinued, Bins.Warehouse, SupplierPartNums.PartNumber, SupplierPartNums.Barcode, ItemLocations.Taken, ItemLocations.Left, ItemLocations.LastSignOutDate, ItemLocations.ItemID_FK, ItemLocations.CurrentOnHand " & _
                    "FROM Bins INNER JOIN ((Items INNER JOIN SupplierPartNums ON Items.ItemID = SupplierPartNums.ItemID_FK) INNER JOIN ItemLocations ON Items.ItemID = ItemLocations.ItemID_FK) ON Bins.BinID = ItemLocations.BinID_FK " & _
                    "WHERE (((Items.MasterNum) LIKE '*" & theform!txtSearch.Text & "*')) OR (((Items.Item) LIKE '*" & theform!txtSearch.Text & "*') OR ((SupplierPartNums.PartNumber) LIKE '*" & theform!txtSearch.Text & "*') OR ((SupplierPartNums.Barcode) LIKE '*" & theform!txtSearch.Text & "*')) " & _
                    "ORDER BY [MasterNum]"
End Sub

SQL, multiple table/field search textbox (1 Viewer)

mjdemaris

Working on it...

jdraw

Super Moderator

mjdemaris

Working on it...

Similar threads

Users who are viewing this thread