Return multiple items in access using Dlookup

Question

DealID is set using a Combobox and it needs to Display up to 3 MID's in the field below it ``` Private Sub DealID_AfterUpdate() Dim strFilter As String strFilter = "DealID = " & Me!DealID Me!MID = DLookup("MID", "DealContent", strFilter) Exit_ProductID_AfterUpdate: Exit Sub End Sub ``` That's the code I'm using the obvious limitation is Dlookup only returns the first result it finds. That creates 2 problems but Ill focus on the first, It won't display more than one MID so how I can get the 1-3 MIDs to be displayed? The second issue I have is more in-depth but if anyone wants to help, a personal chat would be appreciated. Basically The form above is a child form and I need it to save a separate entry in the forms table for each Mount ID. If anyone would like to help but doesn't understand(as is often the case with my submissions), I think screen sharing on Skype is the best option.

Accepted Answer

As already said in a comment, you can't use `DLookup` to return more than one value.

You need to select the first three `MID`s into a `Recordset`, loop through them and append them to `Me!MID`:

```
Dim RS As DAO.Recordset
Dim SQL As String

'ordering is only important if you want the FIRST three MIDs.
'If you don't care, just omit the "order by MID" part.
SQL = "select top 3 MID from DealContent where DealID = xxx order by MID"

Set RS = CurrentDb.OpenRecordset(SQL)
Do While Not RS.EOF
    Me!MID = Me!MID & RS("mid") & " "
    RS.MoveNext
Loop
RS.Close
Set RS = Nothing
```

Note that this example uses DAO, which is the default (and recommended by MS) data access technology in newer Access versions. Older versions used ADO as default. DAO works there as well, you just need a reference to `Microsoft DAO x.x Object Library`.

Return multiple items in access using Dlookup

Solution

Problem