copy 2 columns from listbox to clipboard

[megatronixs]

Hi all,

I made a search tab where I use SQL query to populate a listbox with what I type in a text box. I works great, but if there is text that starts with a "#" then it will not show this example: #AA.
I need to copy the first and second row into clipboard to paste it into a word document. And it should combine both into one text.

I can only copy the column that is set in the "Bound Column" (it is set to 1, if I change it to 2, then I can copy the second on)

This is the code I use for the search box and listbox:

Option Compare Database
Option Explicit
Private Const BASE_SQL As String = _
    "SELECT ind_prod, description, prod_code " & _
    "FROM tbl_prod_description " & _
    "<whereclause>" & _
    "ORDER BY prod_code;"
    
    Private Sub Search_Change()
'   This event fires for every keystroke in the textbox
    Dim where As String
    
    If Me.Search.Text <> "" Then
        where = "WHERE ind_prod LIKE '*" & Me.Search.Text & "*' "
    End If
        
    Me.ListSearch.RowSource = Replace(BASE_SQL, "<whereclause>", where)
End Sub

Any idea how to get this done?

Greetings.

 

[Minty]

I think Access is escaping the # as it thinks it's referencing a date field.
What happens if you try it in the normal query window with hard coded values?

I'd also be tempted to rename your variable to strWhere as where is obviously a reserved word...

As for getting your list values into a string that is simple concatenation in the after update event of the ListSearch

strListResult = Me.ListSearch & Me.ListSearch.Column(1)

The column numbers start at 0 rather confusingly when you are referencing them.

 

[megatronixs]

HI Minty,

Hard coded in the query editor it works, just like this "#AA"

I will work now on the button to see if I get it into the clipboard.
I don't have to use copy like in excel?

Greetings.

 

[Minty]

Try LIKE '*#AA' in the query editor, as that is what you are passing as the SQL.

To copy something directly to the clipboard there is a function you can use for this; (Not mine stolen from the interwebs somewhere...) Paste this code into a new module, save the module OtherFunctions;

Option Compare Database
Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
   ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
   As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
   ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
   As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
   Dim hGlobalMemory As Long, lpGlobalMemory As Long
   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.
   '-------------------------------------------
   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer
   ' to this memory.
   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.
   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.
   If GlobalUnlock(hGlobalMemory) <> 0 Then
      MsgBox "Could not unlock memory location. Copy aborted."
      GoTo OutOfHere2
   End If

   ' Open the Clipboard to copy data to.
   If OpenClipboard(0&) = 0 Then
      MsgBox "Could not open the Clipboard. Copy aborted."
      Exit Function
   End If

   ' Clear the Clipboard.
   X = EmptyClipboard()

   ' Copy the data to the Clipboard.
   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

   If CloseClipboard() = 0 Then
      MsgBox "Could not close Clipboard."
   End If

   End Function

 

[megatronixs]

Hi Minty,

I actually solved like this:

Private Sub txtSomeTextBox_Click()
    Dim strListResult As String
    Me.txtSomeTextBox = Me.ListSearch.Column(0) & " - " & Me.ListSearch.Column(1)
    DoCmd.RunCommand acCmdCopy

End Sub

And it works nice. Just used a textbox, and when I click on it, it will combine both columns and put them in the clipboard

I guess it was not that hard after all. Just need to solve the "#"

Greetings.

 

[Minty]

Glad you have half of it sorted out.
The ClipBoard routine I find very useful if you are setting it based on things not necessarily in view on a form or after a message box. e.g. Do you want to email the client with these results or put them in the clipboard for use elsewhere?

 

[megatronixs]

Hi Minty,

I need to put them in the clipboard to use them on a word document. I still need to figure out the textboxes in the word document as they are not clear to me right now.
In future I could make access write it straight to word.

the most silly one to solve is the "#" thing.

Greetings.

 

[Minty]

Add a Debug.Print of your complete SQL statement and see what it is actually passing to the query.

 

[static]

# is a wildcard so you need to wrap it in square brackets

[#]AA

 

[megatronixs]

hi Static,

Thanks, it is a good one
Just wonder how to add it to the SQL. Will have a few try outs to see what I get.

Greetings.

 

[sxschech]

Here are two other methods of copy to clipboard that uses less lines of code.

Option Compare Database
Const DATAOBJECT_BINDING As String = "new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"  'http://bytecomb.com/copy-and-paste-in-vba/

Public Sub CopyToClip(ByVal Expression As String)
'http://bytecomb.com/copy-and-paste-in-vba/
    With CreateObject(DATAOBJECT_BINDING)
        .SetText Expression
        .PutInClipboard
    End With
End Sub
 
Public Function PasteFromClip() As String
'http://bytecomb.com/copy-and-paste-in-vba/
    With CreateObject(DATAOBJECT_BINDING)
        .GetFromClipboard
        Paste = .GetText
    End With
End Function

This version puts the object in the code rather than in the options section

Sub CopyTextToClipboard(ByVal inText As String)
'https://desmondoshiwambo.wordpress.com/category/malibu-bytes/microsoft-access/vba/page/3/
'20160401
  Dim objClipboard As Object
  Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  objClipboard.SetText inText
  objClipboard.PutInClipboard

  Set objClipboard = Nothing
End Sub

Function GetTextFromClipboard() As String
'https://desmondoshiwambo.wordpress.com/category/malibu-bytes/microsoft-access/vba/page/3/
'20160401
  Dim objClipboard As Object
  Set objClipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  objClipboard.GetFromClipboard
  GetTextFromClipboard = objClipboard.GetText

  Set objClipboard = Nothing
End Function