Navigating unbound table

[zakary]

(Access 2003) I have a single user form that has been designed with no associated table. It's sole purpose is to collect user entered criteria then perform a database search.

I have recently added a save facility so that separate search criterias can be held in an accompanying save table. The intention is to allow users the ability to save a new criteria, delete an existing one, navigate to previous criteria saves, edit previous saves. The criterias can be quite extensive and there is a requirement to run /edit-run the same criterias at a later date as the database grows. so this is a handy feature.
Problem: While I am able to set the control source of e.g. a combobox to the savetable, I can't get record navigation to work..."DoCmd.GoToRecord , , acNext" fails, as does "DoCmd.GoToRecord , tablename, acPrevious".

I think this problem is due to the user form not being bound in some way to this new savetable.

 

[vbaInet]

How are you presenting the saved criteria?

Place it in a subform and run your search based on the data in the current record on that subform. The subform can remain linked to its source.

 

[zakary]

Thanks for your reply. I think I'm making progress. I set the userform's Record Source to the save table, set a few test control's Sources to the applicable field in the savetable, and I am able to navigate back and forwards.

The userform includes 3 subforms that contain one field each, varying in length from 20-30 records (a separate table is the source). The user enters numeric values into the 3 fields, as applicable. To save a new record into the savetable, I convert each of the 3 fields into comma-deliminated strings, then Insert Into / Update (if amending existing) as appropriate. Additional search criteria is also saved e.g. various comboboxes and Boolean switches.

Is there an automatic way to transfer the comma deliminated strings back into the 3 subforms on the main userform? ...rather than programmatically, as I did in constructing the strings in the first place?

 

[vbaInet]

Why complicate matters with 3 subforms, 3 tables and delimited strings?

If you have three search criteria save those three values in their own fields. Create a form that is bound to this table and use this form as the Source Object of a subform in your main form. The subform will be used for your searches and will not be linked to the main form.

 

[zakary]

Thanks again, that sounds so much easier! I've not too much experience with db use in general (background in different languages) and so if there's a crazy long (sometimes working) way to do something, that's me.

 

[vbaInet]

You'll get the hand of it soon Just ask as you go along.

By the way, is the list of recent searches going to be shared by multiple users or will each user have his/her own "recent searches" list?

 

[zakary]

It would be shared by multiple users and that will likely cause issues.

Currently (without the save facility) >1 user can open the .mde version and perform searches. The database is currently not split front/backend and so each user loads a copy of everything in the .mde. The search is a working demonstration though it is used widely since it greatly saves time, is more efficient and allows complex searches that would be almost impossible previously. I'm close to winning a case to have this converted into a more work-approved format (I'm laboratory based).

However, for Access and with the non split format I currently have it in, what kinds of issues might I run into in allowing record/table modification e.g. save facility? and can Access safely allow this? Thanks again.

 

[vbaInet]

No issues there, just make it a bit longer to edit a saved search by adding an edit button. If each user had their own list of saved searches then I was going to remind you to include a UserId field in the SavedSearches table.

It's best for each user to have their own front end.