Solved

Concatenate record fields from a table

Posted on 2009-07-02
16
319 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

0
Comment
Question by:davecocks
  • 8
  • 5
  • 3
16 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 480 total points
ID: 24766171
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24766194
Also, you may need to modify this line:

>> MsgBox concatBehav

to

Msgbox strBehav

to avoid a recursive call to the function.
0
 
LVL 1

Author Comment

by:davecocks
ID: 24766339
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24766436
Ok.

In the VBA Editor:

Tools -> References ...

Make sure you have the reference to Microsoft DAO checked.

Click "OK" to save and exit.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 480 total points
ID: 24766465
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
 
LVL 1

Author Comment

by:davecocks
ID: 24766518
Cool, thanks for your help.

That sorted the DAO issue, now I'm getting

'Item not found in this collection'
0
 
LVL 6

Expert Comment

by:lorenda
ID: 24766570
I'm guessing your loop tried to run one more cycle than there were records - maybe Do While Do Until rs.EOF = False ?
0
 
LVL 6

Assisted Solution

by:lorenda
lorenda earned 20 total points
ID: 24766597
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 6

Expert Comment

by:lorenda
ID: 24766611
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 480 total points
ID: 24766653
<'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
 
LVL 61

Expert Comment

by:mbizup
ID: 24766680
Also, If that does not help -- be sure to let us know which line you receive the error on.
0
 
LVL 6

Expert Comment

by:lorenda
ID: 24766810
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24766852
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
 
LVL 1

Author Closing Comment

by:davecocks
ID: 31599313
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24766902
Glad to help out :)
0
 
LVL 6

Expert Comment

by:lorenda
ID: 24766910
Yep - thanks for the points.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now