none
Query Filter by Tag RRS feed

  • Question

  • Hello

    In an effort to reduce the number of objects in this little db project, I am looking to use a single query to to gather info to supply to a Tab Control on a Form. So the thought is to use the Tag control as a filter so I only need 1 query. In this case I have a number lets say 200 items but I only want certain items to show on each Tab. There are 17 Tabs. The Query Fields include a Field called "Location". The plan is to use the Location Field Values as the Tab Control Tag to use as a filter.

    Does anyone have a method that can do this? or a better way so I only need 1 query instead of 17? I also considered using the Location field Values as the Tab Names but that would make the Table width too wide unless multiple rows of Tabs are used.

    Thank you for any help n this regard!!!

    SQL example: The WHERE would look to the Tag in the Tab for filtering.

    SELECT tblItems.Quantity, ([tblItems].[Brand]+" "+[tblItems].[ItemName]) AS BrandItem
    FROM tblLocation INNER JOIN tblItems ON tblLocation.LocationID = tblItems.LULocation
    WHERE (((tblLocation.LocationName)="bathroom"))
    ORDER BY tblItems.Brand, tblItems.ItemName;


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Sunday, August 2, 2015 9:26 PM

Answers

  • Hi Chris. Yes, exactly! I was just thinking what's the sense of reducing the number of query objects if you don't also reduce the number of subforms where that query is used. So, you can create a main form and add a subform to it, then add the tab control and just change the order of display to move the subform in front of the tab control. Now, when you click on each tab, the same subform will be visible through all of them. Then, in the Change event of the tab control, just change the filter of the subform. For example:

    Select Case Me.TabControlName
    
    Case 0 '1st tab
    Me.SubformName.Form.Filter = "Location='Kitchen'"
    
    Case 1 
    Me.SubformName.Form.Filter = "Location='Garage'"
    
    End Select
    
    Me.SubformName.Form.FilterOn = True

    Hope that helps...

    • Edited by .theDBguy Monday, August 3, 2015 1:32 AM
    • Marked as answer by KCDW Monday, August 3, 2015 3:40 AM
    Monday, August 3, 2015 12:39 AM

All replies

  • In an effort to reduce the number of objects in this little db project, I am looking to use a single query to to gather info to supply to a Tab Control on a Form. So the thought is to use the Tag control as a filter so I only need 1 query. In this case I have a number lets say 200 items but I only want certain items to show on each Tab. There are 17 Tabs. The Query Fields include a Field called "Location". The plan is to use the Location Field Values as the Tab Control Tag to use as a filter.

    Hi Chris,

    There are a couple of ways to build in some flexibility in order to have only one query. It all depends on which systematic you want to use, and suits your housestyle. In all of my applications I use a "typical" way of working so that I have no QueryDefs at all!

    One way could be to fill the caption of the Tabs with the value of the LocationName of the 17 "selected" Locations. In the Click event of these 17 Tabs you then can run a general sub with the Tab Control as parameter, in which the Where-parameter is constructed that is used in the following OpenForm command.

    Another way could be to generate a selection-form with all (selected) LocationNames. In that case this forms reduces the 17 Tabs to only 1 (or a command button). After valid selection of one LocationName, the Where-condition is constructed on basis of this name, and the next form is opened.

    Instead of constructing only the Where-condition, you could also construct the whole sql-string, that is further assigned to the RecordSource of the to-open-form.

    Most important point however is to develop a good and consistent housestyle, to re-use this in different situations.

    Imb.

    Sunday, August 2, 2015 10:10 PM
  • Hi Chris,

    An interesting approach that got me thinking but i am not sure exactly what you are after, so perhaps this...

    I would try to create the where clause "on the Fly" in the on open event of the Form. 

    Then as each tab will have a subform for data display, you will set the Where clause of the Record-source of each subform based on that Location.

    strSQL = strSelect & " WHERE (((tblLocation.LocationName)='" & me.[Page1].Tag  &"'" & strOrderBy


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Sunday, August 2, 2015 10:15 PM
  • Hi Chris,

    It's good to try and reduce your objects down to just one query. But with 17 tabs, how many subforms are you using? I hope it's also just one. There's a few ways to get what you want. If you're only using one subform, you don't really need to use the Tag property at all. In the Change event of the Tab control, you can just use a Select Case statement to set the Filter property of the subform. Just my 2 cents...

    Sunday, August 2, 2015 10:54 PM
  • Imb, ProcessIT & DB guy thank you for your advice. I am just trying to build a little thing for my wife to help her with a shopping list. so the location Table is being used to indicate where in the house to look for these items displayed. Each Tab is a different room or cabinet etc. So...

    IMB The Form itself is unbound. I was just dropping a subform on each Tab.

    ProcessIT I tried using your SQL but couldn't. Error The expression On Enter you entered as the event property setting produced the following error, The object doesn't contain the automation object strsql. The error only occus when I select the particular tab the code is on. When I click OK, the subform returns all records in all locations.

    the DB guy, I am not grasping your solution quite. Only 1 subForm for all tabs? Is the subForm outside the Tab control altogether and selecting Tabs just changes the sql of the subForm. Please explain further.

    Thank you all!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, August 3, 2015 12:13 AM
  • Hi Chris. Yes, exactly! I was just thinking what's the sense of reducing the number of query objects if you don't also reduce the number of subforms where that query is used. So, you can create a main form and add a subform to it, then add the tab control and just change the order of display to move the subform in front of the tab control. Now, when you click on each tab, the same subform will be visible through all of them. Then, in the Change event of the tab control, just change the filter of the subform. For example:

    Select Case Me.TabControlName
    
    Case 0 '1st tab
    Me.SubformName.Form.Filter = "Location='Kitchen'"
    
    Case 1 
    Me.SubformName.Form.Filter = "Location='Garage'"
    
    End Select
    
    Me.SubformName.Form.FilterOn = True

    Hope that helps...

    • Edited by .theDBguy Monday, August 3, 2015 1:32 AM
    • Marked as answer by KCDW Monday, August 3, 2015 3:40 AM
    Monday, August 3, 2015 12:39 AM
  • Hi Chris,

    The SQL was a demonstration of what you can create in code to update each record source from VBA.

    Wouldn't you be better off just filtering the full recordset by location?

    One single for that displays all records or just the specific location?

    This could be easliy done. Either using the inbuilt filter dropdowns of a Datasheet view of your data or via an unbound combo box that selects the Locations and updates the Filter property of the form...


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Monday, August 3, 2015 1:53 AM
  • I may have gotten something incorrectly. I receive a compile error; Method or data member not found. Here is the code. Please indicate what my error is. Thank you!!!

    Option Compare Database
    Option Explicit
    
    Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0
    
    Case 0
    Me.TabCtl0.Form.Filter = "LocationName='Bathroom'"
    Case 1
    Me.TabCtl0.Form.Filter = "LocationName='Laundry'"
    Case 2
    Me.TabCtl0.Form.Filter = "LocationName='Pantry3'"
    Case 3
    Me.TabCtl0.Form.Filter = "LocationName='Pantry4'"
    Case 4
    Me.TabCtl0.Form.Filter = "LocationName='Stove5'"
    Case 5
    Me.TabCtl0.Form.Filter = "LocationName='Stove6'"
    Case 6
    Me.TabCtl0.Form.Filter = "LocationName='Stove7'"
    Case 7
    Me.TabCtl0.Form.Filter = "LocationName='Stove8'"
    Case 8
    Me.TabCtl0.Form.Filter = "LocationName='Fridge9'"
    Case 9
    Me.TabCtl0.Form.Filter = "LocationName='Freezer10'"
    Case 10
    Me.TabCtl0.Form.Filter = "LocationName='Cab11'"
    Case 11
    Me.TabCtl0.Form.Filter = "LocationName='Cab12'"
    Case 12
    Me.TabCtl0.Form.Filter = "LocationName='Cab13'"
    Case 13
    Me.TabCtl0.Form.Filter = "LocationName='Cab14'"
    Case 14
    Me.TabCtl0.Form.Filter = "LocationName='Cab15'"
    Case 15
    Me.TabCtl0.Form.Filter = "LocationName='KitSink16'"
    Case 16
    Me.TabCtl0.Form.Filter = "LocationName='Gen17'"
    End Select
    
    Me.TabCtl0.Form.FilterOn = True
    End Sub
    

    The error shows on .Form

    Case 0
    Me.TabCtl0.Form.Filter


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, August 3, 2015 2:23 AM
  • Oops got that wrong here is the corrected one. The error is Variable not defined. on; subForm 1

    Case 0
    Me.TestSQLFilter subform1.Form.Filter

    Option Compare Database
    Option Explicit
    
    Private Sub TabCtl0_Change()
    Select Case Me.TabCtl0
    
    Case 0
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Bathroom'"
    Case 1
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Laundry'"
    Case 2
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Pantry3'"
    Case 3
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Pantry4'"
    Case 4
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Stove5'"
    Case 5
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Stove6'"
    Case 6
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Stove7'"
    Case 7
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Stove8'"
    Case 8
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Fridge9'"
    Case 9
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Freezer10'"
    Case 10
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Cab11'"
    Case 11
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Cab12'"
    Case 12
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Cab13'"
    Case 13
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Cab14'"
    Case 14
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Cab15'"
    Case 15
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='KitSink16'"
    Case 16
    Me.TestSQLFilter subform1.Form.Filter = "LocationName='Gen17'"
    End Select
    
    Me.TestSQLFilter subform1.Form.FilterOn = True
    End Sub


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    • Edited by KCDW Monday, August 3, 2015 2:32 AM
    Monday, August 3, 2015 2:28 AM
  • What was the error message and which line is highlighted?
    Monday, August 3, 2015 2:32 AM
  • Hi Chris,

    My suggestion was to just use a single form (main form -no subform ) and set the filter for this main-form only! -Use the After update event of an unbound combo box. Set the RowSource for the Combo = "Select distinct Location from MyLocationsTableName"

    IF you need a subform for EACH  your syntax would be:

    Me.BathRoomSubFormsName.Form.Filter = "LocationName='Bathroom'"

    You do not need a reference to the TabControl.

    <o:p></o:p>

    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Monday, August 3, 2015 2:50 AM
  • oKay!

    The issue was brackets needed to be used in the name of the subform.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, August 3, 2015 3:35 AM
  • Hi Chris. Congratulations on getting it to work. We're all happy to assist. Good luck with your project.
    Monday, August 3, 2015 3:54 AM