Doesn't delete field

with this it doesn't delete a record if the record's field only contains a period for some reason any ideas?

Rs.Open "Select * From Business", Conn, adOpenKeyset, adLockOptimistic
    pb1.Max = Rs.RecordCount
    With Rs
        Do Until Rs.EOF
        pb1.Value = pb1.Value + 1
        pb1.Refresh
        DoEvents
        DeleteRecord = False
            For fLoop = 0 To Rs.Fields.count - 1
                If Trim(Rs.Fields(fLoop).Value) = "" Or IsNull(Rs.Fields(fLoop).Value) Or Trim(Rs.Fields(fLoop).Value) = "-" Or Trim(Rs.Fields(fLoop).Value) = "." Then
                    DeleteRecord = True
                    Exit For
                End If
                Next
                If DeleteRecord = True Then
                Rs.Delete adAffectCurrent
                End If
        Rs.MoveNext
        Loop
    End With
LVL 8
thenoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
After the loop do you have Rs.Update? If not all deletes are done only in memory - never sent to the server.
clarkscottCommented:

Try changing VALUE to TEXT


  If Trim(Rs.Fields(fLoop).Value) = "" Or IsNull(Rs.Fields(fLoop).Value) Or Trim(Rs.Fields(fLoop).Value) = "-" Or Trim(Rs.Fields(fLoop).Value) = "." Then

change to

  If Trim(Rs.Fields(fLoop).text) = "" Or IsNull(Rs.Fields(fLoop).text) Or Trim(Rs.Fields(fLoop).text) = "-" Or Trim(Rs.Fields(fLoop).text) = "." Then



PS.  I think the Rs.DELETE actually DOES delete the record.  I've used this without an UPDATE ?????


Scott C.

clarkscottCommented:
Whoops.....

If Trim(Rs.Fields(floop).text) = "" or Trim(Rs.Fields(floop).text) = "-" .........

Remove the Isnull(Rs.Fields(floop).text) = ""  part.  The first part looks for 'no text'.  Isnull won't work for isnull...   .text.

You check for numeric values if you use the .VALUE syntax.  (If Rs.Fields(floop).value = 0 )

Scott C.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

thenoneAuthor Commented:
Can't use text in vb throws a method error
clarkscottCommented:
What part was thrown out (what highlighted on the error)?  The FIRST instance of 'TEXT'?

Scott C.
thenoneAuthor Commented:
Still doesn't work..
thenoneAuthor Commented:
Yes can't use text it won't compile not a valid property choice.
amit_gCommented:
Do you have the Rs.Update or Rs.UpdateBatch or not?
thenoneAuthor Commented:
No rs.update or rs.updateBatch do I need that?
amit_gCommented:
Yes.
clarkscottCommented:
>If Trim(Rs.Fields(fLoop).Value) = "" Or IsNull(Rs.Fields(fLoop).Value) Or Trim(Rs.Fields(fLoop).Value) = "-" Or Trim(Rs.Fields(fLoop).Value) = "." Then<

The user states that the "." record will delete.  If that's true then the Rs.Update doesn't  have to be there.  I'm stumped as to why the LAST condition in this statement works but not the leading 3.  I thnk the ' IsNull(Rs.Fields(fLoop).Value)' syntax look suspicious  - isnull(Rs.Fields(floop)) looks more like it would work.

????

I'm not currently working on a PC with Access or VB so I can't test.  I'm very curious of the solution, though.

Scott C.
thenoneAuthor Commented:
Im stumped as well I manually checked the records the field contains only a . in the middle of it.
amit_gCommented:
Did you debug it? Does it go into this if?

                If DeleteRecord = True Then
                Rs.Delete adAffectCurrent
                End If

add a MsgBox before it to check ...

                MsgBox DeleteRecord
                If DeleteRecord = True Then
                Rs.Delete adAffectCurrent
                End If

What do you get?
clarkscottCommented:
Could these leading characters be a TAB or some other 'goofy' unintentional character(s) not printable?
The TRIM works for spaces - but I'm not sure about TABS??

Scott C.


 
thenoneAuthor Commented:
well it shows up in the field as something googfy as paragraph indent character or something but then when I do a view it just shows as a period.
amit_gCommented:
Is DeleteRecord true ever? Try this ...

Add a reference to Microsoft VBScript Regular Expression 5.5 and change the

Dim re As New RegExp

re.Pattern = "^\W$"
re.Global = True

Rs.Open "Select * From Business", Conn, adOpenKeyset, adLockOptimistic
    pb1.Max = Rs.RecordCount
    With Rs
        Do Until Rs.EOF
        pb1.Value = pb1.Value + 1
        pb1.Refresh
        DoEvents
        DeleteRecord = False
            For fLoop = 0 To Rs.Fields.count - 1
            If Trim(Rs.Fields(fLoop).Value) = "" OR IsNull(Rs.Fields(fLoop).Value) OR re.Test(Trim(Rs.Fields(fLoop).Value)) Then
                    DeleteRecord = True
                    Exit For
                End If
                Next
                If DeleteRecord = True Then
                Rs.Delete adAffectCurrent
                End If
        Rs.MoveNext
        Loop
    End With

clarkscottCommented:
I think it's probably a TAB (a square character).  This is why the TRIM doesn't work.
If you can check the ASCII character of the problem character you may trap it.

Scott C.
thenoneAuthor Commented:
Scott this is what is in my fields ¶   .
but when I do a field viewer there is only  a .

¶ in ascii standards for beginning or new paragraph.Not sure if this would make a difference or not?
clarkscottCommented:
I believe it's a carriage return (chr(13))  
It's odd that it was entered into a field.  Maybe check your text box and make sure the ENTER KEY BEHAVIOR is set to DEFAULT (not New line in field).

I think I would manually delete this record and try to prevent this type of data entry.  This could very well be an extreme exception.

Scott C.
clarkscottCommented:
Further....

I have a Point of Sale system that uses a text box for scanning barcodes.  I added some functionality for auto-processing (an optional quick-entry process that I'm not going to describe here) and the results were that a carriage return (entered at the trailing end of the bar code scan) was being saved in the bar code text box even after I set the box to Null. - very strange.

I couldn't trap the character (carriage return) and was having all sorts of problems.  I eventually solved the problem by adding a DOEVENTS during the auto process.  IT WORKED.  Apparently, the text box (which is an ActiveX object) was 'interrupted' by other processes and wasn't 'allowed' to clear itself.  The DOEVENTS allowed the text box to 'do its thing' and all was good.

Sometimes we have to resort to smoke, mirrors, and chants :-)

Scott C.
thenoneAuthor Commented:
I do have a do events in my code becasue I have a status bar.It does seem to be a carriage return in there a carriage return plus a period.Thats weird.How would I resolve this problem?
thenoneAuthor Commented:
What about a sql execute statement like delete from table where (column) = '(chr(13)) + .'"

not sure if this the correct way of writing it.
amit_gCommented:
You yourself said that it is not a . then how would any combination of somthing + . would work? Did you try the regular expression? When you don't know what this character is, RE is easiest way to deal with it.
thenoneAuthor Commented:
sorry amit I did check my fields again and it is a crraige return or tab with a period.
amit_gCommented:
Ok then it is much easier.

FieldValue = Trim(Rs.Fields(fLoop).Value)
If IsNull(FieldValue) Then
      FieldValue = ""
Else
      FieldValue = Replace(FieldValue, vbCrLf, "")
      FieldValue = Replace(FieldValue, vbTab, "")
      FieldValue = Replace(FieldValue, ".", "")
      FieldValue = Replace(FieldValue, "-", "")
End IF

If FieldValue = "" Then
      DeleteRecord = True
      Exit For
End If

...
...
...

MsgBox DeleteRecord
clarkscottCommented:
To prevent this from happening again, maybe do these things...

---
I think this is what you need to do....
Check your text box and make sure the ENTER KEY BEHAVIOR is set to DEFAULT (not New line in field).
In the Change or Key Down event maybe you can check for TABS.
----

You could do this but I don't think it's necessary since Tabs and Carriage returns are form navigation keys... but....

Change
if Instr(1,me.textbox.text, chr(10)) > 0 then
     Me.textbox.text = left(Me.textbox.text, len(me.textbox.text)-1)
end if

This will check for the TAB and remove immediately if hit.




Scott C.


thenoneAuthor Commented:
amit put this inside the for loop correct?

FieldValue = Trim(Rs.Fields(fLoop).Value)
If IsNull(FieldValue) Then
     FieldValue = ""
Else
     FieldValue = Replace(FieldValue, vbCrLf, "")
     FieldValue = Replace(FieldValue, vbTab, "")
     FieldValue = Replace(FieldValue, ".", "")
     FieldValue = Replace(FieldValue, "-", "")
End IF

If FieldValue = "" Then
     DeleteRecord = True
     Exit For
End If
amit_gCommented:
Yes. Basically the above code should replace your existing code

                If Trim(Rs.Fields(fLoop).Value) = "" Or IsNull(Rs.Fields(fLoop).Value) Or Trim(Rs.Fields(fLoop).Value) = "-" Or Trim(Rs.Fields(fLoop).Value) = "." Then
                    DeleteRecord = True
                    Exit For
                End If
thenoneAuthor Commented:
Thanks Amit I will give that a try and let you know.
thenoneAuthor Commented:
On Error Resume Next
Dim DeleteRecord As Boolean, fLoop As Integer
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
    Conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=localhost;" _
            & "DATABASE=sites;" _
            & "UID=hbannerman;" _
            & "PWD=password1221;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

Conn.Open
    Dim Rs As ADODB.Recordset
    Set Rs = New ADODB.Recordset
    Rs.Open "Select * From Business", Conn, adOpenKeyset, adLockOptimistic
    pb1.Max = Rs.RecordCount
    With Rs
        Do Until Rs.EOF
        pb1.Value = pb1.Value + 1
        pb1.Refresh
        DoEvents
        DeleteRecord = False
            For fLoop = 0 To Rs.Fields.count - 1
                FieldValue = Trim(Rs.Fields(fLoop).Value)
                If IsNull(FieldValue) Then
                FieldValue = ""
                Else
                FieldValue = Replace(FieldValue, vbCrLf, "")
                FieldValue = Replace(FieldValue, vbTab, "")
                FieldValue = Replace(FieldValue, ".", "")
                FieldValue = Replace(FieldValue, "-", "")
                End If
                If FieldValue = "" Then
                DeleteRecord = True
                Exit For
                End If
                Rs.MoveNext
                Loop
               

I get loop without do?
amit_gCommented:
You missed the Next. That is why it is important to keep code properly indented.

Also your With Rs is not required so you could remove that too...

Rs.Open "Select * From Business", Conn, adOpenKeyset, adLockOptimistic
pb1.Max = Rs.RecordCount
Do Until Rs.EOF
      pb1.Value = pb1.Value + 1
      pb1.Refresh
      DoEvents
      DeleteRecord = False
      For fLoop = 0 To Rs.Fields.count - 1
            FieldValue = Trim(Rs.Fields(fLoop).Value)
            If IsNull(FieldValue) Then
                  FieldValue = ""
            Else
                  FieldValue = Replace(FieldValue, vbCrLf, "")
                  FieldValue = Replace(FieldValue, vbTab, "")
                  FieldValue = Replace(FieldValue, ".", "")
                  FieldValue = Replace(FieldValue, "-", "")
            End IF

            If FieldValue = "" Then
                  DeleteRecord = True
                  Exit For
            End If
      Next
      If DeleteRecord = True Then
            Rs.Delete adAffectCurrent
      End If
      Rs.MoveNext
Loop

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clarkscottCommented:
Wow.... good one!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.