(--)help me on this project

[jemz]

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

[debasisdas]

..everytime i always add data it always already exist ....

What exactly is the problem ?

[vb5prgrmr]

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

[QVeen72]

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

[kinwang2009]

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