One form for different queries?

  • Thread starter Thread starter djtommye
  • Start date Start date
D

djtommye

I have a table of clients and each line has a field indicating the state
of our relationship. (e.g. past customer, current customer, etc).

I would like to be able to view their information in a form, where I
can go from one client to the next.

For instance, I want to look through each of my past clients' records.
Or maybe my current client's records, etc.

However, I would prefer NOT to have to create a different form for each
of these; as the data is all the same. (My database has been this way
for a while, and it's a pain to manage because each change has to be
duplicated for each of the identical forms).

I thought I was being smart - I created macros and queries so that it
selects those entries matching the state I want to look at and then
creates a new, temporary, table. This table is then viewed by the
form.

That way - I have one form - populated by the different queries.

However, I then realized that I can't change any information in the
form because it only changes the info in the temporary table and not in
the master (e.g. "REAL") table.

I hope this is making sense...

I'm wondering if I'm missing some really easy method of doing this?
 
Any reason why you don't simply use the form's 'Filter by Selection' or
'Filter by Form' tool buttons.
That's what they're for.
Select the Client State field while showing any Past Client, click the
Filter by Selection tool button, and only Past Clients will be shown.
Click on a particular Client Name, again click the Filter by Selection
button, and only that Client's records will show, etc.

Filter by Form is just as simple if you know in advance which records you
wish to view.
 
The trick IS to view all data through one form only... But it isn't done
through setting up temp tables, etc! Your description does make sense and
although you are almost their you fell at the last hurdle! Why do you need a
temp table? All you need to do is to filter out all the irrelevant data,
i.e. show only current clients, or show only past clients, or show all
clients.

One way is to place 3 buttons on your form and set a different SQL statement
for the form's RecordSource in each of them. SQL is easily written in the
query grid and copied to the button's OnClick event, something like
Me.MyForm.RecordSource = "The SQL copied from the query grid" Use a
different criteria in the query grid so that the 3 RecordSources have
different Where clauses in them, Past, Present or Nothing.

You can mess about with the buttons visible property to show each button in
sequence, placing them on top of each other... or you can use a combobox and
select the view you want... etc. There's lots of fun to be had!

HTH.

Tom.
 
Can I do this from the switchboard - e.g. via a macro, etc? Because
open the form up so often, usually looking at different "sources", i
would be nice to do it with the click of a single button.


Thanks again
 
Okay - I found *how* to do this with a macro...
SetValue the Filter property of the form, and SetValue the FilterOn to
TRUE.

However, I can't get the filter property to set right. I did a Filter
By Selection, then went into design view and tried to paste that into
the expression field.

However, the State field I'm filtering on does a lookup to show the
values, so the Filter field shows:

((Lookup_StateCombo.State="Closed - Completed"))

Trying to put that in the filter property with SetValue gives an error
because it is a lookup.

SO - I tried putting:
StateCombo.State=7

(7 is the ID for "Closed - Completed" in the Event State table, where
it does the lookup), but I still get an error about automation.

Suggestions?

Tommy
 
Back
Top