Record Already Exists

[Naveed_786]

Dear all,
Please help me i want to check record already exists in database please modify this code please help

Private Sub txtempno_KeyPress(KeyAscii As Integer)
On Error Resume Next
If KeyAscii = 13 Then
btncalculate.SetFocus
rs2.MoveFirst
While rs2.EOF = False
If Val(txtempno.Text) = rs2.Fields!EmpNo Then
txtsub.Text = rs2.Fields!subjects
txtempname.Text = rs2.Fields!TeacherName
Else
If txtempno.Text = nul Then
MsgBox "Please enter StudentID!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
txtempno.SetFocus
Exit Sub
End If
End If
rs2.MoveNext
Wend
End If
End Sub

[smsmcp]

Have a try of this. I think its right. Others can comfirm

Private Sub txtempno_KeyPress(KeyAscii As Integer)
Dim EmpNo as String
On Error Resume Next
EmpNo = txtempno.text
If KeyAscii = 13 Then
   btncalculate.SetFocus
   rs2.MoveFirst
   While rs2.EOF = False
      If EmpNo = rs2.Fields![EmpNo] Then
         txtsub.Text = rs2.Fields![subjects]
         txtempname.Text = rs2.Fields![TeacherName]
      Else
         txtempno.Text = Null
         MsgBox "Please enter StudentID!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
         txtempno.SetFocus
         Exit Sub
      End If
      rs2.MoveNext
   Wend
End If
End Sub

[Naveed_786]

How would i check record already exists??

[smsmcp]

Added ucase as not sure if your data is in upper or lower case

While rs2.EOF = False
      '-------this is the check I.E EmpNo = rs2.Fields![EmpNo]
      If ucase(EmpNo) = ucase(rs2.Fields![EmpNo]) Then

[smsmcp]

You code does not add anything into the database. It just checks if the EmpNo is in your database in the EmpNo field.

If ucase(EmpNo) = ucase(rs2.Fields![EmpNo]) Then

If it is, it fills the txtsub text box and the txtempname text box with the data.

txtsub.Text = rs2.Fields![subjects]
txtempname.Text = rs2.Fields![TeacherName]

[abelingaw]

Try:

If txtsub.Text = rs2.Fields![subjects] then
       MsgBox "Please enter StudentID!", vbExclamation, "Error"
       txtempno.Text = vbNullString
       txtempno.SetFocus
 End if

Haven't try this but just think of the concept.

If the textbox value is equal to any record on the database (or what column that is)

then the message appears.

[smsmcp]

As an example here a bit of code i used years ago to check if a user can access a program. Like a logon check
It compares the values in the text box with the ones in the MS Access database. If everthing is good then your in :)
username and userpass are global as i use them a few times for diffrent checks

Public Sub UserCheck()
    Dim DBuser As String
    Dim DBPass As String
    strSQL = "SELECT * FROM Users "
    If Con1.State = 0 Then
        Con1.Open
    End If 
    rs1.Open strSQL, Con1, adOpenStatic, adLockBatchOptimistic
    Username = frmLogin.txtUserName.Text
    UserPass = frmLogin.txtPassword.Text
    If rs1.EOF Then
        MsgBox "Password or User Name not found", vbInformation, "End OF FIle"
        rs1.MoveFirst
        rs1.Close
        Exit Sub
    Else 
        Do
        DBuser = rs1![Lastname]
        DBPass = rs1![password]
            If Username = DBuser And UserPass = DBPass Then 'this line does the check
                ok = True
                Firstname = rs1![Firstname]
                UserLevel = rs1![UserLevel]
                Unload frmLogin
                frmMain.Show
                rs1.Close
                Con1.Close
                Exit Sub
            Else
                rs1.MoveNext
                If rs1.EOF Then
                    rs1.MoveFirst
                    rs1.Close
                    Exit Do
                End If
            End If
        Loop Until rs1.EOF
        MsgBox "Invalid Password, try again! Logon atempt No" & failureCount + 1 & " of 3", , "Login"
        frmLogin.txtPassword.SetFocus
        frmLogin.txtPassword.SelStart = 0
        frmLogin.txtPassword.SelLength = Len(frmLogin.txtPassword.Text)
        failureCount = failureCount + 1
        
        If failureCount = maxFailureCount Then
            MsgBox "Max Logon attemts reached. Closing program", vbCritical, "Error"
            End
        End If
    End If
End Sub

[abelingaw]

Can you please clarify if your code is for adding data to database.? (I'm such a noob)

Just like the guy above me said, your code doesn't add data to your database.

Your code really is confusing (alignment thingy), please use tabs.

[Naveed_786]

I want to check if record does not in database it should populate message that user doesn't exists in database. i want to adjust do while loop or some thing else to perform this operation please help.

Private Sub txtempno_KeyPress(KeyAscii As Integer)
      On Error Resume Next
      If KeyAscii = 13 Then
      btncalculate.SetFocus
      rs2.MoveFirst
      While rs2.EOF = False
      If Val(txtempno.Text) = rs2.Fields!EmpNo Then
      txtsub.Text = rs2.Fields!subjects
      txtempname.Text = rs2.Fields!TeacherName
      Else
      If txtempno.Text = nul Then
      MsgBox "Please enter StudentID!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
      txtempno.SetFocus
      Exit Sub
      End If
      End If
      rs2.MoveNext
      Wend
      End If
      End Sub

[AndreRet]

Although the samples given is working towards the solution. it is way too extended. The simplest way is as follow -

rs.Open "SELECT * FROM MyTable WHERE MyFeldName = '" & Text1.Text & "'", cn, adOpenStatic, adLockOptimistic

If rs.BOF = True Or rs.EOF = True Then
'All your code here, no record exists.....
Else
'Record exist, code here to handle that
End IF

Short and sweet....:)

[Naveed_786]

Now my code looks like this but it populates message No such record exists again and again please check this code for bugs

Private Sub txtempno_KeyPress(KeyAscii As Integer)
On Error Resume Next
If KeyAscii = 13 Then
btncalculate.SetFocus
rs2.MoveFirst
While rs2.EOF = False
If Val(txtempno.Text) = rs2.Fields!EmpNo Then
txtsub.Text = rs2.Fields!subjects
txtempname.Text = rs2.Fields!TeacherName
Else
If txtempno.Text = nul Then
MsgBox "Please enter StudentID!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
txtempno.SetFocus
Exit Sub
End If
End If
rs2.MoveNext
Wend
If rs2.EOF = True Or rs2.BOF = True Then
MsgBox "No such record exists!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
End If
End If
End Sub

[jhai_salvador]

remove this line so you can check if its bug free...

On Error Resume Next

Also add this line on your loop to prevent your application from hanging / lagging..

DoEvents

[AndreRet]

@Naveed, have you tried the solution I posted yesterday? This will search for a record, if it exists, do what you want it to do or if it does not exist, let you know...

rs.Open "SELECT * FROM MyTable WHERE MyFeldName = '" & Text1.Text & "'", cn, adOpenStatic, adLockOptimistic
 
If rs.BOF = True Or rs.EOF = True Then
'All your code here, no record exists.....
Else
'Record exist, code here to handle that
End IF

[smsmcp]

Edited your code again. The way you had the last IF allways triggered even after your while loop had completed. I have put it inside your while so that it will trigger after your rs2.MoveNext statment.

Private Sub txtempno_KeyPress(KeyAscii As Integer)
On Error Resume Next
If KeyAscii = 13 Then
    btncalculate.SetFocus
    rs2.MoveFirst
    While rs2.EOF = False
        If Val(txtempno.Text) = rs2.Fields!EmpNo Then
            txtsub.Text = rs2.Fields!subjects
            txtempname.Text = rs2.Fields!TeacherName
        ElseIf txtempno.Text = nul Then
            MsgBox "Please enter StudentID!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
            txtempno.SetFocus
            Exit Sub
        End If
        rs2.MoveNext
        If rs2.EOF = True Or rs2.BOF = True Then
            MsgBox "No such record exists!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
        End If
    Wend
End Sub

[Naveed_786]

As per your instruction i have changed the code but it populates no such record exists please help

On Error Resume Next
Set cn = New adodb.Connection
Set rst = New adodb.Recordset
cn.Open "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=D:\EA\DB\EA.mdb;"
rst.CursorLocation = adUseClient
      rst.Open "SELECT * FROM Employee WHERE Empno = '" & txtempno.Text & "'", cn, adOpenStatic, adLockOptimistic
      If rst.BOF = True Or rst.EOF = True Then
      MsgBox "No Such Recrods exsists!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
      txtempno.SetFocus
      Exit Sub
      Else
      If Val(txtempno.Text) = rst.Fields!EmpNo Then
      txtsub.Text = rst.Fields!subjects
      txtempname.Text = rst.Fields!TeacherName
      End If
      rst.MoveNext
      End If

[AndreRet]

Is there a record that exists with the emp number entered?

Also change this part to ---

If Val(txtempno.Text) = rst.Fields!EmpNo Then
txtsub.Text = rst.Fields!subjects
txtempname.Text = rst.Fields!TeacherName
End If
rst.MoveNext

1) You don't have to call txtempno = rst because you have already done so in your sql statement where it searched for the specific emp number.
2) rst.Movenext might be your problem here because you have searched for a record and then moves to the next, remove the code...

Else
      txtsub.Text = rst.Fields!subjects
      txtempname.Text = rst.Fields!TeacherName

[Naveed_786]

So i have removed rst.movenext and If Val(txtempno.Text) = rst.Fields!EmpNo Then but the result is same when i try to enter empno as i press 5 it populates message then i terminate the message then i press 0 then it again populate message i don't why it is so?

[AndreRet]

That is because no record exist. Is there ANY records in the table?

[AndreRet]

Also, under which event do you have the code? Is it under a command button or text box, click event or change event?

Please answer question above as well.:)

[Naveed_786]

Ok i have resolved the problem now its fine but when we didn't have record in database it gives me message no record exists but when i try to exit the form it populates message again and again what should i do

[AndreRet]

Under what event are you running the code?

Show me your code, all under the event and I can solve it for you...:)

[Naveed_786]

I have strugle and find out the way now it is fine i done it in this way

Private Sub txtempno_Validate(Cancel As Boolean)
On Error Resume Next
Set cn = New adodb.Connection
Set rst = New adodb.Recordset
cn.Open "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=D:\EA\DB\EA.mdb;"
rst.CursorLocation = adUseClient
      rst.Open "SELECT * FROM Employee WHERE Empno = '" & txtempno.Text & "'", cn, adOpenStatic, adLockOptimistic
      If rst.BOF = True Or rst.EOF = True Then
      MsgBox "No Such Recrods exsists!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
      txtempno.SetFocus
      Exit Sub
      Else
      txtsub.Text = rst.Fields!subjects
      txtempname.Text = rst.Fields!TeacherName
      End If
End Sub

any way thanks for your precious time and grate help.

[AndreRet]

It was a pleasure. Happy coding.:)

Merry Christmas and a happy new year.