ConcatRelated() - Error 3059: Operation canceled by user

[Zak14]

I'm using Allen Browne's Concatenate Function to concatenate the many phone number records of one person into one field of a record.

i.e.

John Smith | 02038483956
John Smith | 07593999493
Jim Jones | 07540029938
James Williams | 02079348395
James Williams | 07948394903
James Williams | 08003489358

This function, "ConcatRelated()" is used on the query behind a form, like ConcatRelated(.......) AS Phone
It produces these results:

John Smith | 02038483956, 07593999493
Jim Jones | 07540029938
James Williams | 02079348395, 07948394903, 08003489358

...

I also have a KeyPress event on the form that traps the ESC key and sets focus to the searchbox of the form. However, when the ESC key is pressed, it produces the following error:

ConcatRelated()
Error 3059: Operation canceled by user.

Any ideas how to fix this?

 

[jdraw]

??? I believe the ConcatRelated function is intended for reporting/presenting stored data. I would not store the result of the function into a single field.
Data storage is NOT the same as data presentation.

Perhaps you can tell us more about your application and requirements.

Do you have a table for PhoneTypes?

 

[Zak14]

??? I believe the ConcatRelated function is intended for reporting/presenting stored data. I would not store the result of the function into a single field.
Data storage is NOT the same as data presentation.

Perhaps you can tell us more about your application and requirements.

Do you have a table for PhoneTypes?

It's a continuous form, which displays limited details of individuals. You can click on an individual's record to be directed to another form which displays more details about just that individual.

I'm going to simplify. The form is based on a query with the fields, IndividualID and IndividualName from one table and the ConcatRelated function which pulls and concatenates the many phone numbers of one individual from an Individual_Phone table. The Individual_Phone table doesn't have a 'PhoneType' field, but just a Comment field.

In the form, I allow the user to search by ID, Name or Phone, and once they search, if the ESC key is pressed, it produces the error.

 

[vbaInet]

Don't click the Esc key? Or disable the Esc key whilst it's running and re-enable it afterwards?

Esc is an interrupt key for running code.

And I think jdraw thought that you were saving the values, but I don't think it's the case, so you can clarify this point raised.

 

[jdraw]

vbaInet is correct --- I took your statement

concatenate the many phone number records of one person into one field of a record.

literally and thought you were storing into 1 field in a record.

 

[Zak14]

Don't click the Esc key? Or disable the Esc key whilst it's running and re-enable it afterwards?

Esc is an interrupt key for running code.

And I think jdraw thought that you were saving the values, but I don't think it's the case, so you can clarify this point raised.

How do I disable the ESC key? What to you mean by 'interrupt key'. Does it interrupt all instances of code when pressed. In that case, shouldn't I have it permanently disabled?

 

[jdraw]

However, when the ESC key is pressed, it produces the following error:

Why would someone click ESC?

 

[Zak14]

Why would someone click ESC?

I use code that traps it and moves focus to the searchbox.

 

[vbaInet]

vbaInet is correct --- I took your statement
literally and thought you were storing into 1 field in a record.

It does sound like it doesn't it! But I think I remember when Zak14 asked about concatenating data in one of his threads and he was advised to calculate it using the function. It was either you, myself or JHB who gave him the link to Allen Browne's ConcatRelated function.

@Zak14, yes it can interrupt code that's running and/or sometimes interrupt q query that's churning. You can trap for the Esc key but in the meantime try setting KeyPreview to False just before calling the function and setting it back to True afterwards.

 

[Zak14]

It does sound like it doesn't it! But I think I remember when Zak14 asked about concatenating data in one of his threads and he was advised to calculate it using the function. It was either you, myself or JHB who gave him the link to Allen Browne's ConcatRelated function.

@Zak14, yes it can interrupt code that's running and/or sometimes interrupt q query that's churning. You can trap for the Esc key but in the meantime try setting KeyPreview to False just before calling the function and setting it back to True afterwards.

I don't think it was me that asked about concatenating haha.
Anyway, I set the KeyPreview to False before the function and it produces the same error, except this time, the whole form (apart from one record) goes blank when the error message is displayed.

 

[vbaInet]

Not quite what I thought then

Look into disabling the key in one of the appropriate events.

 

[Zak14]

Not quite what I thought then

Look into disabling the key in one of the appropriate events.

Sorry, I don't know what you mean.

 

[vbaInet]

Do some research Zak, i.e. look through the events, Google search and come up with something. Then we can work from that.

 

[Zak14]

What I understood was that I should set vbKeyEscape to 0 in the Form_KeyDown event, or something of the like. But how would that help? Why should I disable the ESC key when I want something to happen when it is pressed (i.e. the focus moves to the searchbox)?

 

[vbaInet]

The same way you disabled the KeyPreview before the function is called, is the same way you would disable the Esc key before the function is called. Re-enable it on completion.

 

[Zak14]

I tried this

If KeyAscii = 27 then
   KeyAscii = 0
   Me.txtSearch.SetFocus
   KeyAscii = 27
End If

Same problem :|

 

[vbaInet]

The Esc key needs to stay disabled whilst the code is running. Give that some thought.

 

[vbaInet]

Ok, I've just tested it myself and I can see what's happening.

Try adding these lines:

Err_Handler:
[COLOR="Blue"]    If Err.Number = 3059 Then
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        Err.Clear
        Resume Next
    End If[/COLOR]
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler

 

[Zak14]

Ok, I've just tested it myself and I can see what's happening.

Try adding these lines:

Err_Handler:
[COLOR="Blue"]    If Err.Number = 3059 Then
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        Err.Clear
        Resume Next
    End If[/COLOR]
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler

Sorry to be a noob. Where do I add these? I don't currently have an error handler for this. It seems like you might've posted something before this post that didn't come through?

 

[vbaInet]

No I didn't send any other messages.

It's the error handler in the ConcatRelated function I'm referring to, hence the "add these lines in blue" statement.