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"?

Edited 6 Years Ago by nagatron: n/a

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

Edited 6 Years Ago by PoisonedHeart: n/a

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?

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.

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

Edited 6 Years Ago by nagatron: n/a

This question has already been answered. Start a new discussion instead.