Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2018, 05:09 PM   #1
tssparky
Newly Registered User
 
Join Date: May 2017
Posts: 19
Thanks: 10
Thanked 0 Times in 0 Posts
tssparky is on a distinguished road
Help with saving prior search terms

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

tssparky is offline   Reply With Quote
Old 09-13-2018, 06:48 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,178
Thanks: 57
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Help with saving prior search terms

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
tssparky (09-13-2018)
Old 09-13-2018, 07:29 PM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,580
Thanks: 55
Thanked 2,090 Times in 2,002 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Arrow Re: Help with saving prior search terms

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.
Attached Files
File Type: zip sampleSavedSearch.zip (68.0 KB, 4 views)

__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 09-13-2018 at 07:40 PM.
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
tssparky (09-13-2018)
Old 09-13-2018, 07:37 PM   #4
tssparky
Newly Registered User
 
Join Date: May 2017
Posts: 19
Thanks: 10
Thanked 0 Times in 0 Posts
tssparky is on a distinguished road
Re: Help with saving prior search terms

Quote:
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 is offline   Reply With Quote
Old 09-13-2018, 07:40 PM   #5
tssparky
Newly Registered User
 
Join Date: May 2017
Posts: 19
Thanks: 10
Thanked 0 Times in 0 Posts
tssparky is on a distinguished road
Re: Help with saving prior search terms

Quote:
Originally Posted by arnelgp View Post
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....
tssparky is offline   Reply With Quote
Old 09-13-2018, 07:44 PM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,580
Thanks: 55
Thanked 2,090 Times in 2,002 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Help with saving prior search terms

you may have to downliad again if frmEmployees2 is not on the db. i edited my post.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-14-2018, 05:01 AM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,178
Thanks: 57
Thanked 1,127 Times in 1,029 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Help with saving prior search terms

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.


__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how: search dynamically using mult search filter terms [based on john big booty's db] Minddumps Queries 6 04-25-2014 11:22 AM
VBA Basic Terms Dream.Van Modules & VBA 3 04-27-2011 11:59 AM
Terms of Use lisarmj General 5 07-31-2008 07:30 PM
Query Criteria to change with Search Terms Adzi Queries 2 01-13-2007 08:11 PM
Saving last 5 search items crich21 Forms 1 02-13-2003 05:02 AM




All times are GMT -8. The time now is 03:23 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World