Solved

Concatenate record fields from a table

Posted on 2009-07-02
16
322 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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