Need help in adding data in listbox; data entered in list box saves data in Access

viv345's Avatar, Join Date: Feb 2010
Contributor
Tried making something new i.e "adding data in listbox; data entered in list box saves data in Access and for deleting the data from Access instead of inserting delete button/command delete it by double clicking items added in list box"

Project attached
Attached Files
File Type: zip 2.zip (12.8 KB, 10 views)
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Please do not attach your complete projects but try to explain what is working and what not.
viv345 like this
viv345's Avatar, Join Date: Feb 2010
Contributor
Sorry it is not a full project it is a trial to add items in 2 list boxes so that the data remains constant and it can be seen after quitting also. and for deleting both from access and through my project.
Thanks
viv345's Avatar, Join Date: Feb 2010
Contributor
In this project I am getting problem i.e: after adding data list box remains empty and fields in access also remains empty. Is their any problem in coding. (Where I am doing Wrong?)
viv345's Avatar, Join Date: Feb 2010
Contributor
Written the coding as follow:

Code:
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset

Private Sub cmdAdd_Click()
If Text1.Text Or Text2.Text Or Text3.Text > 0 Then
With Rs
.AddNew
Field1 = Text1.Text
Field2 = Text2.Text
Field3 = Text3.Text
.Update
End With
    ' Add the string that is currently in the textBoxes to the List Boxes
    List1.AddItem Text1.Text
    List2.AddItem Text2.Text
    List3.AddItem Text3.Text
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
End If
End Sub

Private Sub List1_DblClick()
Dim Field1 As String 'name of the user
'If no user is selected or listbox is empty then
If List1.SelCount < 1 Or List1.ListCount < 1 Then MsgBox "Select the users to delete": Exit Sub

Field1 = List1.List(List1.ListIndex) ' store the name of the selected user
Rs.MoveFirst ' Move to the first record
'Loop thru the records
Do While Not Rs.EOF
    If Rs("Field1") = Text1.Text Then ' If both matches
        Rs.Delete ' then delete
        List1.RemoveItem List1.ListIndex ' and remove it from list box
        Exit Sub ' and exit the sub
    End If
    Rs.MoveNext
Loop

MsgBox "Empty database"

End Sub

Private Sub Form_Load()
Set Conn = New ADODB.Connection
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
Conn.ConnectionString = "Data Source=" & App.Path & "\Db1.mdb"
Conn.Open
Set Rs = New ADODB.Recordset
Rs.Open "SELECT * from table1", Conn, adOpenStatic, adLockOptimistic
Do While Not Rs.EOF = True
List1.AddItem Rs("Text1.Text") (After debugging it is giving error over here)
List2.AddItem Rs("Text2.Text")
List3.AddItem Rs("Text3.Text")
Rs.MoveNext ' Move next
Loop
End Sub

Private Sub Form_Unload(Cancel As Integer)

Rs.Close
Conn.Close
End Sub

Private Sub cmdQuit_Click()
Unload Me
End Sub

Private Sub Text2_Click()
Form1.Text2.Text = Val(Form1.Text1.Text) * 1#
End Sub

Private Sub Text3_Click()
Form1.Text3.Text = Val(Form1.Text2.Text)
End Sub

Private Sub List2_DblClick()
Dim Field2 As String 'name of the user

If List2.SelCount < 1 Or List2.ListCount < 1 Then MsgBox "Select the users to delete": Exit Sub

Field2 = List2.List(List2.ListIndex) ' store the name of the selected user
Rs.MoveFirst ' Move to the first record
'Loop thru the records
Do While Not Rs.EOF
    If Rs("Field2") = Text2.Text Then ' If both matches
        Rs.Delete ' then delete
        List2.RemoveItem List2.ListIndex ' and remove it from list box
        Exit Sub ' and exit the sub
    End If
    Rs.MoveNext
Loop

'Just in case but it will never happen
MsgBox "Empty database"

End Sub

Private Sub List3_DblClick()
Dim Field3 As String 'name of the user
'If no user is selected or listbox is empty then
If List3.SelCount < 1 Or List3.ListCount < 1 Then MsgBox "Select the users to delete": Exit Sub

Field3 = List3.List(List3.ListIndex) ' store the name of the selected user
Rs.MoveFirst ' Move to the first record
'Loop thru the records
Do While Not Rs.EOF
    If Rs("Field3") = Text3.Text Then ' If both matches
        Rs.Delete ' then delete
        List3.RemoveItem List3.ListIndex ' and remove it from list box
        Exit Sub ' and exit the sub
    End If
    Rs.MoveNext
Loop

MsgBox "Empty database"

End Sub

Last edited by shabbir; 2Apr2010 at 19:16.. Reason: Code blocks
viv345's Avatar, Join Date: Feb 2010
Contributor
In this code, where i am doing wrong? Pl help!
virxen's Avatar, Join Date: Nov 2009
Pro contributor
maybe
List1.AddItem
Rs("Field1")
viv345 like this
viv345's Avatar, Join Date: Feb 2010
Contributor
I have tried that but there is a same problem. Pl help?
virxen's Avatar, Join Date: Nov 2009
Pro contributor
the first project you send had 2 listboxes,
but when you posted your code later you mentioned 3 listboxes why?

the correct code is

Code:
............
List1.AddItem Rs(1).value
List2.AddItem Rs(2).value
............

Last edited by virxen; 5Apr2010 at 20:49..
viv345 like this
viv345's Avatar, Join Date: Feb 2010
Contributor
Getting same problem pl. help