Solved Click Event for part of a TextBox string.

[raziel3]

I am trying to create an OnDouble Click event for the field APPLIED CHQS. If I add a hyperlink to the field the entire string is hyperlinked. I want to be able to click on each on the values that is separated by a comma. For example, the first record, if I click on either 506 or 1199 it opens the form to that record number. Each of the values in APPLIED CHQS relates to its own record in the CHQS Table.

 

[CJ_London]

Use selstart to determine where the control has been clicked then use the instrrev function to determine which comma is before this position, then you can work out which section it is you need to use

 

[MajP]

Why do you have a field with multiple data values? Is this a multivalue field or a regular field? Is this for display purposes only or is this field actually storing mulitple pieces of data?
if your field actually looks like this
506, 1199, 345
I would pop open a small form with a listbox when the textbox has a comma in it. The pop up form would split the string using the split command to create single lines. Then let you click the selection. If no comma then it should be able to open to the record.
506
1199
345

I have not tried @CJ_London approach to see how well that would work.

 

[raziel3]

Why do you have a field with multiple data values?

It's only for display.

 

[Edgar_]

See if this helps, it uses the timer to get the value after double click.

Option Compare Database
Option Explicit

Private selectedText As String

Private Sub Form_Timer()
    selectedText = Me.txtAPPLIED_CHQS.SelText
    Me.TimerInterval = 0
    DoCmd.OpenForm "CHQS", , , "TheNumber = '" & selectedText & "'"
End Sub

Private Sub txtAPPLIED_CHQS_DblClick(Cancel As Integer)
    Me.TimerInterval = 100
End Sub

The module variable can be local to the timer too.

I don't like it, because it uses a timer. I'd use my almighty web browser control. Or maybe a subreport? or add steps and pop another form where I can choose. Maybe even an unbound combobox?

 

[raziel3]

This is exactly what I wanted. But now you have me worried

I don't like it, because it uses a timer.

Why? Can you explain more?

 

[Edgar_]

Well, mainly because I have to have the form with the timer closed to edit any VBA code. It disrupts your experience. The good thing is that as soon as it starts ticking, it stops, in this case just to capture the double click, which to me it sounds like a hack.

I first tried to get the .SelText property just with the double click, it didn't work, then with the mouse up event, it didn't work either. So the timer was my last resource before changing the entire workflow to display a browser control, or popping up an additional form where I can select. Ideally, a rich text field should do this, but it does not support <a> tags.

So, basically, I wanted to do it via the obvious methods, but I had to resort to a hack.

 

[MajP]

Here is generic code to get the value from a delimited list

Private Sub SplitField_DblClick(Cancel As Integer)
  MsgBox GetSelection(Nz(Me.SplitField, ""), SplitField.SelStart, ";")
End Sub

Public Function GetSelection(DelimitedValues As String, Location As Integer, Optional Delimiter As String = ",") As String
 Dim aVals() As String
 Dim i As Integer
 Dim commaCounter As Integer
 If Not IsNull(DelimitedValues) Then
   If Location = 0 Then Location = 1
   If Location = Len(DelimitedValues) Then Location = Location - 1
  ' MsgBox Location & " " & Len(DelimitedValues)
   aVals = Split(DelimitedValues, Delimiter)
   For i = 1 To Len(DelimitedValues) - 1
     If i = Location Then
       GetSelection = aVals(commaCounter)
       Exit Function
      End If
     If Mid(DelimitedValues, i, 1) = Delimiter Then commaCounter = commaCounter + 1
   Next i
End If
End Function

 

[CJ_London]

I like the listbox (or combo) idea

 

[ebs17]

Each of the values in APPLIED CHQS relates to its own record in the CHQS Table.

You could also use a continuous form for this table.
The double-click event handler can be used to open this form, passing the contents of APPLIED CHQS as a filter => "FieldX IN (2, 4, 7)"
If the form shows three records instead of one record, that shouldn't hurt the overview; the user will certainly still know the value that he wanted to click on and evaluate.
For this sustained intellectual achievement, he doesn't need to click on one of the values again, but simply click on the control.

 

[raziel3]

Well, mainly because I have to have the form with the timer closed to edit any VBA code. It disrupts your experience. The good thing is that as soon as it starts ticking, it stops, in this case just to capture the double click, which to me it sounds like a hack.

I first tried to get the .SelText property just with the double click, it didn't work, then with the mouse up event, it didn't work either. So the timer was my last resource before changing the entire workflow to display a browser control, or popping up an additional form where I can select. Ideally, a rich text field should do this, but it does not support <a> tags.

So, basically, I wanted to do it via the obvious methods, but I had to resort to a hack.

Sorry everyone for not responding sooner. You all know how it is, it's just one of those weeks.

@Edgar_ Thanks for the explanation. Yeah, it is "hacky". I think I'll try @MajP code together with a pop-up form and see how it goes.

I was trying to come up with something

Private Sub CHQNUM_DblClick(Cancel As Integer)
Dim LArray() As String
Dim i As Integer

LArray = Split(CHQNUM, ",")

For i = LBound(LArray) To UBound(LArray)

Debug.Print Replace(LArray(i), " ", "")

Next i
End Sub

the results look like this

506
1199

Now that I technically created a list I need a way to tell which item in the list was clicked.

 

[MajP]

Now that I technically created a list I need a way to tell which item in the list was clicked.

That is pretty much what I did. I make the list and then figure out based on cursor location and locations of the commas which item it falls into.

@Edgar took advantage of an interesting feature. If you click into a delimited list it actually will select the text within the delimiter. However it takes a while to do this so you need the timer to wait until it has time to select.

 

[KitaYama]

@Edgar took advantage of an interesting feature. If you click into a delimited list it actually will select the text within the delimiter. However it takes a while to do this so you need the timer to wait until it has time to select.

Did @Edgar_ 's sample database work for you?
I receive the following error but didn't mention it. Mostly because I thought OP will ask about it. Or maybe I'm the only one who receives this error.

 

[MajP]

Works for me. I get that specific error a lot on certain DBs created in other language versions of Access. So it may be some kind of version compatability. Specifically Arabic databases, I have to import into a clean database to get them to work.

 

[KitaYama]

Works for me. I get that specific error a lot on certain DBs created in other language versions of Access. So it may be some kind of version compatability. Specifically Arabic databases, I have to import into a clean database to get them to work.

Thanks. I wonder why it happens for me. I'm on English version of Windows & Office And I believe Edgar_ is in German.
Importing to a blank database didn't work for me.

I had to recreate everything to make it work.
I'm OK now. Thank you.

 

[Edgar_]

I wish I had multiple versions of Office where I could test my samples, I wonder how many times others have experienced the inconvenience of opening a sample of mine and getting an error.

Maybe that error happens because I tried to use several different events and deleted the code many times? maybe the file stored some traces of my tests? or maybe it didn't like the underscore in the double click event?

I just exported the form's code-behind, I don't see anything that could be causing issues, so maybe it's some misconfigured property in the form.

Anyway, sorry about that.

 

[KitaYama]

Anyway, sorry about that.

Never mind about that.
As far as your code works for you, we can recreate it and check how your intelligence solves the problems.

Thanks again.

 

[raziel3]

@MajP your code works perfectly. Thanks.