I have recently gone from an Access 97 stand-alone database to SQL 7.0
using Access 2000 ADP as front end.
One of the things I could do very easily in Acc 97 was to select
records in a related table that were filtered according to the primary
record selected in the main table. For example, The main table listed
primary portfolios and the second table listed individuals. An
intermediate table recorded relationships between the primary
portfolio and individuals eg Mr Smith is a member of Portfolio A.
I would set up a combo box on a form to select the portfolio - let's
say "portfolio A" in this example.
This would be followed by another combo box that referred to the value
selected in the first combo box and only returned associated records
from the second table. So in this example, the second combo box only
showed Mr Smith and did not show the hundreds of other individuals who
are members of other portfolio.
This process was a breeze in Access 97. Try as I might, I cannot get
this to work effectively in Access 2000 / SQL 7.0 other than with a
very clunky and unsatisfactory process.
Help would be greatly appreciated. |
Request for Question Clarification by
hammer-ga
on
04 Jul 2003 05:49 PDT
Could you describe the "clunky and unsatisfactory process" to ensure
that we don't take that route?
- Hammer
|
Clarification of Question by
yuleba-ga
on
06 Jul 2003 16:49 PDT
I have set up a form and subform to record new deposits to a
portfolio. Data is stored in 2 tables - primary table records details
at portfolio level and secondary table at member level. The tables are
linked by a one to many relationship.
The main form has a combo box to select the relevant portfolio and
stores the Portfolio ID in field. The subform has a combo box to
select individual members. The source for subform combo looks at a
view that links the record details entered in main form and shows only
members that have a relationship with the selected portfolio.
The problems with this approach are as follows:
1.
|
Clarification of Question by
yuleba-ga
on
06 Jul 2003 16:53 PDT
I have set up a form and subform to record new deposits to a
portfolio. Data is stored in 2 tables - primary table records details
at portfolio level and secondary table at member level. The tables are
linked by a one to many relationship.
The main form has a combo box to select the relevant portfolio and
stores the Portfolio ID in field. The subform has a combo box to
select individual members. The source for subform combo looks at a
view that links the record details entered in main form and shows only
members that have a relationship with the selected portfolio.
The problems with this approach are as follows:
1. Cannot get form to auto-refresh. Records will refresh if command is
run from menu bar, but not otherwise.
2. When multiple entries are made, the subform combo box (after manual
refresh) shows members related to all portfolios selected in main form
- not just the current entry.
|
Request for Question Clarification by
hammer-ga
on
23 Jul 2003 05:24 PDT
Yuleba,
I know how to do most of what you need. The part that does not appear
to be doable is where you are using a View. The ADP dos not appear to
be able to dynamically pass a parameter to a View. You would need to
use an unbound combo box to select your PortfolioID, which would use
event programming to refresh the subform and its combo box. You would
need to recreate the view's underlying SQL in the event programming,
or use a stored procedure instead. I do not have your application, so
I cannot provide code specific to your database, however, I can
provide sample code demonstrating the technique.
Would this be acceptable as an answer?
- Hammer
|
Clarification of Question by
yuleba-ga
on
24 Jul 2003 15:37 PDT
Hammert
This is something that I need to be able to apply to Access 2000
front-end on SQL 7.0 server using ADP. Is the sample code that you
mention specific to this configuration? If so, I am happy to accept if
you believe it will allow me to do what I am trying to do.
Thanks
Yuleba
|
