Understanding forms 1

[Overmyhead2]

For Data entry with a subform..Am I right in thinking that every record entered in the subform should also include the Main forms' data also. I have a year and a month in the main form and entering about 20 days in the subform. The table shows most records without yr and mo.

 

[RickSpr]

I don't exactly understand what you're saying, but it sounds like you might have the wrong idea about how to use subforms.

Typically, you have two tables with a one-to-many relationship. You bind the main form to the parent, and the subform to the child table. In creating the subform control, you specify as master and child link fields the fields you join the tables on, in the parent and child tables respectively. (You can join on more than one field, too.)

When you navigate in the main form, the join field is used to automatically select records in the child table to be displayed in the subform. You can update or add data in the main form to update or add to the parent table, and you can update or add data in the subform to update the child table. If the join corresponds to a relationship that enforces referential integrity, you can update the join field on the main form and it will update the foreign keys in the child table (if update cascading is selected). The only thing you can't do is change the join field in the subform, but you normally wouldn't display that on the subform anyway (you'd display it on the main form).

I hope this makes sense. If it doesn't you probably need to study relational database theory a bit.

 

[rafe]

Rick is dead on.

The only thing i'd emphasize, relative to the 1st question, is that the *only* data you duplicate in the subform's data is the join field (typically small... say a long int)

for example

main table
===========
MainKey (a long int)
Address
Name
Phone#
blah, blah, blah

Sub table
============
key (another long int)
MainKey (this must match the main table's MainKey & only this)
description
amount
yakitty yak

just like rick said, you use these linked tables as data that feed into your forms

 

[Conner]

I am following this thread and it is "close" to a problem I am trying to solve. Please keep in mind that there is a solution using a "main" form and a subform, but my interest is in why this particular situation cannot be resolved:

I have a main form with a combo box. The combo box only has two values -- "Employee" and "Student." I have managed to attach an Update event to this combo box that uses and IF..Then...Else event. The name of the combo box is classification.

If Me.Classification = "Employee" then
DoCmd.OpenForm "FrmEmployee",,acNormal "[tblEveryoneBio]._ [SSN]=' " & Me.SSN & "'"

Else
DoCmd.OpenForm "FrmStudent', acNormal,,' "& Me.SSN & "'"
EndIf

The underlying source for the two forms "FrmEmployee" and "FrmStudent" are queries.

This event works flawlessly for EXISTING records. But there is no sync between edits and new records. I have tried every combinatin that I can think of from Dynaset(Inconsistent Updates) to DataEntry...allow editions...requery, etc.

How do I get these two "secondary" forms to sync with the "main" form when I edit an existing entry or add a new record?

 

[Overmyhead2]

Hope this is clearer..I'm cutting my teeth on this on so bear with me please...The form is to collect 3 items of data: Location, Date and an Amount. (1 - tblLocation to Many-tblData) (..Join on Location..)
?The Problem.. Too many redundant key strokes..
Why does the user have to enter Month and Year for every record when it will always be the same for that month's data..1 step further ..why have to enter Location # when it will be the same for 20 or so records. I put (Location #, Month,Year) in the main form & (Day and Amount) in the subform. After reading your answers, I took out Year, Month from the main form and left just Location. New records are being added to the child tbl but they have the default value in the Loc field. Maybe a subform is not the answer..

 

[rafe]

Supfs,

Data does NOT have to equal input. i have used defaults in the header for data that goes into the line items... in this case you leave the DefaultLocationTextBox unbound in the header & then you can do...

on the DefaultLocationTextBox's after update you can change the default for the LocationTextBox in the line items. in this case i tend to not make the LocationTextBox a tab stop.

Conner,

Things work ok for me but then again do things a bit differnetly, so i'll have to test it. the way i do it is *not necessiarily* better, it's just how i do it.

fyi: i leave the header unbound & then use a buffer file for the detail lines. then when user clicks ok i validate the input add data to from the header controls to the header file & the buffer file to the line item file. this works for me BUT YOUR MILAGE MAY VARY!