We help IT Professionals succeed at work.

Concatenate record fields from a table

davecocks
davecocks asked
on
Medium Priority
356 Views
Last Modified: 2013-11-28
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also, you may need to modify this line:

>> MsgBox concatBehav

to

Msgbox strBehav

to avoid a recursive call to the function.

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Ok.

In the VBA Editor:

Tools -> References ...

Make sure you have the reference to Microsoft DAO checked.

Click "OK" to save and exit.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Cool, thanks for your help.

That sorted the DAO issue, now I'm getting

'Item not found in this collection'
Lorenda ChristensenAccounting and VBA Consultant

Commented:
I'm guessing your loop tried to run one more cycle than there were records - maybe Do While Do Until rs.EOF = False ?
Lorenda ChristensenAccounting and VBA Consultant
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Lorenda ChristensenAccounting and VBA Consultant

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also, If that does not help -- be sure to let us know which line you receive the error on.
Lorenda ChristensenAccounting and VBA Consultant

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help out :)
Lorenda ChristensenAccounting and VBA Consultant

Commented:
Yep - thanks for the points.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.