Subform Troubles

[Guest]

I have a Programs database to keep track of our recreation participants. I
originally designed this database with a Participants Table as the main table
to hold data. When I first designed the DB, I had tons of columns to hold
our Swim Lesson records for each participant who took swimming lessons. Now
rather needing to continually add columns for each set a of lessons a child
takes, I'd like to add a subform to collect the data into a Swim Lessons
Detail Table. The Participants Table is autonumbered with that column
(ParticipantID) being the Primary Key. The Swim Lessons Detail Table is also
autonumbered with thaat column (SLID) being the primary key for that table.
The Swim Lessons Detail Table has a column, ParticipantID, to link it to the
Participant Table...this field is designated a Number, Long Integer. MY
PROBLEM: When I try to make a subform to enter data into the Swim Lesson
Detail Table on the Program Data Entry Rorm that feeds into the Participants
Table, the ParticipantID comes up with an "#Error" and won't link the data.
I've tried making tables with the SLID and ParticipantID included to link the
data, and queries and any combo of the two to try to get the subform to work.
I've had success with other subforms in other databases I've made, but can't
seem to figure out what I'm doing wrong. The only difference in this
database that I can come up with over the other databases I've had success
with, is that it must maintain the Participants as unique... there is no
repeating of that data. With that #Error message in the ParticipantID field
of the subform, I'm thinking I'm having trouble with that unique number or
something. Can anyone figure out what I'm doing wrong?

Thanks!

Kass

 

[knawtn via AccessMonster.com]

One possible approach:

Check the control source of the subform. You may want to consider a PopUp
instead of a subform. (You can have a command button on the Program Data
Entry Form that will PopUp the new form). The control source of your new form
should be the Swim Lessons Detail table. Set the ParticipantID to allow dupes
in the Swim Lessons Detail table.

You want the new record to be saved in the Swim Lessons Detail table. It will
not store in a table where the ParticipantID field will not allow duplicates.

Although your description was fine, I'd need a snapshot of the DB to continue.
......but I hope this helps.

 

[Guest]

Kass,

Hmm. Please cut, paste and post the SQL of the RecordSource of both the
main form and subforms, and the LinkMasterFields and LinkChildFields
properties of the subform control.

Sprinks

 

[Guest]

I think I'm getting what you need. THis is from the Record Source of the
subform:

SELECT [Swim Lesson Detail Table].SLID, [Swim Lesson Detail
Table].ParticipantID, [Swim Lesson Detail Table].LastName, [Swim Lesson
Detail Table].FirstName, [Swim Lesson Detail Table].Level, [Swim Lesson
Detail Table].[Date Completed], [Swim Lesson Detail Table].Instructor, [Swim
Lesson Detail Table].PFI, [Swim Lesson Detail Table].Comments, [Swim Lesson
Detail Table].[Next Level], [Swim Lesson Detail Table].[Last Date Taken],
[Swim Lesson Detail Table].[Register Level] FROM [Swim Lesson Detail Table];

This is from the Record Source of the main form: Participants

In the subform subreport window...

Name and Source Object are: Swim Lesson Detail Table subform

Link Child Fields: ParticipantID
Link Master Fields: ParticipantID

Thanks for your help!

Kass

 

[Guest]

knawt,

Good suggestion...I looked and the Swim Lesson Detail Table - ParticipantID
number is set to Yes(Duplicates OK). What kind of snap-shot would help you
the most? Thanks again for your help!

Kass