Can any body tell me how can i check that record already exists?
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.
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
rs.Open "YOUR SELECT STATEMENT HERE", cn, adOpenKeyset, adLockOptimistc, adCmdText
As in -
rs.Open "SELECT * FROM student WHERE StudentName LIKE " & "'" & Nav & "%'", cn, adOpenStatic, adLockOptimistic
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
Hope this simple tutorial really help your prob. :D
ooopss sorry, it must be
if recordset.recordcount > 0 then
msgbox "Record already exist.",vbexclamation
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
'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
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.
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.