Copy form data to a table

G

Gunner

I have a database application where the user enters data into a form (Main
Form) and that data is used to search the database (Main Table). No problem
there using queries to do it.

What I'd like to do is have a button on the form the user could press that
would store the search parameters the user has entered for their search in a
separate table (StoredSearches). The form fields that they use for the
search are unbound and most are combo boxes tied to queries that allows the
user to select a default value.

Sort of build a table that tracks or stores the searches. I'd also really
like to allow the user to pull up the previous searches, clicking a button,
and repopulate the search fields using one of the previous searches.

Any idea how to approach this?

Thanks,

Scott
 
T

tina

that sounds like a cool idea, really. have you tried building a table with
fields to match the controls you've already got on the Search form? i'd make
sure the data type of each field in the new table is appropriate, then bind
the table to the form, and each field to the corresponding control in the
form.

you could, for instance, open the form and go instantly to a new record, so
the user can create a new search immediately. add a field in the table (and
control on the form) for a "SearchName", and the user can select the search
parameters record s/he wants (from a droplist in the header section, maybe)
and edit it, or run it as is. if you have multiple users, you could go an
extra step: add another field in the table for the user's name or ID, and
the user can filter for his/her own searches only - or perhaps you want to
prevent a user from seeing others' search parameter records, or see and run
any records but only edit his/her own.

hth
 
G

Gunner

Tina,

After playing around with it I decided to do what you suggested. I have the
form set to "Add a New Record" when it opens and I've created buttons for
the user to go back and forth through the records from the form and a "Save
Search" button. It works very well.

I can't believe I didn't think of it sooner. I know I run searches, look at
the data and then later on I'll go back and rerun the search to print a
report. Now I just have to go to the previous search and it populates all
of the search fields for me. I'm going to create a pop-up form with the
Search Table on it so that when a record is select it sets the focus of the
main form to that record. One-click and it will be set to search!

Thanks!!

Scott
 
T

tina

you're very welcome, Scott. :)
using parameters from multiple form controls as criteria for queries, is
something i've done very little of - and not in a really "flexible for the
user" way. if you'd be willing to share a copy of your form/query setup with
me, i'd really appreciate it!
thx, tina :)
tta JUST cce DELETE ss1 ALL at THE ya CAPITAL hoo LETTERS dotcom
and the spaces, to get the "real" address.
 
S

Scott Doyle

Tina,

I think you would laugh at what I've made if you have any experience at all
with making Access applications.

Mine is an application used by Ballistics Experts to search for firearms
based on the marks placed on bullets by a barrel. In cases where we have no
gun the examiner will take measurements from the bullet and enter them into
the search form. Running the query then produces a list of firearms that
could have fired the bullet.

The data in the database is limited to Law Enforcements Ballistics Experts
only.

It works very well though and is used around the world by most examiners.
I'm working on an update and adding a few new features.

As usual though the little modification we've talked about has produced a
problem that I have to fix now. I use the Tab Control on the main form.
There are six tabs. One is a welcome page, the second the search page, the
third info entered for reports, a fourth for buttons that open custom
results, a fifth that contains a small calculation exercise, and a sixth
that has buttons that open Help files (forms).

Now that I've tied the main form to a database whenever I click a button to
open another form the focus of the tab control on the main form goes to the
last tab (6) automatically. I don't want it to do that. Any idea how I can
keep the tab control on the active tab when the search is run?

Just something really annoying for me.

Thanks,

Scott
 
T

tina

well, the focus normally would not move at all just because another form is
opened. my first thought would be that there is something in the code that
is forcing the change in focus. have you tried setting up a break in the
code behind the button click (you can do this in VBA code, or an macros) and
then stepping through the code to observe what is happening?

as for laughing at your setup, i doubt it. almost any developer, even the
experts, have their strong and weak areas - and i'm very far from an expert!
the search setup you've built is an area i'm especially weak in, and i'm
sure i could learn from your design. as for the data being confidential - of
course. i'd only like to see the form/query setup, i'd expect you to delete
all table data from anything you sent me. as a side benefit, i can take a
look at your focus issue and see if i can pin it down. thanks, one way or
the other, and hopefully my comments in previous paragraph will be of some
help to you.
 
Top