Lstbox code not working

I have vba code set up to add information selected in a listbox to a table. I only need one field from the listbox added. It is in the 5th column.

I was having trouble with the code only adding blank records to the table. I added debugging code and now the code does nothing.

When I run it, I do get this message in the Immediate Window, "0         Null"

Here is the code:

Private Sub btnAddSelected_Click()

Dim frm As Form
Dim ctl As Control
Dim intCounter As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set frm = Forms("frm_Act_Enter")
Set ctl = frm![lstPrevRpts]

Set db = CurrentDb
Set rst = db.OpenRecordset("Act_SubTo_Date")


If ctl.ItemsSelected.Count > Null Then
    For intCounter = Null To ctl.ItemsSelected.Count - 1
        rst.AddNew
          Debug.Print intCounter, ctl.Column(5, intCounter)
            rst("ActID") = ctl.Column(5, intCounter)

        rst.Update
    Next intCounter
End If

rst.Close
Set rst = Nothing



End Sub

Open in new window

MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
If what you want is in the 5th column then try this...
ctl.Column(4, intCounter)
0
MeginAuthor Commented:
Still nothing.
0
Anthony BerenguelCommented:
Hm. Everything looks fine to me. Can you provide a test copy of the database for us to play with?

You're sure a selection is being made when you click the button to add the record?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
Anything compared to Null is Null so your If isn't working.  The following loop will get everything that was selected.  If you really want to know how many, if any were selected, you can look at the count property but your code as I understand it doesn't need to check the count.

Dim i As Variant

    For Each i In ctl.ItemsSelected
        rst.AddNew
          Debug.Print intCounter, ctl.Column(5, i)
            rst("ActID") = ctl.Column(5, i)

        rst.Update

    Next i

Open in new window


Also, you have abstracted the code to some degree but I don't see the purpose since the click event is probably in the form.  If you are going to leave it there, it is best to use Me. rather than to create a form object.  However if you want to abstract it because you want to be able to use the code elsewhere, then the best method is to pass in the form object:

Private Sub btnAddSelected_Click(frm as Form)

When you call the sub, use  Call btnAddSelected(Me)
0
MeginAuthor Commented:
Okay. Now I am confused. Here is what I have now:
Private Sub btnAddSelected_Click()

'Dim frm As Form
Dim ctl As Control
Dim intCounter As Integer

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Variant
'Set frm = Forms("frm_Act_Enter")
Set ctl = frm![lstPrevRpts]

Set db = CurrentDb
Set rst = db.OpenRecordset("Act_SubTo_Date")

    For Each i In ctl.ItemsSelected
        rst.AddNew
'          Debug.Print i, ctl.Column(5, i)
            rst("ActID") = ctl.Column(5, i)

        rst.Update



'If ctl.ItemsSelected.Count > Null Then
'    For i = Null To ctl.ItemsSelected.Count - 1
'        rst.AddNew
'
'            rst("ActID") = ctl.Column(4, i)
'
'        rst.Update
    Next i
'End If

rst.Close
Set rst = Nothing



End Sub

Open in new window


I have commented out several of the lines because I am not entirely sure if I was supposed to delete them.

I am attaching the database. It should open to the form I am using.

The list box is above the button "Add Selected Items to Current Report."

Information will only show up in the list box if there is information in the Sub Task Order combo box and two dates chosen. So do this:

1. Select the AA task order.
2. Select the sub task order "5- testing....".
3. Add the date 7/1/14 to the beginning date and 7/19/14 to the end date.
4. Click "Search for Previous Reports."

This will bring up the list in the "Previous Reports" lstbox.

The button labeled "Add Selected Items to Current Report" is what is supposed to trigger this code.


I am sorry I am not picking up on how to do this. Thank you for your patience and your help!
NewStartPMAC.accdb
0
PatHartmanCommented:
If you comment out the "frm =" line, you must alter the procedure header as I described and change the procedure call as I described.

You can ignore what I said about abstraction and we'll just get the code working as it is.  So, uncomment the "frm =" line.

I can't download the database at this time so hopefully just uncommenting the line will resolve the problem.
0
MeginAuthor Commented:
Now I am not getting any errors, but it is back to adding empty records to the table.
0
PatHartmanCommented:
uncomment your debug.print so you can see what is happening within the loop.
0
MeginAuthor Commented:
It says "2       Null" in the immediate window.
0
PatHartmanCommented:
Sounds like column 5 may not be what you want.  Try putting a stop in the code and then displaying each column in the debug window, one at a time.  Remember that the RowSource of a combo/listbox is a zero-based array so the first column is 0, the second is 1, etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MeginAuthor Commented:
Okay, so it wasn't working when I went to lunch, but now it is!!!!!!

I could cry, I am so happy.

THANK YOU SO MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
MeginAuthor Commented:
You were so patient and helpful. Thank you so much!
0
PatHartmanCommented:
Sometimes Access needs lunch also:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.