Record Already Exists

[Naveed_786]

Can any body tell me how can i check that record already exists?

[AndreRet]

Use the same select statement type -

SELECT * FROM student WHERE StudentName LIKE " & "'" & Nav & "%'"

This will return all the names that has Nav in its text.

[PoisonedHeart]

You can use this code to check wether a query result returns a macthing record or not

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'NOTE: you can use AndreRet's given example above this post...

rs.Open "YOUR SELECT STATEMENT HERE", cn, adOpenKeyset, adLockOptimistc, adCmdText
If rs.BOF and rs.EOF = True then 'if there are no results from the query, then do this (this means that there are no records)

Else 'when the query have return a number of matching records...do this

End if

[AndreRet]

rs.Open "YOUR SELECT STATEMENT HERE", cn, adOpenKeyset, adLockOptimistc, adCmdText

As in -

rs.Open "SELECT * FROM student WHERE StudentName LIKE " & "'" & Nav & "%'", cn, adOpenStatic, adLockOptimistic

.:)

[devLady]

if you want to know if the record is already exist you need to query from your database if that record is already been there example.

RecordSet.Open "Select * from studentTable Where StudenID = '" & txtStudentID.Text & "'", cn, adOpenStatic, adLockOptimistic

and then check if recordcount of recordset is more than 0 example
if recordset.recordcount > 0 then
msgbox "Record already exit.",vbexclamation
exit sub
end if

Hope this simple tutorial really help your prob. :D

[devLady]

ooopss sorry, it must be
if recordset.recordcount > 0 then
msgbox "Record already exist.",vbexclamation
exit sub
end if

[Naveed_786]

I am using this code on keypress event of txtstudentid please help me how would i check here that record already exists. when i use this it populates message when i click on the textbox

Private Sub txtstudentid_KeyPress(KeyAscii As Integer)
if rs.recordcount > 0 then
msgbox "Record already exist.",vbexclamation
exit sub
If KeyAscii = 13 Then
txtstudentname.SetFocus
If txtstudentid.Text = nul Then
MsgBox "Field Must Be Entered", vbOKOnly + vbExclamation, "EXCELLENT ACADMY"
txtstudentid.SetFocus
End If
End If
End if
End Sub

[AndreRet]

'Declare the ADODB objects
Dim cnMyCon As ADODB.Connection
Dim rsMyRec As ADODB.RecordSet

Set a reference to the ADODB that will be used in this context
Set cnMyCon = New ADODB.Connection
Set rsMyRec = New ADODB.RecordSet

'Do your access database connection here.
cnMyCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= WhereYourPathOfDatabaseIS\YourDatabaseName.MDB;Persist Security Info=False"

If txtstudentid.Text = nul Then
   MsgBox "Field Must Be Entered", vbOKOnly + vbExclamation, "EXCELLENT ACADMY"
   txtstudentid.SetFocus

   Exit Sub
      Else    
   rsMyRec.Open "SELECT * FROM YourTableName WHERE TheStudentIdNumber = " & "'" & txtstudentid.Text & "'"

   If rsMyRec.BOF = True Or rsMyRec.EOF = True Then
      'Add the new record here because it does not exist
         Else
      msgbox "Record already exist.",vbexclamation
      
      cnMyCon.Close
      rsMyRec.Close
      exit sub
   End If
End IF

[Naveed_786]

Hi dear,

I have resolved the problem by using this code

On Error Resume Next
If KeyAscii = 13 Then
txtstudentname.SetFocus
rs.MoveFirst
While rs.EOF = False
If Val(txtstudentid.Text) = rs.Fields!StudentID Then
MsgBox "RECORD ALREADY EXISTS!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
txtstudentid.SetFocus
Exit Sub
Else
If txtstudentid = nul Then
MsgBox "Please enter StudentID!", vbOKOnly + vbInformation, "EXCELLENT ACADMY"
txtstudentid.SetFocus
Exit Sub
End If
End If
rs.MoveNext
Wend
End If

Thanks alot for your help.

[AndreRet]

It was only a pleasure.:)

I see that you had your txtStudentId set to your datacontrol. Nicely done to compare from there.

Please mark this thread as solved for us, thanks.

Happy coding.:)