How to utilize VBA...

[MSherfey]

I recently found this sample code on how to concatenate data fields:

Public Function concat_alrgy(Patient_ID)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim hold_alrgy As String
Set db = CurrentDb
hold_alrgy = ""

[COLOR=#008000]'  clear out old list
[/COLOR]
DoCmd.RunSQL ("delete * from t_patient_alrgy")

[COLOR=#008000]'  start creating new list

'  select list of records for this patient
[/COLOR]
Set rst = db.OpenRecordset("SELECT Allergy " & _
" FROM L_alrgy INNER JOIN M_Patient_alrgy " & _
" ON L_alrgy.Allergy_ID = M_Patient_alrgy.Allergy_ID " & _
" WHERE Patient_ID=" & Patient_ID)

[COLOR=#008000]'  skip process if there are no items in the list[/COLOR]

If rst.BOF Or rst.EOF = True Then GoTo jump_out
  rst.MoveFirst

[COLOR=#008000]  '  start Concatenate Multiple Records to Text Field[/COLOR]

Do While Not rst.EOF
  If hold_alrgy = "" Then
    hold_alrgy = rst!Allergy
  Else
    hold_alrgy = hold_alrgy & "; " & rst!Allergy
  End If
  rst.MoveNext
Loop
  [COLOR=#008000]'  end Concatenate Multiple Records to Text Field[/COLOR]
jump_out:
rst.Close
Set rst = Nothing

[COLOR=#008000]'  load the concatenated  list[/COLOR]

DoCmd.RunSQL ("INSERT INTO T_Patient_alrgy ( Patient_ID, Allergy ) " & _
" SELECT " & Patient_ID & ",'" & hold_alrgy & "'")

End Function

Here: http://www.blueclaw-db.com/concatenate_multiple_records_one_field.htm

This is just what I need for my own tables. However, I don't know how to 'call' it. I can make the needed changes to use my table/field names, but how do I envoke/call/start it?

I'm sorry for such a basic question but any tips would be great.

 

[jzwp22]

Normally, you would just call the function using its name: concat_alrgy()

In a query it would look like the following; you will have to pass your patient_ID to the function by putting the Patient_ID field reference within the parentheses:

SELECT patient_ID, concat_alrgy(Patient_ID)
FROM yourtable


But, this function is different in that it pushes the results out to a temporary table. I would recommend not doing that.

I have modified your code to just give you the list when you call the function. You can call it as I have described above.

I have not tested this, so you may encounter an error or two. Please let me know if you do. I am assuming that your patient_ID is a long number value; if not, the code below will need to be modified further.

Public Function concat_alrgy(Patient_ID as long) as string
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim hold_alrgy As String
Set db = CurrentDb
hold_alrgy = ""

'  select list of records for this patient

Set rst = db.OpenRecordset("SELECT Allergy " & _
" FROM L_alrgy INNER JOIN M_Patient_alrgy " & _
" ON L_alrgy.Allergy_ID = M_Patient_alrgy.Allergy_ID " & _
" WHERE Patient_ID=" & Patient_ID)

'  skip process if there are no items in the list

If rst.BOF Or rst.EOF = True Then 

hold_alrgy="No allergies"

Else
  rst.MoveFirst

  '  start Concatenate Multiple Records to Text Field

Do While Not rst.EOF
  If hold_alrgy = "" Then
    hold_alrgy = rst!Allergy
  Else
    hold_alrgy = hold_alrgy & "; " & rst!Allergy
  End If
  rst.MoveNext
Loop
  '  end Concatenate Multiple Records to Text Field

End if
rst.Close
Set rst = Nothing

concat_alrgy=hold_alrgy

End Function

 

[MSherfey]

All the values are actually text. I'll make the modifications and see how far I get before it blows up

Thanks. I'll let you know how it works out as well the final code.