Update data from form based on dynamic table creation from query 1

[BFP]

Hi all.

I have an Access table and associated form linked to an Excel Spreadsheet that will be used by about 10 people.

I am wondering what would be the best way to accomplish the following:

1) Allow each individual to filter the data based on about 6 different criterion.
2) Automatically populate a form based on 1.
3) Allow changes by the user that will propagate back to the table that is linked to the Excel spreadsheet.


For 1 and 2, I have created a form with the desired filter parameters that runs a Make Table query (as I do not want to require the dB users to filter things manually).

My questions are
* Am I on the right track? If not, what is the BEST way to do this?
* What do I need to do to accomplish 3?

The main issues I am facing are: a) A newly created table based on a query will not be linked to the "master" data; (Recall that a make table query deletes the table from the last time the query was run, if it exists) b) The solution needs to work with more than one user running a query at the same time.

Example

Suppose the following was the original spreadsheet:

Name Status Priority
Joe S1 P1
Dan S2 P2
Dan S3 P3
...
Bob S4 P1

Now suppose the user only wants to see Name = Dan and Status = S2, the resulting subset after running the create table qurey would then be
Dan S2 P2

The idea is to now let Dan change the Status or Priority without opening the original table and finding the record.

What is the best way to do this?

Thanks in advanced,

BFP

 

[Eupher]

Hi, BFP,

FWIW, I would not use make table queries in this arrangement, for precisely the reasons you have posted. The same logic that produces the temporary tables can be used to simply filter the original data. And, depending on how you are managing the workflow through your forms, this can be totally transparent to the user. Just depends on how you want to set it up.

In your example above, you could easily create one or or a series of cascading comboboxes that would precisely filter the records. And, since you would be looking directly at the original data, no worries about getting the data to/from temp tables.

Ken S.

 

[BFP]

Hi Ken.

Thanks for the advice. I think that will work like a charm using DoCmd.ApplyFilter on a change event.

Just one small issue: in a seamless use case, in order to activate the filter, I actually need to change the value (say in the combo box as you suggested.) If I make the first record a "dummy", then this is really not a big deal, but it will throw a person's total record count off by one in some instances.

Any simple yet elegant workaround to this.

Thanks,

Dan