save-previous-next facility

[mesab66]

I have a user-form whose job is to gather various search criteria entered by the user, construct an SQL query then perform the search on the main database. Various switches are included to allow fine tuning of the search process.
The main search criteria is stored in the table table tblSrchCrt (comprising 3 fields of user-entered search data).

tblSrchCrt:






What I’d like to do is have the ability for the user to recall previous search criteria thus enabling the ability to further amend existing search criteria (if the current criteria needs to be amended at a later date). The actual criteria is much larger than this example, so this facility would be handy. Additionally, I’d like the switch states (Boolean yes/no primarily) to be saved also.

To facilitate this, I’m thinking about adding ‘Save’, ‘Previous’ and ‘Next’ buttons, then coding as appropriate underneath the buttons.
I could replicate tblSrchCrt manually, say, 20 times to allow for a history of 20 search criterias…adding a number between 1-20 to the table name then navigating between tables using the number as a pointer.

Is there a more standard way to do this?

[June7]

Why would you replicate tblSrchCrt? Why not just 1 table?

I have a search procedure that saves the filter criteria as a text string into a table and that table is displayed in a listbox. But your approach is probably just as reasonable.

[mesab66]

Thanks for the reply,
Replicating the criteria table, tblSrchCrt, 20 times does look a bit daft. What I think should have said was create 3x20 fields in tblSrchCrt and cycle through each criteria history (block of 3 fields) using a suitable numeric increment (as the pointer) added to each block of 3 field names (I'm building the SQL query within VBA)...achieving the same thing in one table. Again, I'd be looking to include a few other items e.g. switch states.

However, I was wondering if there's a more standard/acceptable approach (not being too db savvy myself)? I'd guess there's many approaches dependent on what's being saved.

[CJ_London]

I'd guess there's many approaches dependent on what's being saved.

I do what June7 does - main benefit is that it is not limited to one scenario - the 'routine' can then be used anywhere, whereas yours is very specific to a single form/recordsource with specific criteria elements. You can also easily extend the functionality to include say a userid so the list can be displayed unique to a user.

[June7]

Why would each search need its own set of 3 fields? Similar name fields indicates non-normalized data structure. Seems to complicate storing and retrieving.

[mesab66]

Thanks June7...penny finally dropped re converting to string