hello please help me on this project form3 ..everytime i always add data it always already exist please help me to solve my problem..heres my code

Option Explicit
Public con As ADODB.Connection
Public cmd As ADODB.Command
Public mch_rs As ADODB.Recordset, index As Integer, m_mchno As String
Public sql As String


Public Sub clear()

Me.txtmchname = ""
Me.txtmchno = ""
Me.txtqtyh = ""
Me.txtuprice = ""

cmdadd.Item(index).BackColor = vbWhite

End Sub


Private Sub cbomeasure_KeyPress(KeyAscii As Integer)

If KeyAscii = 13 Then
  cbomeasure.SetFocus
End If



End Sub

Public Sub cmdadd_Click(index As Integer)




      If cmdadd.Item(index).BackColor = vbWhite Then
         cmdadd.Item(index).BackColor = vbRed
      End If
   
  
If error_check() = 1 Then
  cmdadd.Item(index).BackColor = vbWhite
 Exit Sub
 End If

 

  
  sql = "select * from merchandise_table where mchno = '" & m_mchno & "'"

Set mch_rs = con.Execute(sql)
  

 If mch_rs.BOF = True And mch_rs.EOF = True Then
       
      

        check_table
        Exit Sub
         
Else
  
        MsgBox "already exist"
        cmdadd.Item(index).BackColor = vbWhite
        clear
        cbomeasure.clear
       End If


With cbomeasure

.AddItem "pcs"
.AddItem "ream"

End With




End Sub


Private Sub cmdexit_Click()
Unload Me
End Sub

Private Sub Form_Activate()
Me.txtmchno.SetFocus
End Sub

Private Sub Form_Load()

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set mch_rs = New ADODB.Recordset


con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\project.mdb"
con.Open

With cbomeasure

.AddItem "pcs"
.AddItem "ream"

End With
End Sub



Public Sub check_table()

With cmd
 .ActiveConnection = con
 .CommandText = "merchandise_table"
 .CommandType = adCmdTable
 
 End With
 
 
 With mch_rs
 
 .CursorLocation = adUseClient
 .CursorType = adOpenDynamic
 .LockType = adLockPessimistic
 .Open cmd

End With
   
  With mch_rs
 .AddNew
 !mchno = m_mchno
 !mch_name = Trim(txtmchname.Text)
 !mch_umsr = Trim(cbomeasure.Text)
 !mch_qyth = Val(txtqtyh.Text)
 !mch_uprice = CDbl(txtuprice.Text)
 !mch_rstatus = "1"
 .Update
 .Close
End With
  MsgBox "record has been added", vbInformation
  

End Sub




Public Sub displayrec()


txtmchname.Text = mch_rs!mch_name
txtuprice.Text = mch_rs!mch_uprice
txtqtyh.Text = mch_rs!mch_qyth
cbomeasure.Text = mch_rs!mch_umsr


End Sub

Edited 6 Years Ago by WaltP: Added CODE tags again

If Rs.BOF = True And Rs.EOF = True Then
  'do something and exit sub
Else
  MsgBox "Exists!"
End If

Of course you are always going to get the msgbox that tells you your information exists. A recordset cannot be both BOF AND EOF at the same time even if there are no records...

You should do something like...

If Rs.RecordCount <> 0 And Rs.BOF = False And Rs.EOF = False Then
  'Record Exists
Else
  'No Record
End If

Good Luck

Hi,

Just check for "EOF", If it EOF is true, means record does not exist and You can add new data..

If RS.EOF Then
   ' Code To Add New Data
Else
   'MsgBox "Record Exits..."
End If

Regards
Veena

Hi,
I agree with all Posts above but in addition to them I suggest you to make Public Sub into Private Sub if you don't use that Procedures/Functions on other forms.

Thanks

This question has already been answered. Start a new discussion instead.