Please Help VB 6 ADODB, after adding new record, duplicate values exists in listbox

buboy · #1 (**permalink**) 12-14-11, 02:00

Code:

Private Sub cmdadd_Click()
rs1.Open "Select * from AccountTypes", con, adOpenDynamic, adLockOptimistic
rs.Open "Select * from Accounts", con, adOpenDynamic, adLockOptimistic
    rs1.Find "AccountType = '" & Combo1.Text & "'"
    If cmdadd.Caption = "&Add" Then
    txtAccount.Text = ""
    cmdadd.Caption = "&Save"
    txtAccount.SetFocus
    Else
    rs.AddNew
    rs.Fields("AccountTypeID") = rs1.Fields("AccountTypeID")
    rs.Fields("Account") = txtAccount.Text
    rs.Update
    txtAccount.Text = rs.Fields("Account")
    List1.Clear
    Do While rs.EOF <> True
    List1.AddItem rs.Fields("Account")
    rs.MoveNext
    Loop
    cmdadd.Caption = "&Add"
    End If

Set rs = Nothing
Set rs1 = Nothing

loquin · #2 (**permalink**) 01-09-12, 18:17

I would first create a new button, used to save the code, and move the 'save' code to that button. Have the button's visible state initially set to false.

During the 'Add' button's code execution, make the Add button invisible, and the save button visible. And, in the save button code, make IT invisible, and the add button visible. Finally, place the new button so that it's behind the existing 'add' button.

That way, you can keep the logic separate for the two separate tasks.

Next. If, instead of the recordset FIND method, you instead use the recordset Filter property, it can make your code a bit cleaner, IMO.

Code:

rs1.Filter =  "AccountType = '" & Combo1.Text & "'"
' Now, the recordset only 'contains' records which match your criteria.
' Then, When done, 
rs1.Filter = adFilterNone
'restores the recordset.

Try issuing a listbox.refresh immediately after the listbox.clear.

Internet Services

Web Development

Digital Marketing

Consumer Tech