Full Version: Keyword Search In Multiple Tabs/pages
UtterAccess Forums > Microsoft® Access > Access Forms
Hello all!

This is my first post, and I am a beginner with VBA, so I will try to be as clear as possible. I have built my first database and through the help of this forum, YouTube, and others, I have working IT trouble ticket DB that my coworkers are enjoying. However, I would like to add more advance features. Particularly, more robust search options.

On the dashboard of my application (the main form, 'frmMSLHub'), I have a three-tab window that displays a history of trouble tickets utilizing embedded continuous subforms (sfrmMSL_CurrentMonth, sfrmMSL_PreviousMonth, and sfrmTicketList). All subforms are populated by a queries, whose criteria directly apply to the purpose of the subform. In an unfiltered state they are identical just queried differently. Tab 1, or page 1, queries the current month, tab 2 queries the Previous, and tab 3 is a keyword search whose underlying query is unfiltered until the command button is triggered.

My issue is with the keyword search. It is a little clunky; the tab needs to be clicked to display the ticket list, then the user types the (wildcard) search criteria, and clicks a button to run the query. This piece works fine as I have multiple fields included that can return values.

So, here is my rub. What I would like happen is to include all three tabs/pages in the singular keyword search so no matter which tabbed is focused on I can return the desired criteria but within the parameters of the of the tab (keyword search in current month only return values from the current month, etc.).

I have included the code for the MSL Hub. The command button to execute the query is cmdSearch and the Tab Control is TabControl_1. I am eager to resolve this so hopefully somebody can help me out!


Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT tblTicketDetails.TicketID, tblTicketDetails.Network, tblTicketDetails.Equipment, tblTicketDetails.OpenedDate, tblTicketDetails.OpenedBy, tblTicketDetails.Status, tblTicketDetails.ResolvedBy, tblTicketDetails.Assist1, tblTicketDetails.Assist2, tblTicketDetails.Issue, tblTicketDetails.TypeofWork, tblTicketDetails.Solution, tblTicketDetails.Time " _
& "FROM tblTicketDetails " _
& "WHERE [Network] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Equipment] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Status] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [TypeofWork] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Issue] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Solution] LIKE '*" & Me.txtKeywords & "*' " _
& "ORDER BY tblTicketDetails.OpenedDate "

Me.subTicketList.Form.RecordSource = SQL

Me.txtKeywords = ""

DoCmd.GoToControl "txtKeywords"
End Sub

Private Sub TabControl_1_Click()
If Me.txtKeywords = "" Then
End If
DoCmd.GoToControl "txtkeywords"
End Sub
Welcome to UtterAccess.

"... so no matter which tabbed is focused on..."

You can determine which tab is currently active, by using the Change event of the tab control:
Private Sub tabYourTabNameGoesHere_Change()
    Dim I As Integer
    I = tabYourTabNameGoesHere.Value
    Select Case I
        Case 0
              Msgbox "Current tab is " & I
        Case 1
             Msgbox "Current tab is " & I
        Case Else
    End Select
End Sub

With that information, you can add an additional filter to your dynamic SQL to restrict the results to the appropriate tab, e.g.

"...WHERE tblTicketDetails.OpenedDate Between MonthStartDateOnTab1 and MonthEndateonTab1"

Of course, you'll need to work out how to craft the two dates, depending on what is actually available from tab1.


Thank you for your quick response! Fortunatley for me, this problem has resolved itself. Some of the feedback I received from the Beta test indicated that the keyword search, which is the primary function my folks are looking for, feels 'buried' in the tabbed structure. So I am going back to my original design have the keyword open a form to display thier query results.

Again, thank you for your time and assistance! Have a great day!

tina t
Some of the feedback I received from the Beta test indicated that the keyword search, which is the primary function my folks are looking for, feels 'buried' in the tabbed structure.

your last post indicates you resolved this to your satisfaction, but i just want to add a few comments as "food for thought", for your and/or other folks who may read this thread going forward...

1) if the keyword search is the primary function the users want, putting that on the first tab - rather than the third - might solve that issue while still supporting the alternate views you wanted to add.

2) you don't need three subforms and three queries for a tabbed setup. remember that your user can only view one tab at a time; that's the nature of a tabbed control. you can put a single subform "behind" the tabbed control; it will "show through" all three tab pages (at least, that's the behavior in older .mdb versions of Access; you'd have to test that in the newer .accdb versions). use code behind the tab control's Change event to set the RecordSource of the subform to the desired SQL statement, depending on which tab is clicked, and whether or not the keyword search controls have values in them. note that you can put the keyword search controls "behind" the tabbed control too, so they show up on all three tab pages.

Thank you, Tina! I believe I understand your recommendation. I will circle back to this in the next couple of days and see if I can get it to work. I will post any specific operations questions that pop up while i work through it. Thanks again!

Ok! After many other distractions, upadres, and changes I was able to circle back to this problem. Sorry for the long delay, designing this database is a tertiary task and I am not a programmer by any means so things are moving slow.

I was unable to provide a single keyword search for each subform embedded wihtin each tab, so I removed the keyword search from the main form and added it to each subform. I formatted everything so on change the search controls appear to be singular. I knw this is the easy way out, and I would love to figure out the code eventually, but for now I need to field this product to my team so we can start tracking. Thanks again for your suggestions and time!

tina t
you're welcome, GJS. good work in getting something set up that suits you and your users! :) i wish i had easy access to A2016 to test some of the options i suggest in these forums; they're options that i've used successfully in .mdb versions of Access, but have not tried in .accdb versions. i would have liked to post a simple demo of my multiple-tabs/one-subform suggestion, but posting a db in an "old" version of Access wouldn't be much use. anyway, glad you solved the issue you posted about; good luck with the rest of your project! :) tina
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.