Solved

Access 2003 to 2007 text box blank line problem

Posted on 2013-11-30
32
931 Views
Last Modified: 2013-12-05
I have an Access 2003 mdb. On one of the forms there is a text box.  Address text from the internet is copied into the clipboard . VBA code in the form then copies it into the form's textbox.

I have a routine that deletes any blank lines.  The routine works just fine in
Access 2003, but I've copied the mdb to another computer with Access 2007 and,
for some reason it doesn't do the job.  The program continues on ok, but doesn't
get rid of the blank lines.

This is the routine. I added the MsgBoxes to help me debug what is going on.
With the info below I get 1 of msg #1 and 1 of msg #3 :

Public Function NoBlankLines()
Const CRLFCRLF = vbCrLf & vbCrLf
MsgBox ("gottohere1")
    Do While InStr([ipAddrInfo].Text, CRLFCRLF)
MsgBox ("gottohere2")
        [ipAddrInfo] = Replace([ipAddrInfo], vbLf & vbCr, "")
    Loop
MsgBox ("gottohere3")
End Function

Copied Info (7 lines):

SendFlowersFree
http://www.sendflowersfree.com
(866) 1234-5971


801 W. SR 123, Suite 9999
Soda Springs, FL  33333
0
Comment
Question by:WFBweb
  • 18
  • 13
32 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39687651
A couple of notes...

1.  You've declared this as a public function, but you are referring to a textbox named ipAddrInfo without using a form reference.  This code will ONLY work if it is located in the same form as your ipAddrInfo textbox (unless you add a form reference).

2.  The Text property is incorrect for MS Access VBA... unless you are running the code while the textbox is in focus, but if you are running this from something like a command button click event, then you need to drop the Text property.

Those two points apply regardless of what version of Access you are using,

Apart from that your code works like a charm.

So... the function needs to be in the same form as ipAddrInfo Textbox, and drop the .Text property:


Public Function NoBlankLines()
Const CRLFCRLF = vbCrLf & vbCrLf
MsgBox ("gottohere1")
    Do While InStr([ipAddrInfo], CRLFCRLF)
MsgBox ("gottohere2")
        [ipAddrInfo] = Replace([ipAddrInfo], vbLf & vbCr, "")
    Loop
MsgBox ("gottohere3")
End Function

Open in new window

0
 

Author Comment

by:WFBweb
ID: 39687699
The function is and has always been in the form vba.  It worked in 2003 with the .text.  However, I just removed it and the result is the same....two blank lines.   ?????
0
 
LVL 7

Expert Comment

by:andymacf
ID: 39688005
This may be related to the fact the jet database engine has been changed significantly for 2007 onwards.

Herewith a link to a previous question I had, here

Andy
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39688028
I'm able to run it just fine in Access 2003, 2007 and 2010.  Can you either post a sample with sensitive data such as ip addresses masked, or post more details such as

1. Where the function is located
2. The code you are using to call the function
3. How you are calling the function ( command button click, text box change event, etc)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39688043
This also could be an issue with the actual data.  If you are using Rich Text, or if the data is being copied from the web, etc,  then those breaks between lines might be something other than carriage return/line feeds.
0
 

Author Comment

by:WFBweb
ID: 39688350
The routine below is called from a command button.  The lines down to Call GetClipBoardText are general form housekeeping and do not impact the text box.  The CleanInput routine gets rid of unnecessary spaces, etc.  It does not affect the number of final lines showing in the text box.  I just clicked the command button without having it performed and the blank lines were still there.

Private Sub cmdNewAddr_Click()
Me![chkOrg] = -1
[ipAddrInfo] = Null
Forms![fDexAddr1]![InfoRev] = Null
Call BlankOutFields
[imgArrow2].Visible = False
[imgArrow3].Visible = False
[imgArrow4].Visible = False
On Error GoTo ErrHandler
Call GetClipBoardText
Call NoBlankLines
Call CleanInput
ExitHere:
Exit Sub
ErrHandler:
If Err.Number = 424 Then
    MsgBox ("No copied data is available to paste.")
    Resume ExitHere
End If
End Sub

Function GetClipBoardText()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject

On Error GoTo cbderr

'Get data from the clipboard.
DataObj.GetFromClipboard
[ipAddrInfo].SetFocus
'Get clipboard contents
[ipAddrInfo] = DataObj.GetText(1)
 
Exit Function
cbderr:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Function
refs.JPG
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39688990
You can verify whether the code still works by:

1.  Enter some plain text... not copy/pasted -- just simply enter it by hand into your ipAddrInfo textbox, including some blank lines between text (You may need to use ctrl-Enter, depending on your textbox properties)

2.  Run  Function NoBlankLines without the rest of the code.... just  the call to Function NoBlankLines in the click event.

3.  If that doesn't work, verify that the textbox has Plain Text as it Text Format (under the data tab in the property sheet), and try that test again.


Since your code involves copy/pasted text, my guess is still that the data is at fault... and that those spaces are not actually carriage return-line feeds.

Also, check the data as seen in the underlying table.  Do you see anything like "<br>" (the HTML tag for new lines) in your data?

If so, modify your code like this (just an example.  check for other HTML tags, such as <p> too):


Public Function NoBlankLines()
Const CRLFCRLF = vbCrLf & vbCrLf
MsgBox ("gottohere1")
    Do While (InStr([ipAddrInfo], CRLFCRLF) OR InStr([ipAddrInfo], "<br>"))
MsgBox ("gottohere2")
        [ipAddrInfo] = Replace([ipAddrInfo], vbLf & vbCr, "")
        [ipAddrInfo] = Replace([ipAddrInfo],"<br>", "")
    Loop
MsgBox ("gottohere3")
End Function

Open in new window

0
 

Author Comment

by:WFBweb
ID: 39690432
When I typed it in it worked correctly (the blank lines were gone).  When I executed the revised function I got 1 of msg #1 and 1 of msg #3 and the blank lines were still there.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39690859
The manual typing test was just to demonstrate that the code does indeed find double -CRLF's and remove them in Access 2010, so I think the issue is with the data itself.

What source did you copy the text from -- Excel, Website....?

And did you check, per my last comment, whether there were any HTML tags in the data in your table?

Try running this function, and post the output text from the Debugger Window into your next comment.

Function TestSpecialClaracters()
       if InStr([ipAddrInfo], vbCrlf & vbCRLF) Then Debug.Print "Test 1: Double CRLF Found"
       if InStr(1, [ipAddrInfo], vbCrlf & vbCRLF) > 0  Then Debug.Print "Test 2: Double CRLF Found"
       if InStr(1, [ipAddrInfo], vbCrlf ) > 0  Then Debug.Print "Test 3: Single CRLF Found"
       if InStr(1, [ipAddrInfo], Chr(10)) > 0  Then Debug.Print "Test 4: Chr(10) Found"
       if InStr(1, [ipAddrInfo], CHR(13)) > 0  Then Debug.Print "Test 5: Chr(13) Found"
End Function

Open in new window

0
 

Author Comment

by:WFBweb
ID: 39691023
Yes, the one I've been testing with has an HTML int it.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691122
*sigh*

You are ignoring most of the questions I'm asking you.

Did you try the code I posted?  What lines if any were printed in the debugger window?

Can you be more specific about the tags please?

what tags, exactly?

Better yet, please post a sample database as I'd requested earlier.

I'm asking these questions to get to the bottom of what the blank lines in your data are made up of.

Without the information I'm asking you for, I am simply taking wild guesses as to what characters need to be removed... And that is not a good use of my time.
0
 

Author Comment

by:WFBweb
ID: 39691338
Sorry, I assumed that since you'd asked about the HTML,  you'd be able to identify the problem with only that answer.  

I copied the data from an internet site www.dexknows.com....the Local Business option.

This is the copied info from a notebook page I copied it to:

Big Company
http://websitegeneric.com
 (111) 222-3333
 
 
 99 Somewhere St
Smalltown, MA 02116

I'll try the last function you entered and get back to you with the results.  I'd rather hold off and putting my database onto EE.
0
 

Author Comment

by:WFBweb
ID: 39691367
Results in Debug.print:
Test 3: Single CRLF found
Test 4: Chr(10) Found
Test 5: Chr(13) Found
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691393
Ok I'm seeing some HTML tags in that, but it might not be complete.

Try this:

0.  Make a backup of your database

1.  Open your form in design view

2.  Change the Text Format property of your ipAddrInfo textbox to Plain Text (this property is located under the Data tab in the property sheet)

This should force any HTML tags to appear with your data when you look at the form in Form View.

Copy/Paste the data from one of the memo fields in its entirety into your next post.  You can change any names, phone numbers, etc to cartoon characters and fake data to protect confidential info.  The text content itself is not important, but the specific HTML tags and their locations are.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691409
From the results you posted it looks like there are some CRLFs in there.  But I am guessing that those are separating Name, website, phone number, etc... and that those shouldn't be removed.  (But there are no 'double spaces' made up of two CRLFs.

Just the same, try this *after making a backup* and see if it gets you closer:

Public Function NoBlankLines()
Const CRLFCRLF = vbCrLf & vbCrLf
MsgBox ("gottohere1")
    Do While (InStr([ipAddrInfo], vbCRLF) OR InStr([ipAddrInfo], "<br>"))
MsgBox ("gottohere2")
        [ipAddrInfo] = Replace([ipAddrInfo], vbCRLF, "")
        [ipAddrInfo] = Replace([ipAddrInfo],"<br>", "")
    Loop
MsgBox ("gottohere3")
End Function

Open in new window



(Try that, but please still post the memo field - 'sanitizing' it as described above, and including any html tags you see with the textbox's plain text property )
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691430
One more quick note... when you change the text format property to plain text, bypass Access's popup suggestion to convert the table's field format to plain text.

You want to:

- Change only the textbox's property to Plain Text
-  and leave the underlying field untouched, formatted as Rich Text in the table's design.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:WFBweb
ID: 39691531
The textbox's property has always been Plain Text.  It's Control Source is blank.  I have another command button that takes the textbox's data and generates fields on the form that are then added to a table after the user verifies/fixes them.  For example the city/state/zip line not only ends up in a citystatezip control, but the city, state and zip are separated into their own fields.  Also separate controls for HTTP, email address, phone, etc.

Copied from internet:
Garage At Government Ctr
http://www.govgarage.com
 (999) 123-4567  
 
 
 50 New Somewhere St
Boston, MA 54321

This appears in the text box as a result of running your latest function:
Garage At Government
Ctrhttp://www.governmentcentergarage.com  (999)
123-4567     50 New Somewhere StBoston, MA 54321

However when I copied it from the internet directly into Notebook it was just one long line of data.

I also tried changing the textbox's Text Format to Rich Text and the results were the same as above.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691561
Try this, and post back the debugger printout:


Function TestSpecialClaracters()
       if InStr([ipAddrInfo], "<Div>") Then Debug.Print "Test 1: Div Found"
       if InStr(1, [ipAddrInfo], "<P>") > 0  Then Debug.Print "Test 2: P Found"
       if InStr(1, [ipAddrInfo], "<td>"  ) > 0  Then Debug.Print "Test 3: td Found"
       if InStr(1, [ipAddrInfo], Chr(10) & Chr(10)) > 0  Then Debug.Print "Test 4:  double Chr(10) Found"
       if InStr(1, [ipAddrInfo], CHR(13) &  CHR(13)) > 0  Then Debug.Print "Test 5: double Chr(13) Found"
End Function

Open in new window

0
 

Author Comment

by:WFBweb
ID: 39692714
I'm about ready to give up on this thing.  When I ran the last function, nothing appeared in the debugger window and address info in the textbox looked like it had no break codes at all so I copied it into Notepad and, indeed it was just one long line of data.  I, then, copied the data down from the internet directly into Notepad and all seven lines showed up.

Next, I executed the GetClipboardText function by itself and the result was still continuous data.  I haven't touched the GetClipboardText function since we started.

Function GetClipBoardText()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
On Error GoTo cbderr
'Get data from the clipboard.
DataObj.GetFromClipboard
[ipAddrInfo].SetFocus
'Get clipboard contents
[ipAddrInfo] = DataObj.GetText(1)
Exit Function
cbderr:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Function
0
 

Author Comment

by:WFBweb
ID: 39696727
To simplify this problem, I've created an mdb with a single form (see attachment) with the textbox on it.  
Clicking the top command button (cmdNewAddr) performs the cmdNewAddr sub and copies the data from the clipboard but it does not remove the blank lines although the   NoBlankLines function is called.

Clicking the cmd button with the X on it removes the content of the textbox.
Clicking the icon with the 1 on it performs the cmdBlankLinesOut 1 sub. If there is data in the textbox, it removes the lines.

Clicking the cmd button with the 2 on it is another attempt to remove the lines, but it does not remove the lines apparently because the GetClipBoard function is called immediately before it and therein lies the problem.  

Below is the code behind form.  There is no other vba in the mdb.
Option Compare Database
Dim egg
Dim InpSplit
Dim HoldLine1
Dim HoldToAddr
Private Sub cmdBlankLinesOut1_Click()
NoBlankLines
SendKeys "{END}"
End Sub
Private Sub cmdBlankLinesOut2_Click()
Call GetClipBoardText
NoBlankLines
SendKeys "{END}"
End Sub
Private Sub cmdErase_Click()
Me![ipAddrInfo] = Null
End Sub
Private Sub Form_Open(Cancel As Integer)
Me![ipAddrInfo].SetFocus
DoCmd.MoveSize 0.05 * 1440, , 3.75 * 1440, 3.1 * 1440
End Sub
Function GetClipBoardText()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
On Error GoTo Whoa
'Get data from the clipboard.
DataObj.GetFromClipboard
[ipAddrInfo].SetFocus
'Get clipboard contents
[ipAddrInfo] = DataObj.GetText(1)
Exit Function
Whoa:
If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Function
Private Sub cmdNewAddr_Click()
[ipAddrInfo] = Null
noaddrlines = 0
On Error GoTo ErrHandler
[ipAddrInfo].SetFocus
Call GetClipBoardText
Call NoBlankLines
Call CleanInput
SendKeys "{END}", True
ExitHere:
Exit Sub
ErrHandler:
If Err.Number = 424 Then
    MsgBox ("No copied data is available to paste.")
    Resume ExitHere
End If
End Sub
Function NoBlankLines()
[ipAddrInfo].SetFocus
Const CRLFCRLF = vbCrLf & vbCrLf
' continue until result of InStr = 0
Do While InStr([ipAddrInfo].Text, CRLFCRLF)
[ipAddrInfo] = Replace([ipAddrInfo], vbLf & vbCr, "")
Loop
End Function
Public Function CleanInput()
Dim InpSplit
Dim s As String
'[ipToName1].SetFocus
InpSplit = Split(Me![ipAddrInfo], vbCrLf)
For i = LBound(InpSplit) To UBound(InpSplit)
    InpSplit(i) = Trim(InpSplit(i))
    InpSplit(i) = Replace(InpSplit(i), ".", "")
    InpSplit(i) = Replace(InpSplit(i), ",  ", ", ")
Next i
Me!ipAddrInfo = Join(InpSplit, vbCrLf)
End Function
tempform.JPG
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39697110
Hi - I'll try to take a look at this tonight.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39697263
Okay - I wrote a function to read your data character by character and write characters and ascii codes into a table (called Table1).

The output looked like this:

Characters and codes
So you have Carriage Return /Line Feeds (Chr(13) + Chr(10)), but that website is throwing in spaces (ascii 32) in between.... which is making your code, which looks for consecutive CRLFs, fail.

This is the function I used:
    Dim I
    Dim s As String
    Dim strsql As String
    Dim strChar As String
    Dim strNum As String
    s = Me.IPAddrInfo
    For I = 1 To Len(s)
        strChar = Mid(s, I, 1)
        strNum = Asc(strChar)
        strsql = "INSERT INTO table1 (Letter, CharCode) VALUES('" & strChar & "','" & strNum & "')"
        CurrentDb.Execute strsql, dbFailOnError
    Next

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 39697286
So try this variation on your code:

Public Function NoBlankLines()
Dim DexKnowsCRLF As String
Const CRLFCRLF = vbCrLf & vbCrLf
    DexKnowsCRLF = Chr(32) & Chr(13) & Chr(10)  '<--- This is how DexKnows does CRLFs
'MsgBox ("gottohere1")
    Me.IPAddrInfo = Replace(Me.IPAddrInfo, DexKnowsCRLF, vbCrLf)  '<--- Change DexKnows CRLFs to vbCRLFs which your code will detect when removing blank lines
    Do While InStr([IPAddrInfo], CRLFCRLF)
'MsgBox ("gottohere2")
        [IPAddrInfo] = Replace([IPAddrInfo], vbLf & vbCr, "")
    Loop
'MsgBox ("gottohere3")
End Function

Open in new window


This changes the "new lines" that dexknows uses on its website to vbCRLF's which your code will recognize in removing blank lines.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39697289
So that will take care of that specific case (DexKnows), but when you are dealing with data from the web, you do get characters, or sequences of characters that don't necessarily conform to what you get when you manually enter text in your memo fields.... and the next site you use could break this function again.

So keep the code I posted in http:#a39697263 handy.  It will at least help you determine what some of the special/unexpected character are, so that you can modify your code to handle them.
0
 

Author Comment

by:WFBweb
ID: 39698756
I'm curious to know why it works with no changes in Access 2003.  Surely DexKnows doesn't change what they send.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39698812
It's entirely possible that this was caused by a design update on their site.
0
 

Author Comment

by:WFBweb
ID: 39699288
Looks like that could be true.  I just reran the test mdb on my pc with Access 2003 and the same problem exists there, but I did go back and forth between 2003 and 2007 many times and 2003 did the job while 2007 didn't.

Maybe something to do with the clipboard processing?  I'll keep playing around with it.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39699334
It is more likely in the data provided by the site than in the processing on your side.

Again, any recent change to the site layout or code, the way they are compiling the data for the address info etc could have caused it.  (And if you pull data from a different source you probably would not have that specific problem -- but you could conceivably encounter a different issue)

Did it work with the additions I made to the code?
0
 

Author Comment

by:WFBweb
ID: 39699807
I'll give it a try.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39699832
Okay
0
 

Author Comment

by:WFBweb
ID: 39699852
Works fine so I'll close this out.  Thanks for your help.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39699857
Glad to help. :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now