ummm, i got a for that is connected to a database, and have an

add, edit, delete cmd buttons.

if example i have "MyName" already on a certain field of a table on a database,

then on my form i wanna add "MyName" also,

what i want is there will be a msgbox that will tell theres already "MyName" on my table,

-------
also if i add into the table

"MyNaMe" or "MYNAME" (have diff Uppercase and Lowercase charater)
is will still recognize it as a simple "MyName"


pls help me on this one.

Recommended Answers

All 8 Replies

check out this application logic. assuming that the database used by you is an access database. the db here used is "employee", table "details" with one field "empname". (the Green Lines are comments)

Option Explicit

Dim db As Database
Dim rs As Recordset

''performing the addition operation with the validation
Private Sub Command1_Click()
If Trim(txtempname.Text) <> "" Then
    If IsEmpExists(Trim(txtempname.Text)) Then
        MsgBox "The employee " & Chr(34) & Trim(txtempname.Text) & Chr(34) & " already exists in the database." & vbCrLf & _
            "Please use a different name rather than this one.", vbInformation, "Duplicate Employee"
        txtempname.SelStart = 0
        txtempname.SelLength = Len(Trim(txtempname.Text))
        txtempname.SetFocus
    Else
        rs.AddNew
        If rs.EditMode = dbEditAdd Then
            rs!empname = Trim(txtempname.Text)
            rs.Update
            Call LoadEmployees
            MsgBox "New employee created.", vbInformation, "Done"
            txtempname.Text = ""
            txtempname.SetFocus
        End If
    End If
Else
    MsgBox "The employee name should not be null.", vbCritical
    txtempname.SetFocus
End If
End Sub

Private Sub Form_Load()
''making connection to the database and opening the employee table to retrieve existing records
Set db = OpenDatabase(App.Path & "\employee.mdb")
Set rs = db.OpenRecordset("details", dbOpenTable)

If rs.RecordCount > 0 Then
    rs.MoveFirst
Else
    MsgBox "No employee details found." & vbCrLf & _
        "Please add some records.", vbInformation
End If
''creating the list of existing employees
Call LoadEmployees
End Sub

''this is the core function which is responsible for the entire validation process to be executed. this function takes
''the employee name as an arguement that you wish to check whether already exists in the database or not. the function
''checks for its first occurance. if found then it returns true otherwise returns false. "true" means the employee
''does already exist and "false" means not.
Public Function IsEmpExists(ByVal e_name As String) As Boolean
Dim rsd As Recordset    ''declarigng a recordset object specially for this operation
Dim cur_emp As String   ''this will hold current value returned by the recordset object while scanning through tha table

Set rsd = db.OpenRecordset("details", dbOpenTable)  'pointing the recordset to the table
If rsd.RecordCount > 0 Then     'checking if there is any record exists in the table. if its found any record then
                                'the recordcount should be greater than zero.
    rsd.MoveFirst   'pointing 'rsd' to the first record in the 'table. this actually notifies that a in-scanning process
                    'through the table is about to begin.
    While Not rsd.EOF()     'looping through the table until the end of file is reached
        cur_emp = rsd!empname   'parsing current cell value of 'empname' to the string var. 'cur_emp'
        If UCase(cur_emp) = UCase(e_name) Then      'now checking whether 'cur_emp' and the parameter passed does have
            IsEmpExists = True                      'same value or not. if yes then employee already exists. so returns
            Exit Function                           'true. once it becomes true there is no need of scanning again.so
        End If                                      'exists the function.
        rsd.MoveNext
    Wend
    IsEmpExists = False     'if no matching is found between 'cur_emp' and 'e_name' the function returns false,means there
                            'is no such employee exists with same name in the table.so you can proceed to add it into the
                            'database
Else    'there is no existing record found, so returns false
    IsEmpExists = False
End If
End Function

''this is just a sub-routine to call all existing employee records from the database and add them to the listbox. this
''becomes very useful in case of side-by-side viewing. you can easily see that the validation is going all ok.
Public Sub LoadEmployees()
Dim rsx As Recordset

Set rsx = db.OpenRecordset("details", dbOpenTable)
List1.Clear

If rsx.RecordCount > 0 Then
    rsx.MoveFirst
    While Not rsx.EOF()
        With List1
            .AddItem rsx!empname
            .ListIndex = .ListCount - 1
        End With
        rsx.MoveNext
    Wend
    Exit Sub
End If
List1.AddItem "-=No Existing Employees=-"
End Sub

know me if this helps you.

regards
Shouvik

what i want is there will be a msgbox that will tell theres already "MyName" on my table,

Don't really understand what you want?

If you already have accessed the data, you should already know that the MyName Field exists.

"MyNaMe" or "MYNAME" (have diff Uppercase and Lowercase charater)
is will still recognize it as a simple "MyName"

Microsoft doesn't care if you use Uppercase or Lower Case when referring to field names in a database. It just cares if the letters match.

Hank

im now trying the logic of your example sir shouvi, wish me luck
thx for the help

@hkDani

OPsss sori, i got confused , what i wanna say is about the data inside a field

umm ok here goes.

if theres a "Haw" already inside the field "Username"

then i wanna add "HAW" (all CAPS)

a validation msgbox will say that theres already a data with the same name.

if theres a "Haw" already inside the field "Username"

then i wanna add "HAW" (all CAPS)

Hah, hah. That's really funny:)

You have earned my first official Good Grief!

Hah, hah, hah.

I can't stop laughing!

:) sorry if u cnt get what iwanna tell u,
(cause im not that good at expressing in english :( )

currently converting what sir shouvi gave me into an ADO, (really wish me luch):'(

Save both values into 2 different variables.

Convert both to uppercase. Test to see if they're true. When in doubt. Use your immediate window: <Ctl +G>

This is false
----------------------------------------
Immediate
?"Hah"="HAH"
False


This is true
----------------------------------------
Immediate
?"HAH"="HAH"
True


--------------------
Immediate

?"GOOD GRIEF"="Good Grief"
False

?(ucase("GOOD GRIEF")=ucase("Good Grief"))
True

ic, why dnt' i think of that simple logic,

well now i know , :D
im starting to laugh at myself to,

ok back to converting

IVE SOLVED MY PROB THX to you to,

ive finished converting it to ADO

here what ive finished,

really thx esp to sir shouvi

Option Explicit


Dim rs As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim esql As String

''this is the core function which is responsible for the entire validation process to be executed. this function takes
''the employee name as an arguement that you wish to check whether already exists in the database or not. the function
''checks for its first occurance. if found then it returns true otherwise returns false. "true" means the employee
''does already exist and "false" means not.


Public Function IsEmpExists(ByVal e_name As String) As Boolean
Dim rsd As New ADODB.Recordset    ''declarigng a recordset object specially for this operation
Dim cur_emp As String   ''this will hold current value returned by the recordset object while scanning through tha table

Set rsd = New ADODB.Recordset  'pointing the recordset to the table
rsd.CursorType = adOpenStatic
rsd.CursorLocation = adUseClient
rsd.LockType = adLockPessimistic
rsd.Source = ("Select * From tblStudacct")
rsd.ActiveConnection = Conn
rsd.Open



If rsd.RecordCount > 0 Then     'checking if there is any record exists in the table. if its found any record then
                                'the recordcount should be greater than zero.
    rsd.MoveFirst   'pointing 'rsd' to the first record in the 'table. this actually notifies that a in-scanning process
                    'through the table is about to begin.
    While Not rsd.EOF()     'looping through the table until the end of file is reached
        cur_emp = rsd!Firstname   'parsing current cell value of 'empname' to the string var. 'cur_emp'
        If UCase(cur_emp) = UCase(e_name) Then      'now checking whether 'cur_emp' and the parameter passed does have
            IsEmpExists = True                      'same value or not. if yes then employee already exists. so returns
            Exit Function                           'true. once it becomes true there is no need of scanning again.so
        End If                                      'exists the function.
        rsd.MoveNext
    Wend
    IsEmpExists = False     'if no matching is found between 'cur_emp' and 'e_name' the function returns false,means there
                            'is no such employee exists with same name in the table.so you can proceed to add it into the
                            'database
Else    'there is no existing record found, so returns false
    IsEmpExists = False
End If
End Function



Private Sub Command1_Click()
If Trim(txtempname.Text) <> "" Then
    If IsEmpExists(Trim(txtempname.Text)) Then
        MsgBox "The employee " & Chr(34) & Trim(txtempname.Text) & Chr(34) & " already exists in the database." & vbCrLf & _
            "Please use a different name rather than this one.", vbInformation, "Duplicate Employee"
        txtempname.SelStart = 0
        txtempname.SelLength = Len(Trim(txtempname.Text))
        txtempname.SetFocus
    Else
        rs.MoveNext
        rs.AddNew
            rs!Firstname = Trim(txtempname.Text)
            rs!StudentNumber = 324324535  'just made this because my primary key is not auto number
            
            rs.Update
            Call LoadEmployees
            MsgBox "New employee created.", vbInformation, "Done"
            txtempname.Text = ""
            txtempname.SetFocus
            End If
Else
    MsgBox "The employee name should not be null.", vbCritical
    txtempname.SetFocus
End If
End Sub

Private Sub Form_Load()

''making connection to the database and opening the employee table to retrieve existing records
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

esql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MAINDB2.mdb" & ";Persist Security Info=False"

Conn.Open (esql), , , 0


rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Source = ("Select * From tblStudacct")
rs.ActiveConnection = Conn
rs.Open

If rs.RecordCount > 0 Then
rs.MoveFirst
Else
    MsgBox "No employee details found." & vbCrLf & _
        "'Please add some records.", vbInformation
End If
''creating the list of existing employees
Call LoadEmployees
End Sub


Public Sub LoadEmployees()
Dim rsx As New Recordset

Set rsx = New ADODB.Recordset
rsx.CursorType = adOpenStatic
rsx.CursorLocation = adUseClient
rsx.LockType = adLockPessimistic
rsx.Source = ("Select * From tblStudacct")
rsx.ActiveConnection = Conn
rsx.Open


List1.Clear

If rsx.RecordCount > 0 Then
    rsx.MoveFirst
    While Not rsx.EOF()
        With List1
            .AddItem rsx!Firstname
            .ListIndex = .ListCount - 1
        End With
        rsx.MoveNext
    Wend
    Exit Sub
End If
List1.AddItem "-=No Existing Employees=-"
End Sub

paste it on a blank form soo you can see that comments,

:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.