Record Source and Multiple Select/List box

[jamez05]

Hi,

I'm lost! I have a subform within a form. The main form allows the user to select a site id. There are multiple records associated with each site ID. Each of these child records has a field named "MARINEorFRESHWATER" which is a list delimeted by a colon. The user needs to be able to update these child records on the subform.

First, I set the recordsource of the subform based on the site id that is selected:

    Form![UpdateSpeciesSub].Visible = True
    
    If Me.listSitesEditSpecies.ListCount > 0 Then
        For i = 0 To Me.listSitesEditSpecies.ListCount Step 1
           If Me.listSitesEditSpecies.ListIndex = i - 1 Then
            SiteID = Me.listSitesEditSpecies.Column(1, i)
           End If
        Next i
               
    End If
   
    If SiteID <> "" Then
        
        strSQL = "SELECT DISTINCT d.[Organism Common Name], s.ID2, s.ID1, s.ID, s.SPECIES_NUMBER, s.LATIN_NAME, s.KINGDOM, s.PHYLUM_DIVISION, "
        strSQL = strSQL & "s.Class , s.TAX_ORDER, s.FAMILY, s.GENUS, s.SPECIES, s.MARINEorFRESHWATER, s.[User Entered] "
        strSQL = strSQL & "FROM DATA d INNER JOIN tblSpeciesBSAF s ON d.[Organism Latin Name] = s.LATIN_NAME "
        strSQL = strSQL & "WHERE D.[Site ID] = '" & SiteID & "'"
        Set rs = CurrentDb.OpenRecordset(strSQL)
        
        Form![UpdateSpeciesSub].Form.RecordSource = strSQL

     End If

Next, the subform uses the recordsource to populate the fields for each record. However, I cannot figure out how to populate the combo box. Not sure where or how to do this.

 

[jamez05]

Additionally, right now the combo box has a row source type set to "Field List" and its row source is '"";"marine";"freshwater";"brackish"'. I'm not sure if this is the correct way to go about this. Basically, if
the field MarineOrFreshwater has marine";freshwater" I want those two to be selected in the combo box.

 

[jamez05]

Think I got it, need to use the "OnCurrent" event for the subform.