Member Avatar for siu17

hi i have been able to update a database using the following code
Option Explicit
Dim conn As ADODB.Connection, rec1 As ADODB.Recordset
Dim esql1 As String

Private Sub Command1_Click()
On Error GoTo 2
If Text1 = "" Then
Command1.Visible = False
Command2.Visible = True
Exit Sub
End If
rec1.AddNew
rec1.Fields(1) = Text1
rec1.Update
GetText
Exit Sub
2
MsgBox ("Duplicate Value")
End Sub

Private Sub Form_Load()
Set conn = New ADODB.Connection
Set rec1 = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\shnd\Project.mdb;Persist Security Info=False"
conn.Open
esql1 = "select * from Header"
rec1.Open (esql1), conn, adOpenDynamic, adLockOptimistic
GetText
End Sub

Private Sub GetText()
Do While Not rec1.EOF
List1.AddItem rec1.Fields(1)
rec1.MoveNext
Loop
End Sub


The only problem i am havin is that after clicking the update command the listbox displays the new data twice.. it has to do it once

  • 3 Contributors
  • 3 Replies
  • 103 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by vb5prgrmr

Dani AI

Generated

Two likely causes are already hinted at by replies: either the database actually has two identical rows (), or the refresh logic is repopulating the list incorrectly (). Short checklist, then practical fixes.

First, verify the data in the Access table. Open Project.mdb and run a quick grouping query to find duplicates (replace HeaderField with your real column name):

SELECT HeaderField, Count(*) AS DupCount
FROM Header
GROUP BY HeaderField
HAVING Count(*) > 1;

If that returns rows, the duplicate is in the DB. Add a unique index or primary key on the column you want unique, or check for existence before inserting (run a SELECT COUNT(...) WHERE ... and skip the insert when > 0).

If the table has no duplicates, fix the refresh logic. The root cause is that the recordset cursor position and the listbox contents aren't being managed together. Use a dedicated refresh routine that clears the list, moves the recordset to the first row, and iterates safely:

Private Sub RefreshList()
    List1.Clear
    If rec1.BOF And rec1.EOF Then Exit Sub
    rec1.MoveFirst
    Do Until rec1.EOF
        List1.AddItem rec1.Fields("YourFieldName").Value
        rec1.MoveNext
    Loop
End Sub

After updating the recordset you can either requery or close/open it before calling RefreshList so the recordset reflects the latest rows:

rec1.Requery      'or rec1.Close : rec1.Open esql1, conn, ...
RefreshList

Other quick checks: use field names rather than numeric indexes (Fields("Name") avoids off-by-one), guard against empty recordsets (BOF/EOF), and confirm the click handler is firing only once. If duplicates persist, inspect the table with Access to see whether the insert code is being executed twice or a trigger/append routine is adding the row.

Recommended Answers

All 3 Replies

Member Avatar for exzibit23

Private Sub GetText()
List1.Clear
Do While Not rec1.EOF
List1.AddItem rec1.Fields(1)
rec1.MoveNext
Loop
End Sub

try this..

Member Avatar for siu17

no it didn't work it only clears the listbox and again displays the new entry twice

Member Avatar for vb5prgrmr

Then that means you have two records with the same value that you are displaying.


Good Luck

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.