Urgent help needed please (1 Viewer)

[Elijahnzl]

Hi Again, I'm still stuck after too many hours trying different things. Still needing help please.

I have my main form frmFamily which includes Family Surname, Phone details, address, and so on.

I have a subform frmIndividual Subform which I am wanting to use to list all the individual first names of family members along with their individual email addresses, mobile numbers, dates of birth etc.

I am trying to create a phone directory of the families so am wanting to take the adult names from the subform, join them together, and place them in one field on frmFamily.

I am trying to do this by using a checkbox in the subform alongside each individual to show adult 1, adult 2, or child.

I can't help but think that I'm doing this the wrong way.

Can someone help please?

Many thanks.

Gene

 

[BigHappyDaddy]

What you describe sounds easy enough, but you don't say what your problem is exactly. The first thing I would want to check is that your tables have been designed correctly. Then it would be (relatively) simple to build a query based on the first table to be the source for frmFamily and a query based on the second table to be the source for frmIndividual.

 

[Elijahnzl]

Hi, thanks for your response. I hope I have attached a pdf that will be helpful.

 

[BigHappyDaddy]

Basically OK, but I don't like the _Adult_1, _Adult_2 or _Child fields in tblIndividual. You have nothing in the table structure that would prevent an individual from having all three attributes set to true at the same time. I would combine those three fields into one field that would be a foriegn key to a third table (tblIndividualType for example). That table would have 2 entries of Parent and Child. It could also be expanded to include other family members that are living at the same address (grandparents, uncles, aunts, cousins etc).

Now your query can use the new IndividualType to limit the returned records to just parents.

This help at all?

 

[Elijahnzl]

Hi BigHappyDaddy (or anyone else who would like to help,

I had to settle for something second best to get a mailout done, and am now back to trying to get what for me would be the best best given my limited knowledge and expertise.

I have attached a file showing the query I have built with the resultant output. My problem exactly...

I need to be able to concantenate the ADult names within one family. My name is Gene, my Wife's name is Mariapia. I have a table tblFamily that has the information pertinent to the whole family. Then I have the table tblIndividual that has information pertinent to each individual i.e. date of birth, cell phone number etc.

I am one adult and Mariapia another. I need a field that joins our two adult names together into one field with an "&" in between. This field needs to be accessed to print a telephone directory.

So, have I done the query right? And how then do I get the two adult names (or just the one if that is all there is) into a different field?

ANy help definitely appreciated. Thanks you. :banghead:

 

[Elijahnzl]

Here's the solution I found...

Private Sub cmdUpdate_Click()
Dim Name1 As String
Dim Name2 As String
Name1 = DLookup("Individual_Name", "tblIndividual", "Individual_Type = 'Adult1' AND Family_ID = " & [Family_ID])
Name2 = Nz(DLookup("Individual_Name", "tblIndividual", "Individual_Type = 'Adult2' AND Family_ID = " & [Family_ID]))
If Name2 = "" Then
Family_Names.SetFocus
Family_Names.Text = Name1
Else: Family_Names = Name1 & " & " & Name2
End If
End Sub