result SQL in msgbox

[Annnnn]

I’m looking for a way to show my SQL results in a msgbox.
I am able to show 1 value, the first match, in the msgbox but I would like to have the whole list. Which is approx. 5 rows. This is the code I am using now

 
Private Sub btCPCT_Click()
Dim a As String
Dim b As DAO.Recordset
Dim patientnm As String
 
patientnm = patientnm & ", '" & Me.txPatientID & "'"
patientnm = Right(patientnm, Len(patientnm) - 1)
 
a = "SELECT Patient_name, Sample_name " & _
     "FROM tblSamples " & _
     "WHERE Patient_name IN (" & patientnm & ")" & _
     "AND Source = 'Blood'" & _
     "ORDER BY Sample_name DESC"
Set b = CurrentDb.OpenRecordset(a)
 
msgBox b.Fields("Sample_name")
b.Close
End Sub

So me.txtpatient is the value I am searching in the main table tblsamples the result of this search ar multiple samples names (Sample_name). With this code it only shows 1 sample and I want it to show all the sample names which match the entered patient ID

Hope it's possible XD

[ranman256]

you wouldnt use a msgbox for multiple records,
thats what a datasheet is for.

you dont use a screwdriver to hammer in nails.

[Annnnn]

the thing is that I am using a form to add samples to the table. It is possible that the patient, to which the sample belongs, is already registered in the table. When this is the case a roman 2 has to be added to the sample ID. This
is so that it remains a unique value, it can also be that it is the third, fourth sample etc. This is why I would like the user to visually see what the other sample ID are that are registered.

If you have a better method....

[isladogs]

If you really want to do this, you could loop through the table/query one record at a time then print each result to your message box on a separate line
Something like this (untested)

Private Sub btCPCT_Click()Dim a As String
Dim b As DAO.Recordset
Dim patientnm As String
Dim strText As String
 
patientnm = patientnm & ", '" & Me.txPatientID & "'"
patientnm = Right(patientnm, Len(patientnm) - 1)
strText=""
 
a = "SELECT Patient_name, Sample_name " & _
     "FROM tblSamples " & _
     "WHERE Patient_name IN (" & patientnm & ")" & _
     "AND Source = 'Blood'" & _
     "ORDER BY Sample_name DESC"
Set b = CurrentDb.OpenRecordset(a)

With b
    .MoveLast
    .MoveFirst
    Do Until .EOF
    strText=strText & vbCrLf & !Sample_Name
    .MoveNext
    Loop
    .Close
End With


Set b=Nothing
 
msgBox "The sample names for patient " & Me.txtPatientID & " are " & vbCrLf & strText


End Sub

[Annnnn]

Perfect that works !
Many thanks

[isladogs]

Excellent. All the better for not being tested!