Remove control from tab order 1

[cstuart79]

I am trying to remove a control from tab order so that it does not appear as a category when using the record selector to copy/paste into an email. I have tried setting Tab Stop to "No" which does seem to remove the control from the tab order but it still shows up when copying/pasting. Any way to resolve this?

 

[PHV]

it still shows up when copying/pasting
Why not set its Visible property to False ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cstuart79]

i want the controls to be visible in form view, but to be "invisible" when utilizing record selector to copy/paste...

 

[ProgramError]

Why do you need to copy and paste? Maybe we can help you do it a different way.

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[kjv1611]

So the visibility, etc, is conditional. You need to code it up possibly using the AfterUpdate, OnChange type events of various controls.

 

[cstuart79]

I need to be able to select a record (see attached screenshot) to copy/paste into outlook, but want to get rid of certain categories such as checkboxes since they show up as "-1", "0", etc. (see attached emailexample):

http://www.mediafire.com/i/?s2pthlc1ac5icuq

www.mediafire.com/i/?82iji5w1k95a2ax

 

[MajP]

Is it always a single record? Does it need to be in a table form? I think the easiest thing to do would be click on record and bring up a popup form for the selected record formatted the way you want it with the fields you want. Copy, close, paste.

 

[cstuart79]

i would prefer not to use pop-ups--is it possible to "remove" control from pasting and yet have it remain visible on form?

 

[MajP]

AFAIK, there is no way to do it as you describe. No idea why you would think the Tab order has anything to do with this. The closest I think I could do it if I had to do it that way would be using windows API functions.
Have a button on the form to copy selected record. Select the button and it would
1) open the properly formatted form, invisible.
2) copy the properly formatted record
4) close the hidden form

 

[MajP]

OK this works pretty well
code to copy a passed string to the clipboard
also a function for formatting strings to fixed lengths. The copy to clipboard is from allen browne
Drop everything into a module.

Declare Function abOpenClipboard Lib "User32" Alias "OpenClipboard" (ByVal Hwnd As Long) As Long
Declare Function abCloseClipboard Lib "User32" Alias "CloseClipboard" () As Long
Declare Function abEmptyClipboard Lib "User32" Alias "EmptyClipboard" () As Long
Declare Function abIsClipboardFormatAvailable Lib "User32" Alias "IsClipboardFormatAvailable" (ByVal wFormat As Long) As Long
Declare Function abSetClipboardData Lib "User32" Alias "SetClipboardData" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Declare Function abGetClipboardData Lib "User32" Alias "GetClipboardData" (ByVal wFormat As Long) As Long
Declare Function abGlobalAlloc Lib "Kernel32" Alias "GlobalAlloc" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function abGlobalLock Lib "Kernel32" Alias "GlobalLock" (ByVal hMem As Long) As Long
Declare Function abGlobalUnlock Lib "Kernel32" Alias "GlobalUnlock" (ByVal hMem As Long) As Boolean
Declare Function abLstrcpy Lib "Kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function abGlobalFree Lib "Kernel32" Alias "GlobalFree" (ByVal hMem As Long) As Long
Declare Function abGlobalSize Lib "Kernel32" Alias "GlobalSize" (ByVal hMem As Long) As Long
Const GHND = &H42
Const CF_TEXT = 1
Const APINULL = 0

''''''''''''''''''''''''''''''''''''''
' This enum is used by SizeString
' to indicate whether the supplied text
' appears on the left or right side of
' result string.
''''''''''''''''''''''''''''''''''''''
Public Enum SizeStringSide
    textleft = 1
    TextRight = 2
End Enum

Function Text2Clipboard(szText As String)
    Dim wLen As Integer
    Dim hMemory As Long
    Dim lpMemory As Long
    Dim retval As Variant
    Dim wFreeMemory As Boolean

    ' Get the length, including one extra for a CHR$(0) at the end.
    wLen = Len(szText) + 1
    szText = szText & Chr$(0)
    hMemory = abGlobalAlloc(GHND, wLen + 1)
    If hMemory = APINULL Then
        MsgBox "Unable to allocate memory."
        Exit Function
    End If
    wFreeMemory = True
    lpMemory = abGlobalLock(hMemory)
    If lpMemory = APINULL Then
        MsgBox "Unable to lock memory."
        GoTo T2CB_Free
    End If

    ' Copy our string into the locked memory.
    retval = abLstrcpy(lpMemory, szText)
    ' Don't send clipboard locked memory.
    retval = abGlobalUnlock(hMemory)

    If abOpenClipboard(0&) = APINULL Then
        MsgBox "Unable to open Clipboard.  Perhaps some other application is using it."
        GoTo T2CB_Free
    End If
    If abEmptyClipboard() = APINULL Then
        MsgBox "Unable to empty the clipboard."
        GoTo T2CB_Close
    End If
    If abSetClipboardData(CF_TEXT, hMemory) = APINULL Then
        MsgBox "Unable to set the clipboard data."
        GoTo T2CB_Close
    End If
    wFreeMemory = False

T2CB_Close:
    If abCloseClipboard() = APINULL Then
        MsgBox "Unable to close the Clipboard."
    End If
    If wFreeMemory Then GoTo T2CB_Free
    Exit Function

T2CB_Free:
    If abGlobalFree(hMemory) <> APINULL Then
        MsgBox "Unable to free global memory."
    End If
End Function

Public Sub testLength()
  Dim lname As String * 20
  Dim lnamelbl As String * 20
  Dim fname As String * 10
  Dim fnamelbl As String * 10
  lname = "te"
  fname = "test"
  Debug.Print lname & fname
End Sub




Public Function SizeString(Text As String, Length As Long, _
    Optional ByVal TextSide As SizeStringSide = textleft, _
    Optional PadChar As String = " ") As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SizeString
' This procedure creates a string of a specified length. Text is the original string
' to include, and Length is the length of the result string. TextSide indicates whether
' Text should appear on the left (in which case the result is padded on the right with
' PadChar) or on the right (in which case the string is padded on the left). When padding on
' either the left or right, padding is done using the PadChar. character. If PadChar is omitted,
' a space is used. If PadChar is longer than one character, the left-most character of PadChar
' is used. If PadChar is an empty string, a space is used. If TextSide is neither
' TextLeft or TextRight, the procedure uses TextLeft.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim sPadChar As String

If Len(Text) >= Length Then
    ' if the source string is longer than the specified length, return the
    ' Length left characters
    SizeString = Left(Text, Length)
    Exit Function
End If

If Len(PadChar) = 0 Then
    ' PadChar is an empty string. use a space.
    sPadChar = " "
Else
    ' use only the first character of PadChar
    sPadChar = Left(PadChar, 1)
End If

If (TextSide <> textleft) And (TextSide <> TextRight) Then
    ' if TextSide was neither TextLeft nor TextRight, use TextLeft.
    TextSide = textleft
End If

If TextSide = textleft Then
    ' if the text goes on the left, fill out the right with spaces
    SizeString = Text & String(Length - Len(Text), sPadChar)
Else
    ' otherwise fill on the left and put the Text on the right
    SizeString = String(Length - Len(Text), sPadChar) & Text
End If

End Function

now on the form you need to create as string to pass to the clipboard. You need to define the lables and the data. This has to be formatted in two lines so everything lines up.
In this example I just want to save the first name, last name, and birthdate for the record. The record has many other fields.

Public Sub GetCopyString()
  'Since this needs to look like a table need to define lengths
  Dim lname As String
  Dim lnamelbl As String
  Dim fname As String
  Dim fnamelbl As String
  Dim bDate As String
  Dim bDatelbl As String
  
  lnamelbl = SizeString("Last Name", 15, textleft)
  fnamelbl = SizeString("First Name", 15, textleft)
  bDatelbl = SizeString("Birthday", 15, textleft)
  
  lname = SizeString(Nz(Me.LastName, ""), 15, textleft)
  fname = SizeString(Nz(Me.FirstName, ""), 15, textleft)
  bDate = SizeString(Format(Nz(Me.BirthDate, 0), "MM/DD/YY"), 15, textleft)
  Dim strOut As String
  strOut = lnamelbl & fnamelbl & bDatelbl & vbCrLf
  strOut = strOut & lname & fname & bDate
  Text2Clipboard (strOut)
End Sub

the pasted record in outlook looks like

The only issue is you need a fixed length font. This method provides a lot of flexibility to format the string.