Member Avatar for 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
Edited by WaltP because: Added CODE tags again
  • 5 Contributors
  • 4 Replies
  • 102 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by kinwang2009

Dani AI

Generated

Two quick, concrete reasons the form will always show "already exist" in the posted code:

  • The search value used in the WHERE clause never gets assigned. The public string m_mchno is never set before the SELECT, so the SQL is built with the wrong value. Assign and sanitize the textbox value first (for example, m_mchno = Trim(Me.txtmchno.Text) and m_mchno = Replace(m_mchno, "'", "''")) and be careful to quote it only if the database field is text.
  • The Recordset checks need to match how the Recordset was opened. Contrary to 's statement, an empty ADO Recordset has both BOF and EOF True. 's advice to check EOF is workable, but RecordCount is unreliable unless the Recordset uses a client-side cursor. con.Execute returns a forward-only recordset (RecordCount may be -1), so prefer checking EOF or open the RS with CursorLocation = adUseClient and an appropriate CursorType if RecordCount is required.

Practical fixes and hardening:

  • Set m_mchno from txtmchno before building SQL and escape single quotes.
  • Use a deterministic existence check such as SELECT COUNT(*) AS C FROM merchandise_table WHERE mchno = '<value>' and test C>0, or open a client-side RS and check EOF/RecordCount reliably.
  • Do not repopulate cbomeasure inside the add routine (it is already populated in Form_Load); repeated .AddItem calls create duplicates.
  • Enforce uniqueness at the database level (unique index or primary key on mchno) and handle the duplicate-key error (Access error 3022) rather than relying only on application checks.

Quick debugging checklist:

  • Debug.Print the final SQL and the content of m_mchno before Execute.
  • After Execute, Debug.Print mch_rs.BOF, mch_rs.EOF, and mch_rs.RecordCount.
  • Test the same SELECT directly in Access to confirm the DB returns the expected rows.

As noted, make procedures Private if they are only used by that form to reduce accidental cross-form calls.

Recommended Answers

All 4 Replies

Member Avatar for debasisdas

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

What exactly is the problem ?

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

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

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

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.