Question Help with saving prior search terms (1 Viewer)

tssparky

Registered User.
Local time
Today, 14:46
Joined
May 24, 2017
Messages
23
Hello,


So I want to be able to save prior search terms used to search our database so they can be called with, for example, a back button and re perform the search.


I can work out how to save the data of one search but not sure how I would go about doing that for the last say 5 searched items.




Also

If I use 'On Current' trigger to save the data in the Textbox, how do I call that with a button click? (Sorry new to access) Is it Re-Dim?



Want to be able to click back and forth to see results.


So In short.


Want to save the search term, perform another search, go back and see last search and then go forward to see latest search. would only need to go say 4 searches back.


Thanks in Advance


Nick
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:46
Joined
Feb 28, 2001
Messages
27,001
I can work out how to save the data of one search but not sure how I would go about doing that for the last say 5 searched items.

The only way to do that is to maintain a separate table of the most recent search terms, which you would capture at the time you clicked your SEARCH command button.

If you only wanted the last search, you would just not clear it by default. But for more than one set of search parameters, you must save them and then come up with a method to recall them so that you know you wanted to re-use the third-to-last search, for example. This might be rather complex since a form can't be bound to two disjoint tables at the same time. So EITHER the search history table or the main data table must be bound to the search controls, and the other table operations will be filled in by VBA action.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:46
Joined
May 7, 2009
Messages
19,169
maybe it would be more effective to use unbound combo with Value List as row source type than the textbox.
set the Limit to list property to yes and add code to not in a list event.
see frmEmployees on the sample.
see the code behind the form.

you can also make the combo bound to a separate table so each item not in the list is save on this table. when you open again the form the previous search you made are available.
see frmEmploees2.
 

Attachments

  • sampleSavedSearch.zip
    68 KB · Views: 56
Last edited:

tssparky

Registered User.
Local time
Today, 14:46
Joined
May 24, 2017
Messages
23
So EITHER the search history table or the main data table must be bound to the search controls, and the other table operations will be filled in by VBA action.


I tried this

Code:
Private Function CopyCurrentRecord()

     Dim strSQL As String
     Dim strSearchTerm As String
     strSearchTerm = Me.Text38
     strSQL = _
        "INSERT INTO SearchData_T " & _
         "SELECT * " & _
         "FROM [strSearchTerm] " & _
         "WHERE SearchTerm = " & strSearchTerm
      ' ensure current record is saved
       Me.Dirty = False
       ' execute SQL statement
       CurrentDb.Execute strSQL, dbFailOnError

End Function
but I get an error "database engine can't find the input table or Query 'strSearchTerm'


Am I on the right track?
 

tssparky

Registered User.
Local time
Today, 14:46
Joined
May 24, 2017
Messages
23
maybe it would be more effective to use unbound combo with Value List as row source type than the textbox.
set the Limit to list property to yes and add code to not in a list event.
see frmEmployees on the sample.
see the code behind the form.


Will try that now, this was one of my other ideas but had no idea on how to do it.. Thanks...



Will post back how I go....
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:46
Joined
May 7, 2009
Messages
19,169
you may have to downliad again if frmEmployees2 is not on the db. i edited my post.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:46
Joined
Feb 28, 2001
Messages
27,001
Arnel's comments reminded me that though you can't bind a form to two different recordsets, you CAN make a .RowSource query in a combo box that is not from the same table as the form's .Recordsource, and if you made a big combo box (and limited the size of your search items), you might be able to save search values that way.
 

Users who are viewing this thread

Top Bottom