Subform Controls are Invisible

[TastyWheat]

I have a subform (in form view) in a form that is used for data entry. When I open the form the subform is blank except for the navigation controls. The navigation controls are disabled though. If I switch to datasheet view I will see the column headers but there won't be any place to type data (no white boxes). This doesn't happen if my master and child fields aren't set up, but obviously that will show the wrong data and create other kinds of entry problems. Right now the tables are empty so that could be part of the problem, but I shouldn't have to manually enter a record so my form will look right.

 

[ansentry]

This doesn't happen if my master and child fields aren't set up, but obviously that will show the wrong data

Why would it show the wrong data?

I thought that using master / child was the correct method.

 

[TastyWheat]

Well, if the master and child link fields are blank it will show all records, not just the ones where the foreign keys match the primary keys. Maybe I should start from scratch. The old form worked but it didn't use a subform because the person before me knew nothing about databases and normalization.

This is what I'm aiming for: The main form is used to make reservations and the subform is used to hold information about the children attending (extra info is kept for each child). I think the subform is necessary because the party could have 1 to 6 children and I don't want a table with a bunch of empty entries. It sounds pretty standard I just can't get the subform to behave correctly.

 

[Banana]

How do you have your tables set up? That will help us give you idea how to make queries that will work for both master and child form.

 

[TastyWheat]

This is the basic layout of the tables:

Reservation         Child
===========         =====
*ID                 ~ID
*ReservedBy         ~ReservedBy
FirstName           FirstName
LastName            Age
PhoneNumber         Interests
DinnerDate
Location
AdultsAttending
ChildrenAttending

The asterisks make up the primary key and the tildes are foreign keys. The 'ID' and 'ReservedBy' fields are what I'm linking up. It's not much to look at but I also included a picture of my subform.

 

[Banana]

There's no need for a Child table. You see, the data in Child table are same as the Reservation.

Read up on self join.

 

[TastyWheat]

Okay, technically speaking I don't need a 2nd table. That's how things were originally set up, but it was inefficient. A reservation is made for 1-2 adults and 1-6 children and special data must be kept for each individual child. So it's either have one table with a lot of blank fields (not everyone has 6 kids) or have two tables with much less blank fields.

 

[Banana]

No no no no.

Children should be their own record in the 1st table. Children are attendees just as adults are. You simply add a field something like ParentID that tell you which parent they are attending with.

Did you search for self-join?

 

[TastyWheat]

First off, thank you for your patience, because we're obviously not on the same page here. Now, if I read you correctly you're suggesting a table structure like 'structure1.jpg'.

And what I'm suggesting is a two table structure like 'structure2.jpg'.

Now let's go from there...

 

[Banana]

Structure1 has two tables; the top table isn't right but the bottom one is what I was thinking above.

Structure2 will work, but you are missing one thing; You need a ChildID as a primary key for the child table to allow many children to be related to single parent. This makes sense if all you need is their first name because that'd leave less blanks in the first table. However, if there are other details that you need to know about children that are already in Parent table, (e.g. more data than blanks) then the structure I proposed makes sense.

Did that help?

 

[TastyWheat]

Okay, well I wasn't planning on having a ChildID but otherwise I imagine the whole record would be the primary key. So given I use structure 2 and give the 2nd child table a proper unique ID can you see why my subform would be blank like this? I mean, this doesn't sound like an unusual scenario does it?

 

[Banana]

Can't explain why it's blank, though I can tell you that if ParentID was its only primary key, then you defined a 1-1 relationship, meaning a Parent can only have one Child, which isn't what you want.

 

[TastyWheat]

Ugh, very simple error. For the main Form I had "Allow Edits" set to No. I actually could've swore I saw that already and fixed it. I was able to enter data in every other control so I didn't really think that could be the problem.

Thanks for you patience and dilligence Banana.