Concatenate record fields from a table

Hi,

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

landSightingsID      behaviour
1                                swim
1                               surface
1                               play

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.

Thanks



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)
rs.MoveFirst
Do Until rs.EOF
        strBehav = strBehav & rs("Behaviour.Behaviour") & ", "
    rs.MoveNext
Loop
rs.Close
 
concatBehav = strBehav
MsgBox concatBehav
 
End Function

Open in new window

LVL 1
davecocksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You need to include an ID in your call to the function.  Somthing like this, assuming ID is a field in your form:

call concatBehav(Me.ID)    
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
Also, you may need to modify this line:

>> MsgBox concatBehav

to

Msgbox strBehav

to avoid a recursive call to the function.
0
davecocksAuthor Commented:
Hey mbizup,

Thanks for your reply!

I've changed::     call concatBehav(Forms!behavLandSight.Form.landSightingID.Value)
and the     Msgbox strBehav

But now the    'rs As DAO.Recordset'    is highlighted as:

Complie Error: User defined type not defined
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbizupCommented:
Ok.

In the VBA Editor:

Tools -> References ...

Make sure you have the reference to Microsoft DAO checked.

Click "OK" to save and exit.
0
mbizupCommented:
You may need to scroll down a bit in the list of references.  The one you need to check will look something like this:

Microsoft DAO 3.6 Object Library
0
davecocksAuthor Commented:
Cool, thanks for your help.

That sorted the DAO issue, now I'm getting

'Item not found in this collection'
0
Lorenda ChristensenAccounting and VBA ConsultantCommented:
I'm guessing your loop tried to run one more cycle than there were records - maybe Do While Do Until rs.EOF = False ?
0
Lorenda ChristensenAccounting and VBA ConsultantCommented:
Sorry - mistype. Try Do While rs.EOF = false

Or alternately, you can determine the length of your returned results as so :

If rs.recordcount >0 then
rs.movelast
rs.movefirst
i = rs.recordcount
Else
Exit Sub
End If

Often this is best, because you avoid any issues if a particular ID has no detail behavior records
0
Lorenda ChristensenAccounting and VBA ConsultantCommented:
On my above post - use your i variable to create your loop, instead of the EOF command
For j = 0 to i
""concatenate code""
Next j
0
mbizupCommented:
<'Item not found in this collection'>

That means that a field name is unrecognized.

Try this using Behavior without the table prefix:

strBehav = strBehav & rs("Behaviour")
0
mbizupCommented:
Also, If that does not help -- be sure to let us know which line you receive the error on.
0
Lorenda ChristensenAccounting and VBA ConsultantCommented:
Do Until rs.EOF
        strBehav = strBehav & rs("Behaviour.Behaviour") & ", "
    rs.MoveNext
Loop


I've always designated my fields as rs.fields("Behavior.Behaviour") - maybe that's the problem?
0
mbizupCommented:
Btw, the do loop should be okay as originally posted.

And the syntax for the field is fine.  Either of these will work as well:

rs("FieldName")
rs!FieldName
0
davecocksAuthor Commented:
Hi Thanks mbizup you nailed it with the rs("Behaviour"). I gave a few points to lorenda for participating I hope you don't mind.

Hi lorenda thanks for your suggestions, but the Do Until rs.EOF = False was un-necessary once the rs("Behaviour") was sorted.

Again thank you both for your help, it was greatly appreciated
0
mbizupCommented:
Glad to help out :)
0
Lorenda ChristensenAccounting and VBA ConsultantCommented:
Yep - thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.