problems with linked tables in a subform

Hi all,

I am experiencing difficulties with a subform in a database I am designing for a dance group.

First, a little background about the database. There are 2 main parts:
a) a library of CD information and dance information as well as which dances can be done to which songs
b) a tool to plan monthly dance events

Now for my problem:
I have a main form that's built on the PlanProgram table (see attached relationship diagram) and then a subform that runs from the PlanProgramDetail table. Everything works OK up to that point. However, when I go into the subform's record source and add the table Lessons - this is where it "breaks". What happens is when I go back out to the main form and go to enter data in the subform the child field does not behave normally. It doesn't automatically enter the main form's recno_plnprgm number into the subform's field of the same name. Prior to adding the table called Lessons to the subform it did automatically insert the recno_plnprgm number from the main form to the subform.

The reason I want to add Lessons to the subform is so I can update that table's information as well.

Any "fix-it" ideas would be greatly appreciated.

Thanks, Heather

Is the RecordSource of your SubForm updateable? If not then search Access help (not VBA help) for "Troubleshoot queries".

Yes, the RecordSource of the subform is updateable. I am trying to build the subform on top of the tables themselves, not the queries.

So if I understand you correctly, you have managed to make the RecordSource of your SubForm two tables but *not* in a query? How did you accomplish that?

Well, I may have the terminology incorrect. Hopefully you'll be able to tell from this description of what I did -
in the design view of the subform I went into the form's RecordSource and clicked on the ... button which took me into the Query Builder. I added the Lessons table to the PlanProgramDetail table and got a SQL statement like this:

SELECT PlanProgramDetail.* FROM PlanProgramDetail LEFT JOIN Lessons ON PlanProgramDetail.recno_plnprgmdtl = Lessons.recno_plnprgmdtl ORDER BY PlanProgramDetail.recno_plnprgm, PlanProgramDetail.dancenumber;

Have you recently checked the master/child links? Sometimes if you change the RecordSource, Access looses the information.

I checked on the Source Object and the Link Child Fields and Link Master Fields of the subform. They still contain the correct information after I added more tables to the query of the subform (thus changing the subform's RecordSource). The strange thing is, the subform does display the correct records in the subform that belong to the main form, it's just when I go to add a new record in the subform that the linked child field does not automatically populate with the main form's master field data. Is there a simple way to fix this? Maybe I don't have something set right. Or, is there some code that I should insert to have it populate this critical field?

Shouldn't the relation between the lesson tbl and the planprogamdetail tbl be the other way around?

Btw, if you try it, make a copy of the mdb first

No, I believe the relationship between these tables is set up correctly. Initially, I was going to just add the lesson fields to the PlanProgramDetail table but not every recno_plnprgmdtl is also a lesson - only some are associated with lessons. I elected to make Lessons a table unto itself to cut down on null fields in the PlanProgramDetail table. The Lessons table only stores data for those recno_plnprgmdtl records which have lesson data.

Next, I am going to try inserting some code to get the recno_plnprgm field in the subform to get it's value from the main form's recno_plnprgm field. Maybe this will solve the problem of the subform not automatically inserting the number. If anyone have any ideas about other ways to solve this problem, they would certainly be welcomed.