[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Lstbox code not working

Posted on 2014-07-16
13
Medium Priority
?
247 Views
Last Modified: 2014-07-16
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

0
Comment
Question by:Megin
  • 6
  • 5
  • 2
13 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40199926
If what you want is in the 5th column then try this...
ctl.Column(4, intCounter)
0
 

Author Comment

by:Megin
ID: 40199930
Still nothing.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40199949
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:PatHartman
ID: 40199954
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
 

Author Comment

by:Megin
ID: 40200004
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40200061
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
 

Author Comment

by:Megin
ID: 40200080
Now I am not getting any errors, but it is back to adding empty records to the table.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40200099
uncomment your debug.print so you can see what is happening within the loop.
0
 

Author Comment

by:Megin
ID: 40200110
It says "2       Null" in the immediate window.
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40200178
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
 

Author Comment

by:Megin
ID: 40200443
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
 

Author Closing Comment

by:Megin
ID: 40200448
You were so patient and helpful. Thank you so much!
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40200541
Sometimes Access needs lunch also:)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question