Member Avatar for Naveed_786

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

  • 4 Contributors
  • 9 Replies
  • 269 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

Good thread — useful quick fixes from , and , and a working loop from . The next step is a small cleanup and a more robust pattern: validate the control value, run a focused existence check at the database level (not a full table scan), avoid validating on every KeyPress, and enforce uniqueness in the database so duplicate inserts can never occur by accident.

Practical pattern (summary):

  • Reject empty input with a string check (Trim/LEN) rather than comparing to Null.
  • Use a single-row existence query (COUNT(*) or SELECT 1 ... TOP 1) with a parameter to avoid SQL injection and to be fast.
  • Validate on the control’s AfterUpdate/LostFocus or in the form’s BeforeUpdate instead of KeyPress so the check only runs when editing finishes.
  • For multi-user safety, add a UNIQUE index or primary key on StudentID and handle duplicate-key errors on INSERT rather than relying only on a prior SELECT.

Example (ADODB, parameterized COUNT — different approach from the snippets already in the thread):

' ADODB: parameterized existence check
Dim cmd As ADODB.Command
Dim rs  As ADODB.Recordset

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "SELECT COUNT(*) AS Cnt FROM Student WHERE StudentID = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("pID", adVarChar, adParamInput, 50, Trim$(txtStudentID.Text))

Set rs = cmd.Execute
If rs.Fields("Cnt").Value > 0 Then MsgBox "Record already exists.", vbExclamation

rs.Close: Set rs = Nothing
Set cmd = Nothing

Troubleshooting notes: RecordCount and BOF/EOF checks work but depend on cursor type — they can be unreliable with forward-only cursors. Avoid On Error Resume Next for flow control. Always close recordsets/connections and set objects to Nothing. Enforcing the uniqueness constraint at the database level is the simplest, most reliable safeguard.

Recommended Answers

All 9 Replies

Member Avatar for 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.

Member Avatar for 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
Edited by PoisonedHeart because: n/a
Member Avatar for 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

.:)

Member Avatar for 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

Member Avatar for devLady

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

Member Avatar for 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
Member Avatar for 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
Member Avatar for 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.

Member Avatar for 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.:)

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.