Access 2007 how to enter separate lines of address from web into textbox
I'm developing a program to copy name and address information from the web using the clipboard.
I have a textbox on a form for the information. There is a command button.
Each time the command button is hit, I want the info in the clipboard to appear in the textbox.
I'm having no problem if the info on the web is in normal name/address format. I select it all and copy it all to the clipboard and then to the form. It appears in the textbox line by line. However, if the address lines are separated on the web page, I'd like to copy them one at a time and have each one of the appear as a separate line in the text box ([ipAddrInfo]).
For sake of simplicity, assume that I have a string Dim called HoldClip that the clipboard is properly copied to. I've been trying to use Allen Browne's InsertAtCursor function, but I cannot seem to get the cursor properly set after each address line.
Private Sub cmdFromWeb_Click()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
'Get data from the clipboard.
DataObj.GetFromClipboard
'Get clipboard contents
HoldClip = DataObj.GetText(1)
[ipAddrInfo].SetFocus
If IsNull([ipAddrInfo]) Then
[ipAddrInfo] = HoldClip
[ipAddrInfo].SelStart = 0
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
Exit Sub
End If
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
Call InsertAtCursor(HoldClip)
End Sub
The above code after the first command click enters the correct line of data but with no cursor. The second address line is appended directly to it...no space, no cursor.
I have a textbox on a form for the information. There is a command button.
Each time the command button is hit, I want the info in the clipboard to appear in the textbox.
I'm having no problem if the info on the web is in normal name/address format. I select it all and copy it all to the clipboard and then to the form. It appears in the textbox line by line. However, if the address lines are separated on the web page, I'd like to copy them one at a time and have each one of the appear as a separate line in the text box ([ipAddrInfo]).
For sake of simplicity, assume that I have a string Dim called HoldClip that the clipboard is properly copied to. I've been trying to use Allen Browne's InsertAtCursor function, but I cannot seem to get the cursor properly set after each address line.
Private Sub cmdFromWeb_Click()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
'Get data from the clipboard.
DataObj.GetFromClipboard
'Get clipboard contents
HoldClip = DataObj.GetText(1)
[ipAddrInfo].SetFocus
If IsNull([ipAddrInfo]) Then
[ipAddrInfo] = HoldClip
[ipAddrInfo].SelStart = 0
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
Exit Sub
End If
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
Call InsertAtCursor(HoldClip)
End Sub
The above code after the first command click enters the correct line of data but with no cursor. The second address line is appended directly to it...no space, no cursor.
ASKER
Where do I find the MultiLine property?
Public Function InsertAtCursor(strChars As String, Optional strErrMsg As String) As Boolean
On Error GoTo Err_Handler
'Purpose: Insert the characters at the cursor in the active control.
'Return: True if characters were inserted.
'Arguments: strChars = the character(s) you want inserted at the cursor.
' strErrMsg = string to append any error messages to.
'Note: Control must have focus.
Dim strPrior As String 'Text before the cursor.
Dim strAfter As String 'Text after the cursor.
Dim lngLen As Long 'Number of characters
Dim iSelStart As Integer 'Where cursor is.
If strChars <> vbNullString Then
With Screen.ActiveControl
If .Enabled And Not .Locked Then
lngLen = Len(.Text)
'SelStart can't cope with more than 32k characters.
If lngLen <= 32767& - Len(strChars) Then
'Remember characters before cursor.
iSelStart = .SelStart
If iSelStart > 1 Then
strPrior = Left$(.Text, iSelStart)
End If
'Remember characters after selection.
If iSelStart + .SelLength < lngLen Then
strAfter = Mid$(.Text, iSelStart + .SelLength + 1)
End If
'Assign prior characters, new ones, and later ones.
.Value = strPrior & strChars & strAfter
'Put the cursor back where it as, after the new ones.
.SelStart = iSelStart + Len(strChars)
'Return True on success
InsertAtCursor = True
End If
End If
End With
End If
Exit_Handler:
Exit Function
Err_Handler:
Debug.Print Err.Number, Err.Description
Select Case Err.Number
Case 438&, 2135&, 2144& 'Object doesn't support this property. Property is read-only. Wrong data type.
strErrMsg = strErrMsg & "You cannot insert text here." & vbCrLf
Case 2474&, 2185& 'No active control. Control doesn't have focus.
strErrMsg = strErrMsg & "Cannot determine which control to insert the characters into." & vbCrLf
Case Else
strErrMsg = strErrMsg & "Error " & Err.Number & ": " & Err.Description & vbCrLf
End Select
Resume Exit_Handler
End Function
Public Function InsertAtCursor(strChars As String, Optional strErrMsg As String) As Boolean
On Error GoTo Err_Handler
'Purpose: Insert the characters at the cursor in the active control.
'Return: True if characters were inserted.
'Arguments: strChars = the character(s) you want inserted at the cursor.
' strErrMsg = string to append any error messages to.
'Note: Control must have focus.
Dim strPrior As String 'Text before the cursor.
Dim strAfter As String 'Text after the cursor.
Dim lngLen As Long 'Number of characters
Dim iSelStart As Integer 'Where cursor is.
If strChars <> vbNullString Then
With Screen.ActiveControl
If .Enabled And Not .Locked Then
lngLen = Len(.Text)
'SelStart can't cope with more than 32k characters.
If lngLen <= 32767& - Len(strChars) Then
'Remember characters before cursor.
iSelStart = .SelStart
If iSelStart > 1 Then
strPrior = Left$(.Text, iSelStart)
End If
'Remember characters after selection.
If iSelStart + .SelLength < lngLen Then
strAfter = Mid$(.Text, iSelStart + .SelLength + 1)
End If
'Assign prior characters, new ones, and later ones.
.Value = strPrior & strChars & strAfter
'Put the cursor back where it as, after the new ones.
.SelStart = iSelStart + Len(strChars)
'Return True on success
InsertAtCursor = True
End If
End If
End With
End If
Exit_Handler:
Exit Function
Err_Handler:
Debug.Print Err.Number, Err.Description
Select Case Err.Number
Case 438&, 2135&, 2144& 'Object doesn't support this property. Property is read-only. Wrong data type.
strErrMsg = strErrMsg & "You cannot insert text here." & vbCrLf
Case 2474&, 2185& 'No active control. Control doesn't have focus.
strErrMsg = strErrMsg & "Cannot determine which control to insert the characters into." & vbCrLf
Case Else
strErrMsg = strErrMsg & "Error " & Err.Number & ": " & Err.Description & vbCrLf
End Select
Resume Exit_Handler
End Function
If you're using a TextBox control then that's probably why it didn't work. The TextBox control doesn't have the Value property, so the following line in the InsertAtCursor sub-routine will generate a run-time error. Please check the Debug window (Immediate window) and see if there is any error printed.
.Value = strPrior & strChars & strAfter ' will generate an error if the control is a TextBox
Change to:
.Value = strPrior & strChars & strAfter ' will generate an error if the control is a TextBox
Change to:
.Text = strPrior & strChars & strAfterASKER
No luck....same result.
The "[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1" doesn't seem to be doing the job it's supposed to do i.e., placing the cursor at the start of the next blank line. The cursor remains at then end of the line entered.
Even if I manually move the cursor to the start of the next blank line and then bring in the second line InsertAtCursor places it at the end of the first line not on a separate new line.
The "[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1" doesn't seem to be doing the job it's supposed to do i.e., placing the cursor at the start of the next blank line. The cursor remains at then end of the line entered.
Even if I manually move the cursor to the start of the next blank line and then bring in the second line InsertAtCursor places it at the end of the first line not on a separate new line.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works just fine. I wonder why InsertAtCursor didn't. Thanks!
Private Sub cmdFromWeb_Click()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
'Get data from the clipboard.
DataObj.GetFromClipboard
'Get clipboard contents
HoldClip = DataObj.GetText(1)
[ipAddrInfo].SetFocus
If IsNull([ipAddrInfo]) Then
[ipAddrInfo] = HoldClip
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
Exit Sub
End If
[ipAddrInfo] = [ipAddrInfo] & vbCrLf
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
[ipAddrInfo].Text = [ipAddrInfo].Text & HoldClip
End Sub
Private Sub cmdFromWeb_Click()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
'Get data from the clipboard.
DataObj.GetFromClipboard
'Get clipboard contents
HoldClip = DataObj.GetText(1)
[ipAddrInfo].SetFocus
If IsNull([ipAddrInfo]) Then
[ipAddrInfo] = HoldClip
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
Exit Sub
End If
[ipAddrInfo] = [ipAddrInfo] & vbCrLf
[ipAddrInfo].SelStart = Len([ipAddrInfo].Text) + 1
[ipAddrInfo].Text = [ipAddrInfo].Text & HoldClip
End Sub
Also, could you post the code in the InsertAtCursor() subrountine?