form for a lot of tables

[Liat]

Hello,

I used to have a table with suits information. It
contained player details, 5 paragraphs of evaluations,
each paragraph contained 3 evaluations and 5 paragraphs
of payments, each paragraph contained up to 20 payments.

Since I encountered a problem with the query on the
payments (a big query with a lot of OR), I sent a
question and have been told yjat I should normalize.

I normalized as follows:
tblPlayers
PlayerID (primary key)
PlayerName
PlayerAddress

tblSuits
SuitID (primary key)
SuitDate
SuitComments

tblPlayerSuits
PlaySuitID (primary key)
PlayerID
SuitID

tblSuitParas
SuitParasID (primary key)
PlaySuitID (composite primary key with ParagraphNum)
ParagraphNum (composite primary key with PlaySuitID)

tblSuitEvals
SuitParasID (composite primary key with EvalID)
EvalID (composite primary key with SuitParasID)
EvalQuantity

tblSuitPays
SuitParasID (composite primary key with PaymentID)
PaymentID (composite primary key with SuitParasID)
PaymentAmt

Now, I want to create a form that will fill those tables
(that used to be 1 table, for it I created a form).
I really don't know how to do that - in the form creation
wizard there is an option to use only one table/query.
Should I create a query and from it create the form?

How do I show all the details of each record in the form?

Thanks a lot,
Liat

 

[John Vinson]

Now, I want to create a form that will fill those tables
(that used to be 1 table, for it I created a form).

Use Subforms. The simplest way to handle one-to-many relationships is
to use a Form for the "one" side table, with one or more Subforms for
the "many" side tables. Using the fields you specified in the
relationship window as the Master/Child Link Field property of the
subform will keep the tables in synch.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Liat]

Thank you very much for your help.

I tried to do so but encountered some problems:

My main table is tblSuit. This table contains the details
and is related to the table tblSuitPlayer. This table
only connects between the tblSuit and the tblPlayer (and
also to another table which is not important now).

I can't add the subform for the tblPlayer because the
relation between them is through another table (the
tblSuitPlayer).

Does that meen that I need 2 hierarchies of subforms?
* If so, I tried and I got the following alert - "Object
variable or With block variable not set" and
then, "Microsoft Access can't find the field 'child8'
reffered to in your expression"
What did I do wrong?

* The form doesn't look noce with all the subform inside
(I need a lot of subforms - 5). Is there another option?

Thanks a lot,
Liat

 

[John Vinson]

Does that meen that I need 2 hierarchies of subforms?
* If so, I tried and I got the following alert - "Object
variable or With block variable not set" and
then, "Microsoft Access can't find the field 'child8'
reffered to in your expression"
What did I do wrong?

Since I have no way of knowing what you did, it's hard to say what you
did wrong!

But... having One Great Master Form that simultaneously updates
everything in your database is not typically either necessary nor
practical. Take a look at the Northwind sample database Orders form -
it deals with a many to many (Orders to Items) relationship very much
like yours. You would have separate forms for the two "one" sides,
perhaps each with a subform for the resolver table; you could have a
Switchboard allowing the user to open whichever form (with one or more
subforms) is appropriate at the time; you could (though performance
might be abysmal) have an unbound Form with multiple subforms, perhaps
on tab pages.


John W. Vinson[MVP]
(no longer chatting for now)