Master/Child Field is Null when detail subform has no records (1 Viewer)

[Dûplyy]

Hi all,

I have a Header form (tblEvent) and a revised Detail subform (tblEventGuest) on which guests can be clocked in and out of the event.
The tblEvent table has a one-to-many relationship with the tblEventGuest table.
The subform has a linked master/child field of the EventID.

The issue is that the linked master/child field is Null when no guests are clocked in yet.
This is probably because tblEventGuest doesn't contain records with the specific EventID yet.
This was not a problem in the previous subform (this was a continous form default view) because before a record was inserted, the EventID was added to the subform.

Now with the revised subform i am using a 'single form' as default view which contains two subforms in which the left subform contains the absent guests and the right subform containing the guests which are present on the event.
Between the subforms i have buttons to clock in selected absent guests which are then inserted to the present guests.
For this i am executing an "INSERT TO" which uses EventID as one of its values.

I get the EventID from Me![EventID] which gets the field value via the linked master/child field.
The problem here is when no EventGuest (detail) records exist yet which sets the linked field to Null in the subform.

I know i could solve this by calling me.Parent![EventID] from the subform which i would like to prevent in my code.

For me the expeced behaviour of a linked field should be that when the master field contains a value that the child field also should contain that value.

Is there a way to link the EventID when the detail subform has no records?

~ Jarich


Upper is master form, Lower is Child subform:

Event header which contains guest records in child subform

Event header which has no guest records in child subform

 

[theDBguy]

Hi. Welcome to AWF!

For me the expeced behaviour of a linked field should be that when the master field contains a value that the child field also should contain that value.

I am not sure if this is the default behavior. I have not tried to grab the "value" out of the linked fields. As far as I know, it will only give you the name of the fields in that property.

I know i could solve this by calling me.Parent![EventID] from the subform which i would like to prevent in my code.

You may have to go this route instead, if you can't find a better solution to the above behavior.

 

[The_Doc_Man]

The issue is that the linked master/child field is Null when no guests are clocked in yet.

From your description, this is backwards from the way that parent/child (or independent/dependent) tables work. If you want to know what guests attend your event, there must be an event already defined for guests to be registered to it. Otherwise, the guest records would be "data orphans."

For you to pick up the event in this way, ...

I get the EventID from Me![EventID] which gets the field value via the linked master/child field.
The problem here is when no EventGuest (detail) records exist yet which sets the linked field to Null in the subform.

you are turning the independent and dependent tables upside down. Or at least that is how it looks to me. IF you have an event defined and create a guest entry, a proper parent/child link will automatically put the event in the guest entry. As you say, ...

For me the expeced behaviour of a linked field should be that when the master field contains a value that the child field also should contain that value.

I fully agree with you regarding expected behavior. Where we disagree is which one of your two fields is the master field. The event record doesn't depend on the guest record. The guest record depends on the event record. You would still need an event record before the first person arrives. OK, I know if you throw an event and nobody comes, you didn't have much of an event - but that doesn't change the logic of which field is independent. If you build the relationship correctly, your guests should pick up the event ID automatically.

The only wrinkle would be if you were registering guests to two (or more) different events at once, in which case you would need something to identify the event, like a combo box.

 

[arnelgp]

Is there a way to link the EventID when the detail subform has no records?

not advisable.
why would you "force" a sibbling when there is none yet?

 

[Dûplyy]

Ok, I think i understand now why the linked field is Null.

To show what is happening i created a Header and Detail table:

I created a Form "frmParent" which has the tblHeader as recordsource and a Form "frmChild" which has the tblDetail as recordsource.
I added frmChild as subform to frmParent:

Fig1. New blank header record (parent)--------------------------- fig2. Inserting data into header field (dirty header)

fig3. Inserting data into Detail field (dirty Detail)-----------------fig4. Saving (ctrl+s) detail field (becoming undirty)

In fig1. the header and detail fields are blank as expected.
In fig2. i inserted text into the header field. when the the header became dirty an HeaderID was created. The master/child HeaderID link in the frmChild subform is still Null.
In fig3. i inserted text into the detail field. Upon insertion the detail record became dirty and the HeaderID was added to the record.
in fig4. i saved the detail record.

fig5a. 1st record selected------------fig5b. 2nd record selected-----------fig5c. New blank record selected

So what is happening is when a record is selected the fields of this record are readable in vba like me![HeaderID] and me![DetailField1]. But when the record is a new blank record the HeaderID is not initialized yet (obviously now after hindsight!!).

So to solve it for my case where i have a parent form, a child subform, and a grandchild subform (not completly true but for simplicy sake):

• Setting the Parent form recordsource to tblHeader
• Setting the Child form recordsource to tblHeader
• Setting the Grandchild form recordsource to tblDetail

Now you might be probably wondering why i dont use the grandchild form as the child form. This is because the child form actualy contains 2 grandchild subforms and some comandbuttons: a absent guests form, a present guest form and buttons to clock in and out the guest. And i need to change the recordsource of the child subform because the parent form has multiple child tables so to speak (Event Guests, Event Sales, Event Catering, etc.)

 

[Dûplyy]

From your description, this is backwards from the way that parent/child (or independent/dependent) tables work. If you want to know what guests attend your event, there must be an event already defined for guests to be registered to it. Otherwise, the guest records would be "data orphans."

I am sorry that i wasnt specific enough and not consistent with my wording in the post.
I have a Event -> one to many -> Guest relationship enforcing referential integrity.
I also hide the guest subform until the the event form is filled out such that EventID is intitialized.

The only wrinkle would be if you were registering guests to two (or more) different events at once, in which case you would need something to identify the event, like a combo box.

I have my database set up with recurring guests (we have a construction unit at our home that we use to sit with friends so its like 10 to 20 people in the weekends). When they attend the 'Event' they are clocked in and have a label on what they are drinking and when they leave (or having an other kind of drink) they are clocked out.

 

[Pat Hartman]

The issue is that the linked master/child field is Null when no guests are clocked in yet.

Of course and changing to a single view subform doesn't change the logic at all. The FK on the subform would NEVER be filled in until you actually add a row. I don't normally display the FK on the subform. That would just waste space since it is identical on every row AND it is identical to the PK on the main form. If it works like most defaults, it will not be filled in until the user types the first character into any field.

Since all rows of the subform would be for the same event, you need to change your code to pick up the event from the parent form.