Subform Issues

[beaverx37]

I am working with the student database template. It has a Camper table, Guardian table, Campers and Guardians table, Campers extended query, Guardians extended query, and Campers and Guardians extended query. It also has a Camper Details form which is based off Camper Extended and uses a guardian subform to link the guardian to the camper. I have made quite a bit of modifications to the template to make it what I need. The guardian subform was a datasheet, and I have now made it a continuous form so that the user can select two guardians in the form view. Now all of a sudden when I select a guardian (combo box) it tells me Field cannot be updated but then it inputs the information.



And when I try to select the second guardian, it says "The Microsoft Office Access database engine cannot find a record in the table 'Campers' with key matching field(s) 'CamperID'

I cannot find anywhere on the form or the subform that says anything about CamperID. 'Campers and Guardians' and 'Campers and Guardians Extended' are the only two places where 'Camper ID' is used. 'CamperID' isn't anywhere. I went back to the template, found where "StudentID' was used so I added it to my database exactly how it is in the student template and that didn't fix the problem. The table 'Camper' is used in this form as part of a query, but 'CamperID' is not one of the values found on that table there either.

I copied the 'Guardian Subform' from the student template and pasted it into mine, made the correct changes to make the form compatible with my database and when I swapped them it is doing the same thing. Except it makes me enter a Guardian Extended.ID Parameter Value and then says the exact same things as my original 'Guardian Subform'

I'm sure it is something crazy simple that I have overlooked, but can anyone offer any suggestions? I have poured over the whole form 10 times and still can't find anything.

[June7]

Would have to review db structure and form design. If you want to provide, follow instructions at bottom of my post.

[beaverx37]

Camp.zip Okay not sure if I should start a new thread or what, my original question was fixed but I'm not sure how. And now its happening again. Except with the Emergency Contact (EC) Subform. Its just the CamperID isn't in the Camper table problem.

Camp.zip

[June7]

I don't understand issue. Where are these 'subforms' used? There is a CamperID field in Campers and Guardians table.

Things to consider in design:

1. do not use spaces and special characters/punctuation (underscore is exception) in naming convention - better would be GuardianIDNum or Guardian_ID_Num, DrName or Dr_Name or DoctorName

2. do not use reserved words as field names - Level is a reserved word

3. do not store age, store date of birth and calculate age

4. do not build lookups in table http://access.mvps.org/access/lookupfields.htm

5. do not store full name into one field, just the name parts in separate fields and calculate the concatenated full name when needed - setting Default Value property will not work to save full name into table - it just shows #Name? error

[beaverx37]

Some of that stuff I do simply because the template I downloaded did. But what is a reserved word? And would I go about fixing number 5?

[June7]

Reserved words are words that have special meaning in Access, such as Date because Date is an intrinsic function.

Eliminate the DefaultProperty setting. Eliminate the field from table. Change queries, forms, reports as needed.

[beaverx37]

I don't understand issue. Where are these 'subforms' used? There is a CamperID field in Campers and Guardians table.

This subform is going to be used in the Camper Details Form. I haven't put it in there yet because it isn't working. There is also a CamperID field in Campers and EC table. I don't understand why it is even looking for CamperID in the Camper table. The EC is basically an exact copy of the Guardian. They have the same tables, queries, and forms. Everything is working fine with the Guardian, so why is it not working with the EC? I have even tried remaking the EC completely from scratch. I remade every table, query, and form and it still gave me the same error message.

[June7]

You are basing RecordSource of forms on queries that join tables with INNER JOIN, which requires related records in all referenced tables for record to display.

A form can do data entry/edit for only one table. Suggest you bind forms directly to tables and when that works, then see if there is reason to bind forms to a query or SQL statement.

[beaverx37]

Thank you so much!! I made the EC Subform from the Campers and EC table instead of the query and now it works perfectly. You are the best!!!