thanks john for replies. when i used the code it came with error. this is the code i used to combine the data from multi row to one row.
Code:
Public Function ConcatField_STRING(Code As String) As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim sReturn As String
Set db = Application.CurrentDb
strSQL = "SELECT [Nama Pegawai] FROM tblPegawaiSelected WHERE [Kod Operasi] = '" & Code & "'"
Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
sReturn = sReturn & rst(0) & ", "
rst.MoveNext
Loop
End If
sReturn = Mid$(sReturn, 1, Len(sReturn) - 2)
ConcatField_STRING = sReturn
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Function