Concatenating Fields with VBA

[Elijahnzl]

I have a main form - frmMain
I have a subform - frmSub

I am populating two fields on frmMain from fields on frmSub.

The code that is nearly working is this:

Private Sub cmdUpdate_Click()
Dim Name1 As String
Dim Name2 As String
Dim Children 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]))
Children = Nz(DLookup("Individual_Name", "tblIndividual", "Individual_Type = 'Child' AND Family_ID = " & [Family_ID]))
If Name2 = "" Then
Family_Names.SetFocus
Family_Names.Text = Name1
Else: Family_Names = Name1 & " & " & Name2
End If

Family_Children.SetFocus
Family_Children.Text = Children
End Sub

My problem is that I can have more than one Name with the Individual_Type "Child". The above code only gets the name of the first child. I need to get all children's names and concatenate them in a field on frmMain.

Any ideas please?

 

[BigHappyDaddy]

Open a recordset using your FamilyID in the WHERE statement. Then loop through the records and employ a SELECT CASE on the Individual_Type. Then you handle both adult cases and any number of children.

 

[Elijahnzl]

Hi BigDaddyHappy

I must admit that I am very new to this VBA stuff. I have been trying to search the web for examples of what you are suggesting that I do.

Are you please able to point me to somewhere I could find an actual example please?

Many thanks

Gene

 

[pr2-eugin]

Hello Elijahnzl, Creating a recordset is very simple and easy and what BigHappyDaddy is saying is something like the following.. I do not have the time to actually code what you want.. but this should get you started.. Try this..

Dim dbObj AS DAO.Database
Dim rst AS DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Individual_Name, Individual_Type FROM tblIndividual WHERE Family_ID = " & [Family_ID]

Set dbObj = CurrentDB
Set rst = dbObj.OpenRecordset(strSQL)

Do While Not rst.EOF
    Select Case rst!Individual_Type
        Case "Adult1"
          [B][COLOR=SeaGreen]  [/COLOR][/B][B][COLOR=SeaGreen]  'do something..[/COLOR][/B]
        Case "Adult2"
            [B][COLOR=SeaGreen]  'do something..[/COLOR][/B]
        Case "Child"
            [B][COLOR=SeaGreen]  'do something..[/COLOR][/B]
    End Select
    rst.MoveNext
Loop
rst.Close

If still not sure.. Post back some of us wil look into it..

 

[BigHappyDaddy]

pr2-eugin pretty much nailed it.

I must admit that I am very new to this VBA stuff.

We were all new at some point. I have only been actually coding in VBA for about 18 months now. Try to take one concept at a time. Learn it and use it in an example or actual project. Then move on to next concept.

It won't be long before you look back on code you wrote some time ago and you can see how you could improve it.

As for resources, sites like this one are great resouces because of people like pr2-eugin! Bob Larson's site has some book recommendations that you might find useful. His site also has a lot of coding examples that could be useful just from an educational standpoint. He is another awesome resource on this forum.

Best of luck!

 

[Elijahnzl]

Hi BigHappy Daddy and pr2-eugin

Thanks for your help - HEAPS!

I submit my code and ask that you might critique it for me please - tell me what I could be doing better... It does work, but I am concerned that an error might occur that I do not expect.

Private Sub cmdUpdate_Click()
Dim Name1 As String
Dim Name2 As String
Dim Children As String
Dim Age As Integer

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

strSQL = "SELECT Individual_Name, Individual_Type FROM tblIndividual WHERE Family_ID = " & [Family_ID]

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
Select Case rs!Individual_Type
Case "Adult1"
Name1 = rs!Individual_Name
Case "Adult2"
Name2 = rs!Individual_Name
Case "Child"
Children = rs!Individual_Name
rs.MoveNext
Children = Children & ", " & rs!Individual_Name

End Select
rs.MoveNext
Loop
rs.Close
If Name2 = "" Then
Family_Names.SetFocus
Family_Names.Text = Name1
Else: Family_Names = Name1 & " & " & Name2
End If


Family_Children.SetFocus
Family_Children.Text = Children

'Age = (Date - [Individual_DoB]) / 365.25
'Individual_Age.Text = Age


End Sub

Again, thank you very much.

 

[Elijahnzl]

I AM getting the following error:
Run-time error 3021 - No Current Record

Here si my code... you can see that I added a rs.movenext for Select Child. Otherwise I get a child's name appearing twice in populated field.

Any thoughts please?

Do While Not rs.EOF
Select Case rs!Individual_Type
Case "Adult1"
Name1 = rs!Individual_Name
Case "Adult2"
Name2 = rs!Individual_Name
Case "Child"
Children = rs!Individual_Name
rs.MoveNext
Children = Children & ", " & rs!Individual_Name

End Select
rs.MoveNext
Loop
rs.Close

 

[boblarson]

Never mind, I misread the original post.

Your data should be normalized, not having the fields in the same record.

So it should be


tblFamily
FamilyID - Autonumber (PK)

tblFamilyMembers
FamilyMemberID - Autonumber (PK)
FamilyID - Long Integer (FK)
LastName
FirstName
RelationshipID - Long Integer (FK)

tlkpFamilyRelationship
FamilyRelationshipID - Autonumber (PK)
FamilyRelationshipDesc - Text

 

[BigHappyDaddy]

Do While Not rs.EOF
   Select Case rs!Individual_Type
      Case "Adult1"
         Name1 = rs!Individual_Name
      Case "Adult2"
         Name2 = rs!Individual_Name
      Case "Child"
         Children = rs!Individual_Name
[COLOR=red]        rs.MoveNext <-- Try removing this line[/COLOR]
         Children = Children & ", " & rs!Individual_Name
 
   End Select
   rs.MoveNext
Loop

I was just looking this over when I got your message about the error. I haven't looked at the rest of your code.

 

[BigHappyDaddy]

Try this as you child CASE statement

[COLOR=black]      Case "Child"[/COLOR]
[COLOR=black]         If Children = "" then
            Children = rs!Individual_Name
         Else[/COLOR]
[COLOR=black]            Children = Children & ", " & rs!Individual_Name[/COLOR]
[COLOR=black]         End If
[/COLOR]

 

[Elijahnzl]

Thanks All for your help. BigHappyDaddy your last solved it - thanks very much.

I know I have more questions looming but I will see if I can get things working myself.

Thanks all once again.

 

[pr2-eugin]

As for resources, sites like this one are great resouces because of people like pr2-eugin!

As much as I am flattered BigHappyDaddy, I am still a learner, and have only used access for about a year now.. So there are a lot to learn..

I know I have more questions looming but I will see if I can get things working myself.

Thanks all once again.

You are most welcome Elijahnzl, we are happy to help.. If you do have any more problem, do post and we will try our best to help you out..