Table: landSightings : landSightingsID, behaviourID
Table: Behaviour : behaviourID, behaviour
Behaviour and landSightings joined together 1-Many by behaviourID
I want the code below to concatenate all the 'behaviour' record fields together for a particular 'landSightingsID'. So each of the records for landSightings is joined together
becomes: swim, surface, play,
I've adapted this code from a previous question but I can't get it to work. I'm trying to run it out of a button click on a form but if I 'call concatBehav' i get the ERROR:
Compile Error: Argument not optional.
Function concatBehav(sightID As Integer) As String
Dim rs As DAO.Recordset, strBehav As String
Dim sql As String
sightID = Forms!behavLandSight.Form.landSightingID.Value
sql = "SELECT landBehaviour.landSightingID, landBehaviour.behaviourID, Behaviour.Behaviour " & _
"FROM Behaviour INNER JOIN landBehaviour ON Behaviour.BehaviourID = landBehaviour.behaviourID " & _
"Where landBehaviour.landSightingID = " & sightID & ""
Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
strBehav = strBehav & rs("Behaviour.Behaviour") & ", "
concatBehav = strBehav