report/form

[boris]

I hope someone out there is familiar with the autofilter
command in Excel. It is the basis for the logic which I'd
like to put into an Access project. Basically, I'd like
to have a report, which in essence is a form. In other
words, I want the user to see what looks like a report,
with dropdowns for the column headers. These dropdowns,
when selected, act as filters, in the sense that each and
any one's selection of some criteria parameter limits what
shows up in the others. So if I have:

Name Address Phone Type
Bob Lowell Lane 215-xxx-xxxx regular
john Terry drive 215-xxx-xxxx irregular
sheila lowell lane 310-xxx-xxxx regular

The word "Name" and each of the column headings is a
dropdown when I see this. If I select within
address "lowell lane", then when I do the dropdown of
name, I only want to see bob and sheila. If I then
select "contains 215" in the phone, I only want to see Bob.

Hope that makes sense. This is step one, and is more the
reporting functionality of what I want.

The form functionality is that I want the user to be able
to go to any one of these "cells" (to equate it to Excel)
and type in information (and by typing, I mean I want the
entry field to be able to have the same kinds of
characteristics as in any form, namely dropdown, combo
box, straight entry, spinner, etc.). I also want a button
for update on the right of each record, which of course
appears and disappears along with the record, as the
filters are selected. Once a change is "typed" in,
whether it's in filtered or unfiltered mode, I want the
user to be able to hit the update button on the right of
that record and have whichever of the fields were changed
updated into the record in the right table.

Conceptually, is this possible?

Thanks. Boris

 

[George Nicholson]

Yes, it is doable (although I'm not sure you'd really need or want your
update buttons)

1) Create an updatable query with the data you want on your form.
2) Create a continuous-forms form based on that query. This should look like
a datasheet when opened.
3) In the FormHeader, create combo boxes for each field. These will control
your filter.
4) Create UNION queries for the RowSource for each combobox (this allows you
to include "(All)" as a filter option along with any existing values)
5) Write a "ApplyCriteria" function that takes the current values of each
combobox and generates a single string that is then assigned to
MyForm.Filter (or Turn the filter off if "(All)" is selected across the
board).
(this will likely be the trickiest step)
6) Create _AfterUpdate code for each combobox that calls ApplyCriteria

I would recommend limiting steps 3 thru 6 to only two or three fields to
start. Once you get those working it should very straighforward to add
additional fields & complexity to your filtering, but trying to do more than
that at once might be overwhelming.

HTH,

 

[boris]

thanks much, George. Will give this a go and come back if
needed.

Boris