473,326 Members | 2,076 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

For Each in listbox not working

10
I've created a form dialog box with several checkboxes to determine which offices to include in a sales report. However, I'm getting an "Object doesn't support this property or method" error from MS Access, which isn't very helpful as it doesn't let me debug or pinpoint what's going wrong. I -think- it's at this line

For Each VarItem in Me.lstOffices
strOffices = strOffices & ",'" & Me.lstOffices.ItemData(varItem) & "'"
Next varItem

where Me.lstOffices is a hidden, blank listbox on the form that gets items added to it based on which offices are checked. Here's the full code below. I know an alternate method of doing this is to use a multiple select listbox instead of checkboxes, but my supervisor seems set on checkboxes.

Expand|Select|Wrap|Line Numbers
  1.     Dim strDocName As String
  2.     Dim varItem As Variant
  3.     Dim strOffices As String
  4.     Dim strStatus As String
  5.     Dim strFilter As String
  6.  
  7.     'Clear listbox contents.
  8.     Me.lstOffices.RowSource = ""
  9.  
  10.     If chkLA Then
  11.         Me.lstOffices.AddItem 1
  12.     End If
  13.  
  14.     If ChkSF Then
  15.         Me.lstOffices.AddItem 2
  16.     End If
  17.  
  18.     If chkLV Then
  19.         Me.lstOffices.AddItem 3
  20.     End If
  21.  
  22.     If chkChi Then
  23.         Me.lstOffices.AddItem 4
  24.     End If
  25.  
  26.     For Each varItem In Me.lstOffices
  27.         strOffices = strOffices & ",'" & Me.lstOffices.ItemData(varItem) & "'"
  28.     Next varItem
  29.  
  30. 'Build filter string
  31. strFilter = "[SalesOffice] " & strOffice & ""
  32.  
  33. ' Apply the filter and switch it on
  34.     With Reports![rptPreJobList]
  35.         .Filter = strFilter
  36.         .FilterOn = True
  37.     End With
  38.  
  39.     If Len(strOffices) = 0 Then
  40.         strOffices = "Like '*'"
  41.     Else
  42.         strOffices = Right(strOffices, Len(strOffices) - 1)
  43.         strOffices = "IN(" & strOffices & ")"
  44.     End If
  45.  
  46.     strDocName = "rptPreJobList"
  47.     DoCmd.OpenReport stDocName, acPreview
  48.  
  49.  
Sep 13 '06 #1
1 4804
Selesti
10
Never mind. I figured it out; I was making it way too complicated with the list box - I took it out and just added the numbers to the string. for anyone who is curious, here is my ending code.

Expand|Select|Wrap|Line Numbers
  1.     'Clear listbox contents.
  2.     Me.lstOffices.RowSource = ""
  3.  
  4.     If chkLA Then
  5.         strOffices = strOffices & ", 1"
  6.     End If
  7.  
  8.     If ChkSF Then
  9.         strOffices = strOffices & ", 2"
  10.     End If
  11.  
  12.     If chkLV Then
  13.         strOffices = strOffices & ", 3"
  14.     End If
  15.  
  16.     If chkChi Then
  17.         strOffices = strOffices & ", 4"
  18.     End If
  19.  
  20.     If Len(strOffices) = 0 Then
  21.         strOffices = "Like '*'"
  22.     Else
  23.         strOffices = Right(strOffices, Len(strOffices) - 1)
  24.         strOffices = "IN(" & strOffices & ")"
  25.     End If
  26.  
  27.  
  28. ' Build filter string
  29. strFilter = "[SalesOffice] " & strOffices
  30.  
  31. ' Open report
  32.     stDocName = "rptPreJobList"
  33.     DoCmd.OpenReport stDocName, acPreview
  34.  
  35.  
  36. ' Apply the filter and switch it on
  37.     With Reports![rptPreJobList]
  38.         .Filter = strFilter
  39.         .FilterOn = True
  40.     End With
  41.  
Sep 13 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Andrew | last post by:
I'm having a major problem with a databound listbox in C#. In the constructor for the form I am trying to pre-select some of the items based in information in the database. When I step through the...
8
by: Bill | last post by:
I'm trying to create a wizardlike interface using a couple listboxes. I know you've seen it before. You double click on an item in one listbox and it "moves" it to the other. I used to approach...
9
by: Megan | last post by:
Hi- I'm creating a database of music bands with their cds and songs. I'm trying to program an SQL statement so that I can enter a string of text in a textbox, press the 'Enter' key, and have...
8
by: Oddball | last post by:
Ok - I have a ListBox control and I'm ready to write my own DrawItem event handler. What I want to draw as the item is another control. I have created a user control that I would like to list in...
4
by: amber | last post by:
Hello I'm not sure if I should give up trying to find an answer here...or just keep posting my problem.. I'm having problems with a listbox.. I have a listbox that is populated when a user...
5
by: Dave | last post by:
Hi All, I have a windows form that contains 2 listboxes and 2 buttons. The listbox on the right is populated by a database routine (This is the easy part). The listbox on the left is populated...
11
by: John Dann | last post by:
I'm still struggling to find a way of reordering the items within the same single listbox with drag and drop. I think I've got the drag working but it's the drop code I can't figure out. What I...
4
by: lgbjr | last post by:
Hi All, I've got a listbox on a VB.NET form. when the form opens, the ListBox SelectionMode is set to Single. while running various routines on the form, items get added to the list box (results...
6
by: kucheravy | last post by:
Hi, everybody. I have this problem. When I put a <asp:ListBox on a web page and populate the data in the page Page_Load event the ViewState for the control is saved and loaded (after postback)...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.