Field values from a parent table in a child table 3

[SherryLynn]

Hello,

I have a table that records family information (last name, file number, etc.). The table which is linked to it is literally a child table where the family child information is stored. Each child's first name and date of birth. The table currently shows the ChildID, FamilyID (from the main family table), Child Name, DOB, and I want a column that shows the child's last name. I know this is somewhat of a duplication of data, but I need to have this info. show up in the table. How can I have this information automatically "fillin" from the parent table? Help!

Thanks,
Sherry

 

[scriverb]

Just a little more info is needed. Are you entering the data with a Main form(Family Info) and have a subform control with a continuous form where the children information is entered? This would be the typical design here. In the Properties of the subform control on the Main form there are the Child and Master Link Fields. This is where the PrimaryKey from the master and the Foreign Key fields are identified as you have indicated in your posting here.

If this is what you are using then just put the following code in the Default Value for the Last Name field in the of the Subform control form.

=FORMS![[i][red]mainformname[/red][/i]]![[i][red]LastNameControlName[/red][/i]]

Whenever a new record is created in the subform control form the LastName of the current record in the Main Form will be inserted.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[willir]

Sherry

I am going to approach this two ways.

First, having the lastname / surname on the Child or Family Member table is legit. With blended families, having a mix of surnames seems to happen way too frequently. Using the last name of the family, per Bob Scriver's post, as the default makes sense.

The other aspect is the power of the combo box. If not here, then you will encounter the issue elsewhere where you need to depict both first and last names. The combo box retrieves the selected record set per the SELECT statement but only displays the first visible column. However, all columns are accessible.

For example, for a combo box called cmbFirstName...
[tt]
SELECT tblChild.FirstName, tblChild.LastName
FROM tblChild
WHERE tblChild.FamilyID = Me.FamilyID
[/tt]
(Me.FamilyID reference the form)

The combo box, Me.cmbFirstName will display FirstName

...But you can display the LastName in an unbound text field. (I am going to call the unbound text field UnboundTxtField)

Me.UnboundTxtField = Me.cmbFirstName.Column(1)

Column refers to the columns retrieved in the SELECT statement. "n" starts at 0.

Richard

 

[SherryLynn]

Hello,

Thank you both for your replies. Bob, I used your suggestion since this option will work better for me in the end. I did what you suggested and that worked very well. My other issue though is how do I populate the existing records with their family names? Is there a way to bring those over?

Thanks again!
Sherry

 

[PHV]

Take a look at update query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

Like PHV has suggested, create a new field for FamilyName in your Child table. Then use an Update query to link the two tables together and update the Child table with the Family name from the Family Table.

Example:

Update [ChildTable] AS C INNER JOIN [FamilyTable] as F ON C.[FamilyID] = F.[FamilyID] SET C.[FamilyName] = F.[FamilyName];

Update with the appropriate names, create a new field and execute the above query.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Thanks very much. I will try it out and let you know how it works.

Sherry

 

[SherryLynn]

Hello,

Ok - thank you both. Between both of your suggestions I have managed to update my records with the update query. I put the code in the Default Value of the last name field and that is working very nicely, except...it doesn't populate the first record of the child for that form. When I go to a second record and each one succeeding that, it populates fine with the last name??? Any ideas?

Thanks - you guys are wonderful!
Sherry

 

[scriverb]

Are you saying that the first child record added in the subform doesn't have a default value? But, after the first one is created it works just fine for the second child entered. That surely doesn't make sense because the default reference should be referencing the Main form control not the subform control. Make sure that you have not entered a reference to the LastName field in the subform rather than the Main Form.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello,

I double checked that and yes the reference is in the Main form. And, yes, the first child record last name field remains blank, the second and consecutive records work beautifully. A little bit of a quandry huh?

Sherry

 

[SherryLynn]

Hello,

I keep trying to make this work and I think I'm getting confused. Ok, early on you stated to put the code in the Default Value of the Last Name Field (which in my case is called "Family") on the main form. So, I double click that control to get properties and in the line for Default Value I typed the code. Is this right? I tried doing it in the subform control called "Family" and the same thing happens. If I leave the code in both fields I see "#error" in my field until I type something. But still nothing in the first occurrence of the child record. Help?

Sherry

 

[scriverb]

I believe I said:

If this is what you are using then just put the following code in the Default Value for the Last Name field in the of the Subform control form.

Do not put it in the control on the main form. It is that control that we are trying to bring down to the subform forms control. You put the code in the Default Value property of the Name field on the subform form. It is referencing the control on the mainform so that the same name is brought down as the default for all of the family members.

Hope this clears this up.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello,

I have tried the code in the subform control and that is what gives me the Last Name in every record but the first one. I'm not sure why it's doing that.

Sherry

 

[scriverb]

Create a new Main Form record. Now create your first subform record. It should be the default value in the LastName field. It will only be there when you create a new record. If the record is already created before we got this code setup and working correctly then the old first record is already set and the Default Value setting has no effect. I think this is what is happening here.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hi Bob,

I did try that out and created a couple of new "Test" records. It still does the same thing, only the second and consecutive child records are updating with the last name. I'm at a loss.

Thanks,
Sherry

 

[scriverb]

package up that form and subform in a new database with an empty tables and send to me(see my profile). I would have to take a look to see why this is happening.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hi Bob,

I managed to get this working so that the last name is referencing properly. I needed to change the Select statement for the subform. But...when I go to the Child table, the last name is not being stored in that table (which was what I was looking for). How do I reference this so that it stores it in the child table as well as the family table. I realize this is duplication, but this is what I was looking for all along.

Thanks very much!
Sherry

 

[scriverb]

The control for LastName in the subform form has to have a Control Source linking to the LastName of the child table. This would be the field in the ChildTable from your Query.

Should work just fine. Maybe you changed the Select and made it an Not Updateable Recordset due to one to many where the query doesn't know which record to update in the child side of the link.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Thank you Bob! I have it working now thanks to you.

I really appreciate your help with this.

Sherry