Search box on dashboard (1 Viewer)

Megaduck

Member
Local time
Today, 03:27
Joined
Apr 24, 2020
Messages
30
I have a main dashboard with a split form where the source is a query that displays only records with a blank field. I work at a plant where we have to check in and out trucks delivering parts. I have it setup this way so if their trailer # out is blank, it will display on the main dashboard. When they leave, just click the edit hyperlink that's in that blank field and update the record. Then it goes away to the main table. That way they don't have to scroll through hundreds of records to find the one they need to edit.

Anyway, I have searched and I think I understand how to create a search box on the dashboard that will search as you type, but this I believe would only display records where it matches the search criteria and has a blank field (based on the main query I have there). What I would like to do is if they type in the search box, I want it to search and display based on all records on the main dashboard split form. Is this a possibility or would it be easier to just place a search button on the dashboard and link it to a new split form that would accomplish the same goal?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:27
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

What you want should be possible within the same split form. The search can be based on the record source of your split form. If you're split form is based on a query, it's probably even easier.
 

Megaduck

Member
Local time
Today, 03:27
Joined
Apr 24, 2020
Messages
30
If the form I'm adding a search box to is already filtered by a query, wouldn't I need to dynamically change the record source of said form to show all records (and not just search those already filtered) when text is typed into seach? Otherwise I'm only going to see results from the original record source query, if I'm understanding this correctly.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:27
Joined
Oct 29, 2018
Messages
21,358
If the form I'm adding a search box to is already filtered by a query, wouldn't I need to dynamically change the record source of said form to show all records (and not just search those already filtered) when text is typed into seach? Otherwise I'm only going to see results from the original record source query, if I'm understanding this correctly.
Hi. It all depends on what you want. If you have a form showing some records and you want to search through them, that's easy. If you want to reset the search to display the original records, that's easy too. If you want to show all the records from the table, that's easy as well. Tell us what you want, what you have tried, where it failed, show us your code, or post a sample db, and we'll help you get what you need done.
 

Megaduck

Member
Local time
Today, 03:27
Joined
Apr 24, 2020
Messages
30
The database is pretty simple... I have a query as record source on the main form that sorts records by Date DESC and Trailer OUT # Is Null. No other fields are shown when viewing the main form. What I want to do is when text is entered into the search box, I want to see all records based on the text entered into search, essentially ignoring the first query. I have a second query for searches where each field has Like "*" & [Forms]![Dashboard]![SearchBox] & "*"

I'm just not sure how to make that work.

I've tried something like this to no avail:
Code:
Private Sub Form_AfterUpdate()
   If Searchbox = "*" Then
      Me.RecordSource = "SearchQ"
   Else
      Me.RecordSource = "BlanksQ"
   End If
End Sub
 

Megaduck

Member
Local time
Today, 03:27
Joined
Apr 24, 2020
Messages
30
If I add the Me.RecordSource codes to the userlevel codes I already have on the On_Load event, it will change the record sources based on the user's access. I just can't figure out how to do what I want. Lol
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:27
Joined
Oct 29, 2018
Messages
21,358
The database is pretty simple... I have a query as record source on the main form that sorts records by Date DESC and Trailer OUT # Is Null. No other fields are shown when viewing the main form. What I want to do is when text is entered into the search box, I want to see all records based on the text entered into search, essentially ignoring the first query. I have a second query for searches where each field has Like "*" & [Forms]![Dashboard]![SearchBox] & "*"

I'm just not sure how to make that work.

I've tried something like this to no avail:
Code:
Private Sub Form_AfterUpdate()
   If Searchbox = "*" Then
      Me.RecordSource = "SearchQ"
   Else
      Me.RecordSource = "BlanksQ"
   End If
End Sub
Okay, I think essentially what you're saying is, for example, you have a table with 10 records in it. Then, you have a query that filters that table to show only 5 records. You then use this query on a form. Now, you want to have a search box to display all 10 records again (if they all match). Correct?
 

Megaduck

Member
Local time
Today, 03:27
Joined
Apr 24, 2020
Messages
30
Okay, I think essentially what you're saying is, for example, you have a table with 10 records in it. Then, you have a query that filters that table to show only 5 records. You then use this query on a form. Now, you want to have a search box to display all 10 records again (if they all match). Correct?
Yep! That's exactly what I'm trying to do.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:27
Joined
Oct 29, 2018
Messages
21,358
Yep! That's exactly what I'm trying to do.
Okay, here's probably what I would do. If you have a "Search" button, you could try something like this in its Click event:
Code:
Dim strSQL As String

strSQL = "SELECT * FROM TableName WHERE TextFieldName Like '" & Me.SearchBox & "*'"

Me.RecordSource = strSQL
Hope that helps...
 

zeroaccess

Active member
Local time
Today, 02:27
Joined
Jan 30, 2020
Messages
671
If the form I'm adding a search box to is already filtered by a query, wouldn't I need to dynamically change the record source of said form to show all records (and not just search those already filtered) when text is typed into seach? Otherwise I'm only going to see results from the original record source query, if I'm understanding this correctly.
If I'm understanding your need correctly, you can do what I do for a search form. Pick something that you know will result in 0 records. For example, if you have a Type or a Status field, you can set a filter on the form for Type or Status 0. Since there is no such thing, it will load up blank, yet return results (which is just a different filter) after using the search box, with no need to change the record source.

filter.PNG


Save the form with that property in place.

However, it may be more convenient in your case to put a box in the header of your split form. In this case, my text box is called txtGoTo. After Update of the unbound text box:

SQL:
    DoCmd.ApplyFilter , "[fieldnamehere]=" & Me.txtGoTo & ""

I have another button next to it to clear the filter:

SQL:
Private Sub cmdClear_Click()
    DoCmd.SetFilter , Null
    Me.txtGoTo = Null
End Sub
 

Megaduck

Member
Local time
Today, 03:27
Joined
Apr 24, 2020
Messages
30
Hi. You're welcome. Let us know how it goes. Good luck.

This code worked perfectly for what I was trying to accomplish:
Code:
Private Sub SearchBox_AfterUpdate()
    Dim strSQL As String
    If IsNull(SearchBox) Or SearchBox = "" Then
        Me.RecordSource = "Truck Log Query"
    Else
        strSQL = "SELECT * FROM [Truck Log] WHERE [Company] Like '" & Me.SearchBox & "*' OR [Cab #] Like '" & Me.SearchBox & "*' OR [Trailer In #] Like '" & Me.SearchBox & "*' OR [Trailer Out #] Like '" & Me.SearchBox & "*' OR [Officer] Like '" & Me.SearchBox & "*' OR [Date] Like '" & Me.SearchBox & "*'"
        Me.RecordSource = strSQL
    End If
End Sub

I put it in the After Update just to test out without having to create a button. Everything worked great. I tried putting it in the On Change event but for whatever reason, every letter typed moves the cursor down to the continuous form and if you type any more, it edits the first field of the first record with those letters. Not sure why it's refreshing after each keystroke like that. Lol
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:27
Joined
Oct 29, 2018
Messages
21,358
This code worked perfectly for what I was trying to accomplish:
Code:
Private Sub SearchBox_AfterUpdate()
    Dim strSQL As String
    If IsNull(SearchBox) Or SearchBox = "" Then
        Me.RecordSource = "Truck Log Query"
    Else
        strSQL = "SELECT * FROM [Truck Log] WHERE [Company] Like '" & Me.SearchBox & "*' OR [Cab #] Like '" & Me.SearchBox & "*' OR [Trailer In #] Like '" & Me.SearchBox & "*' OR [Trailer Out #] Like '" & Me.SearchBox & "*' OR [Officer] Like '" & Me.SearchBox & "*' OR [Date] Like '" & Me.SearchBox & "*'"
        Me.RecordSource = strSQL
    End If
End Sub

I put it in the After Update just to test out without having to create a button. Everything worked great. I tried putting it in the On Change event but for whatever reason, every letter typed moves the cursor down to the continuous form and if you type any more, it edits the first field of the first record with those letters. Not sure why it's refreshing after each keystroke like that. Lol
Hi. Glad to hear you got it to work. The Change event fires every time you type in a key (or delete one). Good luck with your project.
 

Users who are viewing this thread

Top Bottom