Using Allen Browne's ConcatRelated() function but getting error

[ScubaBart]

Working on my database and while working on another issue on a separate thread, this is a standalone issue I have encountered so putting it by itself.

I found Allen Brown's wonderful ConcatRelated vba function and was able to enter it into the code window and compile it with no problems.

I am now attempting to use it for a control source in a form as follows:

=ConcatRelated("[Hive_ID]","[T_Hive_Inspection_Detail]","[Link_to_Log_Inspection_ID] = " & [Log_Inspection_ID],"[Hive_ID]",Chr(13)+Chr(10))

Basically, I am pulling list of the Hive_ID's from table T_Hive_Inspection_Detail where the Link_to_Log_Inspection_ID = Log_Inspection_ID then sorting by Hive_ID and finally adding a carriage return and line feed after each item in the list. The data source is a query that has both tables that have a one-to-many relationship. Hive_ID is in the 'many' table.



So, while the function appears to produce the expected results, I get an error every time I open the form as follows:



I have tried tweaking the syntax here and there but that either breaks the function or makes no change with the error still popping up.

Any thoughts on what I am doing wrong are much appreciated.

Update: I used the exact same expression in a query and it did not return the error so it appears to be specific to using it in a form field. Not sure what that implies.

[moke123]

what's the datatype of [Link_to_Log_Inspection_ID] ? if text you need to delimit [Log_Inspection_ID]

[ScubaBart]

It's numerical, Long Integer specifically.

[Welshgasman]

Walk your code into that function.

[June7]

Provide sample tables. Either build in post with Advanced Editor or attach a file (Excel or Access).

Could [Log_Inspection_ID] be Null?

You say data source is a query but you are pulling from T_Hive_Inspection_Detail, which should work just fine.

Is that data source the form's RecordSource? Why have both tables in form's RecordSource?

[ScubaBart]

Walk your code into that function.

I don't know what that means.


My data source actually is the table that is called out in the code shown above.

If [Log_Inspection_ID] were null, it shouldn't pull it into the result, correct ? There is currently row for ]Log_Inspection_ID] and three corresponding rows for [Link_to_Log_Inspection_ID].

The form in question is set for continuous records. Will try single record next and see what happens.

Not much time this evening but will work to post a file(s) tomorrow.

Thx !!

[June7]

It means debug. Set a break point and step through code. Review https://www.techonthenet.com/access/...10/debug01.php

[moke123]

I found a work around but I didn't figure out why your code was failing.
It seemed to be a problem passing the separator and/or a null somewhere but admittedly I didn't look too hard.

I just added an optional argument - UseNewLine as boolean
This way the original code's unchanged.

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ", Optional UseNewLine As Boolean = False) As Variant

And then added a line

If UseNewLine = True Then strSeparator = vbNewLine

Here's how I called it in the control source of a textbox in a contiguous form. (Obviously using my own tables)

=ConcatRelated("CaseID","tblAssigned","StaffID = " & Nz([Forms]![Form4]![StaffID],0),"CaseID","",True)  ' uses the new line

=ConcatRelated("CaseID","tblAssigned","StaffID = " & Nz([Forms]![Form4]![StaffID],0),"CaseID")             'use the default comma

Edit: Looking closer I think you have nulls in your criteria that needs to be handled with Nz()

[June7]

I tested and had no problem passing Chr(13)+Chr(10) as separator. It worked with + sign but advise to use & for concatenation, which also worked.

[davegri]

I think maybe the clue is that it worked as a standalone query, but not with the form. That means the query wasn't looking at the form at all. Check that the query criteria is looking at a form control named Me.Log_Inspection_ID], or whatever control holds that ID.

[Welshgasman]

So, while the function appears to produce the expected results, I get an error every time I open the form as follows:

I was thinking that the locationID was null?
See my signature for how to start debugging.

My thoughts were to see exactly what was constructed for the sql statement.

    strSQL = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSQL = strSQL & " ORDER BY " & strOrderBy
    End If

Or add Debug.Print strSQL before trying to open the recordset.

[Welshgasman]

Here is a simpler function.
You could try this, though you would need to pass the ORDER in as the sql string.

https://www.accessmvp.com/thedbguy/c...itle=simplecsv

[ScubaBart]

Ok, getting back into it this afternoon and now the ConcatRelated function is not working at all. Not in the form or in the query. Not sure what I screwed up. - UPDATE - I fixed this by deleting and completely recreting the function but the original error now comes back so the error shown below can be ignored. - END UPDATE.

So I have uploaded the entire file.

The form that contains the field is [F_Log_Inspection_Main]

The query that contains the function is [Q_Inspection_Main] and it actually returns an error as shown.



I tried recompiling the code with no change.

I am by no means very knowledgeable on the coding side as I previously did almost everything with extensive use of macro's back in my days.

This is a copy specifically for troubleshooting so by all means, feel free to do anything that you guys think would make it work for me.

My goal is to open up the [F_Log_Inspection_Main] form that would list in a continuous form format all the inspections of each Apiary and showing the list of Hives in that Apiary that were inspected. (This is where the ConcatRelated Function comes in).

Clicking the button on any of the inspection rows expands that particular inspection and contains a sub-form showing more details of each Hive inspection. Further, clicking on the button of any given Hive inspection opens up a modal pop-up that allows detailed editing / entry of the Hive inspection details.

There will also be a button at each appropriate level to add a new Apiary and Hive inspection respectively.

The navigation controls are not all in place yet so some of that may not work.

I'm not hesitant to take suggestions but it needs to be somewhat simple to implement for me to grasp it. To many other irons in the fire to spend a ton of time learning something that I likely will never use again.

Thanks in advance.....

[Welshgasman]

As I suspected
SELECT [Hive_ID] FROM [T_Hive_Inspection_Detail] WHERE [Link_to_Log_Inspection_ID] = ORDER BY [Hive_ID]
The second record is a new record so Inspection ID is Null, so the SQL is rubbish

Furthermore you cannot name a module the same name as any function/Sub. Change the module to modConcatRelated.

Edit: A quick fix could be

=ConcatRelated("[Hive_ID]","[T_Hive_Inspection_Detail]","[Link_to_Log_Inspection_ID] = " & NZ([Log_Inspection_ID],0),"[Hive_ID]",Chr(13)+Chr(10))

[ScubaBart]

Thanks Welshgasman, That fixed it.

After playing with this, it seems that the property "Allow Additions" causes the new record to show and throws the error. So, turning this variable to No also fixed the error but I added the NZ function in as well just to cover myself. I will end up using a macro to create a new record at each step.

One problem solved, several more to go probably.

Case Closed and it was simple enough for me to understand.