Access 2010 Get Multiple Related Records from a Table

[sahm]

Hi,

I have the following code which works fine for getting ONE record based on "Job_Number" value and storing it in another control. However, I have more than one record which have the same "Job_Number" and I would like to get all of them and store them in that control. I know I need something like a Loop until I get all of them but not sure how to do it.

================================================
Dim strBill As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb


Set rst = dbs.OpenRecordset("SELECT Containers.* FROM Containers WHERE Containers.Job_Number = " & Me!Job_Number)

strBill = rst.Fields(3).Value


Marks_and_Numbers.Value = strBill




rst.Close
Set rst = Nothing
Set dbs = Nothing


================================================

Any ideas how to achieve that.




Many thanks.
Sahm

[June7]

You want to store multiple values into one field in table? Why? That is poor design.

While Not rst.EOF
strBill = strBill & ", " & rst.Fields(3).Value
rst.MoveNext
Wend

[sahm]

Many thanks June7.

The code works fine.

I need this to put the all values in a Memo field as text info only.

Thanks again.