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.
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 Subknow 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 Subpaste it on a blank form soo you can see that comments,
:)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.