Adding New Records in MS Access Problem

[nagatron]

Hello to all, I am always mad about this problem. If I type a name with two words in a textbox like "google corporation" it will be registered in the database ms access as "Google Corporation" now that is okay. What I want is, there should be no duplicate of names, but the problem is, when I enter "google corporation" it will be registered again in the database instead of displaying "No duplicate". This is my code:

Private Sub Command1_Click()

Dim rst As New ADODB.Recordset
Dim i As Integer
Dim R_Count As Currency

With rst
    .ActiveConnection = con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "SELECT * FROM records"
End With


If rst.AbsolutePosition > -1 Then
    rst.MoveLast
    R_Count = rst.RecordCount
    rst.MoveFirst
    
    For i = 1 To R_Count
        If rst.Fields("name") = Trim$(Text9.Text) Then
            MsgBox "No Duplicate", vbCritical, "Duplicate Name"
            Text1.SetFocus
            Exit Sub
            End If
            rst.MoveNext
            Next i
            Call saveData
            Else
            Call saveData
        End If
End Sub

This code only work like this:

TextBox: Database:

Google Corporation---->Google Corporation = OK
google Corporation---->Google Corporation = Not Working
Google corporation---->Google Corporation = Not Working
google corporation---->Google Corporation = Not Working

As you can see, the code only work if what you enter in the textbox is equal to the database. Is there a way where I can convert "google corporation or google Corporation or Google corporation" to "Google Corporation"?

[AndreRet]

You need to determine or read between lower and upper case. First do the check, reads lower and then upper case, compares with your data entry. If matched, move on.

Have a look at the following links, which gives you sample code on how to determine the values of each first character in a letter.

http://www.dreamincode.net/forums/topic/12952-visual-basic-6-determining-each-character-in-vb/

http://www.bigresource.com/VB-Uppercase-Lowercase-1xQiCHkkK3.html#NAB05E2KX2

[PoisonedHeart]

why don't you just, convert the input text to lowercase and convert the data from the database into lowercase then compare the two of them...

I think you can optimize your code,

you may change your SELECT statment into

.Open "SELECT * FROM records WHERE name=" & Trim$(Text9.Text)

so that you don't have to use a loop just to compare all the records within your query result..

then you can check the result by:

If rst.BOF and rst.EOF = True Then 'returns true when the results are empty, meaning the input text has no matching records within the query.
     MsgBox "No Duplicate", vbCritical, "Duplicate Name"
     Text1.SetFocus
     Exit Sub

[nagatron]

why don't you just, convert the input text to lowercase and convert the data from the database into lowercase then compare the two of them...

I think you can optimize your code,

you may change your SELECT statment into

.Open "SELECT * FROM records WHERE name=" & Trim$(Text9.Text)

so that you don't have to use a loop just to compare all the records within your query result..

then you can check the result by:

If rst.BOF and rst.EOF = True Then 'returns true when the results are empty, meaning the input text has no matching records within the query.
     MsgBox "No Duplicate", vbCritical, "Duplicate Name"
     Text1.SetFocus
     Exit Sub

I can convert all the text in the text input into lower case but the problem is, how can I convert the data from the database into lower case?

[nagatron]

The only way to solve this either:

How to convert the first letter of each word into upper case> or How to convert data from database into lower case? Which is both I don't know.

[nagatron]

I would like to apologize, problem solve. . I found out how to solve the problem. The answer is pretty simple. I am using

rst.Fields!name= StrConv(Text1, vbProperCase)

in saving data to database. Now to solve the problem I use this condition.

If rst.Fields("name") = StrConv(Text1.Text, vbProperCase)

Which capitalize all the 1st character in a word. Ex. i am now happy = I Am Now Happy