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)
0
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
0
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
0
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?)
0
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
0
viv345's Avatar, Join Date: Feb 2010
Contributor
In this code, where i am doing wrong? Pl help!
0
virxen's Avatar, Join Date: Nov 2009
Pro contributor
maybe
List1.AddItem
Rs("Field1")
viv345 like this
0
viv345's Avatar, Join Date: Feb 2010
Contributor
I have tried that but there is a same problem. Pl help?
0
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
0
viv345's Avatar, Join Date: Feb 2010
Contributor
Getting same problem pl. help