Any better ways?

[Mark Andrews]

I am designing a system that has the user navigate to a form with a listbox.
The user can filter the listbox and change the underlying query that drives
the list box in various ways. Once they have filtered the listbox how they
want they double click on a row and a new form opens which shows detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters it
down to 45 and then clicks on number 23 and the new form opens on record 23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have queries
that pull data from multiple tables (example: list accounts and show sum of
sales in one column and sort descending by sales) and allow filtering on
various columns (example filter where sales > 50000) and once I do that I
need to figure out the best way to show something like (when the user clicks
on record 23 I would like the detail form to show the underlying 45 accounts
sorted by sales descending). However the user would only be editing details
on accounts.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and sorts by
Autonumber column (this achieves the same filtering and sorting that the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work. Guessing
my front end
will grow like crazy.

Thanks in advance,
Mark

 

[Jeanette Cunningham]

Hi Mark,
have a look at the search screen pictures on Albert Kallal's website.

http://www.members.shaw.ca/AlbertKallal/Search/index.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Mark Andrews]

Jeanette,
The search screen he has is very similar to what I have but is restricted by
having the searching using an updatable recordset.

My goal and question is how best to do searching off related data
(particularily sums of numbers associated with the main records), a
non-updateble recordset
and still update a recordset that is filtered in a similar way.
Most web based CRM systems have this concept.

I'm using a list box with multiple columns and could use a continuous sub
form with an "edit" button on every row (both have pros and cons in my
opinion).

Any other help is appreciated,
Mark

 

[Jeanette Cunningham]

Mark
In access, once user has selected the record they want, you open another
screen showing either all the records that match the selected record or the
single record to edit.

If I understand what you are trying to do, you want user to select a record
from the listbox, open a new form showing all the related records.
The next part is a bit unclear about what you need next - you could use a
multi-select list box ( on the second form) to let user select which
accounts to edit and open a form with all those accounts available - based
on an updateable recordset. Or - you could let the user select one record at
a time and open an updateable recordset to edit each account details one at
a time. Each time they edited one of the accounts, you would return them to
the second form to select the next account they want to edit.

Often the search screen/s are not for editing, but eventually after how ever
many search screens you need, user selects which record they want to edit,
you can open an updateable recordset - easy with a bound form - to the
record that needs to be edited.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Mark Andrews]

Jeanette,

I think my question is still a little unclear. It's a complicated question,
I'll try to clarify:

Form #1 (has listbox with 100 records in listbox (where and order by clauses
in underlying query), user clicks on number 15 in that listbox)

Form #2 should come up with 100 records and find record number 15

In general this is easy to do, except in this condition:

I want form #1 to show accountid, accountname, totalsales (only sales over
50K) sorted by totalsales (so it's a query with two tables, a group by
condition and a having statement (non updatable)

In this circumstance I can make form #2 come up with a SINGLE record (after
user selects record #15).

My question is:
How would I have form #2 come up with the 100 records with sales over 50K
sorted by totalsales. Even though form #2 is showing account details
with a subform showing individual sales records.

I made it work but had to use a temporary table and that causes my database
to grow after every use. Looking for a better way.


Thanks in advance for your help,
Mark

 

[Jeanette Cunningham]

Mark,
you are asking for:
form #1 has listbox with 100 records in listbox , shows accountid,
accountname, totalsales (only sales over

50K) sorted by totalsales

How would I have form #2 come up with the 100 records with sales over 50K

sorted by totalsales. Even though form #2 is showing account details.

Why do you want form#2 to show the same thing as form#1?


If form#1 has the accountid, you could use the accountid to open a form to
edit the details for that account (from form #1)
Sorry, but the question is not making much sense to me.
Maybe someone else could help with this.
A post in the queries discussion group may help you with this.