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

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

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

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

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

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?)

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

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
[COLOR=Black][B]List1.AddItem Rs("Text1.Text")[/B][/COLOR] ([SIZE=4][U][B]After debugging it is giving error over here[/B][/U][/SIZE])
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

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

In this code, where i am doing wrong? Pl help!

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

maybe
List1.AddItem Rs("Field1")

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

I have tried that but there is a same problem. Pl help?

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

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
............

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

Getting same problem pl. help

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

Getting same problem pl. help

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

send your project in a zip file with the database you have in order to look at it.

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

I have attached the file in zip

 

Re: Need help in adding data in listbox; data entered in list box saves data in Acce

change this and tell me again what is your problem

Code:

..........
Private Sub cmdAdd_Click()
[COLOR=Red]If Trim(Text1.Text) <> "" And Trim(Text2.Text) <> "" And Trim(Text3.Text) <> "" And Trim(Text4.Text) <> "" And Trim(Text5.Text) <> "" And Trim(Text6.Text) <> "" Then[/COLOR]
With Rs
...............