Hi, I have a form that contains a listbox of Site IDs. I want to update Latin Names that have the selected Site ID from the list box.
The OnClick event on the listbox creates a list of Latin Names. I would like to use this list to create a query that becomes the recordsource of a subform. Here's what I have:
The problem is that it error "438 object doesn't support this property or method" on the last line before the final end if.
The OnClick event on the listbox creates a list of Latin Names. I would like to use this list to create a query that becomes the recordsource of a subform. Here's what I have:
Code:
If SiteID <> "" Then
'Want to pass SiteID and a list of Latin Names associated with the Site to UpdateSpecesSub form
strSQL = "SELECT DISTINCT [Organism Latin Name] FROM DATA WHERE [Site ID] = '" & SiteID & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)
LatNameList = ""
rs.MoveFirst
Do Until rs.EOF
If LatNameList <> "" Then
LatNameList = LatNameList & ","
End If
LatNameList = LatNameList & "'" & rs.[Organism Latin Name] & "'"
rs.MoveNext
Loop
rs.Close
strSQL = "SELECT 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.[Organism Latin Name] IN (" & LatNameList & ")"
Form![UpdateSpeciesSub].RecordSource = strSQL
End IfThe problem is that it error "438 object doesn't support this property or method" on the last line before the final end if.
