Need some advice on how to do a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that returns a number of records, for which exist any number
of records in a table. I created a form that has a list box with the records
from the query. I also have a subform with the fields in the table. What I
want is to 1) select a record from the list box 2) have the subform populate
with all the corresponding records and, of couse, 3) have the subform total
values and allow for the user to input any new information. Does anyone know
has to do this?

Thank you in advance,
Nick M
 
I think you will need to be more specific about what you
are trying to do. For instance, on what table is the
query based, and what does it do with the data in the
table? Sort it? Limit it?
Before thinking about forms and subforms you need to have
a clear table relationship set up. If you have a table of
college courses with Course ID as the primary key (PK),
then you could have an enrollment table, with CourseID as
the foreign key (FK). By establishing a relationship
between CourseID in the two tables, when you add a form
based on the enrollment table to the form based on the
Courses table, the relationship will be ready to use.
If you mean to do something like select a name from a list
and have address, phone, etc. details appear, that is a
rather different situation.
 
O.K., I'm sorry I wasn't specific enough. Let me try again.

We are building a database to run a financial instrament we are managing.
One of the things that happens is that we have to track principal payments we
receive. We have a query I'll call the MASTER (we literally call it the
Master Query), which is crunching all our data on a secuirty-by-secuirty
basis. There is another table I'll call PIREC, which is where we're storing
Principal received.

Now, MASTER calculates how much principal we are theortically owed on a
secuirty. PIREC is tracking the actual amount received. They both share a
field called CUSIP (the tracking number of a security). The relationship
between MASTER and PIREC is one-to-many. I built a form of off MASTER, which
basically consists of one large list box. The user finds the CUSIP of the
security which he wants to find all payments received for. What I need is
for the subform to populate with all the records in PIREC which it
corresponds to. Or, in other words, all the principal payments we've
received for the security. Secondly, I want the user to be able to add a new
record for a given security (i.e., we received a payment for the bond).
There's a third element, the subform needs to add all the records for given
CUSIP, but I know how to do that.

Thanks for your help,
Nick M
 
The user finds the CUSIP of the
security which he wants to find all payments received for. What I need is
for the subform to populate with all the records in PIREC which it
corresponds to.
Click to expand...

One simple way to do this is to set the Master Link Field property of
the subform to the name of the (single-select only!) listbox.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I think you need to set up the one-to-many relationship
not with the key field in the query, but rather with the
key field in the query's underlying table. That will
enable you to follow John's suggestion. I am not very
knowledgeable about setting up relationships with queries,
so if I have misunderstood please excuse me.
Also, if your main form default view is Single Form and
the subform default view is Continuous Form (or
Datasheet), that will enable you to look at the main
information about the security and all of the related
payments. Each payment is a separate record in the
subform. A new payment would be added to the bottom of
the list.
 
Back
Top