Re: REPOST: Problem With Listbox Selected Property

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for responding. The queries are shown below.

As you can see, the first query is very simple and the second query is a
little more complex due to the Nz() function containing the listbox
reference and the nested IIf() function. The purpose of the IIf() function
is to make the query filter on the current fiscal year in the event a fiscal
year has not been selected in the lstFY listbox. Both queries have been
consistently returning the correct records.

Just now, I performed a test where I completely removed the filter from the
qryAppCodeLookup query and then re-ran my test. The problem went away.

Afterwards, I modified the query filter by adding only the listbox reference
([Forms]![ffrmWSDBgtToActByWBS]![lstFY]) and then re-ran my test. The
problem returned. The listbox reference appears to be the source of the
problem, but I don't know why. I'm guessing that the problem may have
something to do with the fact that a *listbox* control is being directely
referenced from Jet SQL.

As a workaround, I have a VBA function that I can use within Jet SQL that
will convert selected listbox items to a list that I can use within an In()
expression in Jet SQL. This alternative approach *might* solve the problem.

If you have any other ideas please let me know. I'll post the results of my
next test as soon as I'm done.

qryFiscalYearLookup Query:

SELECT DISTINCT tblAppCode.FiscalYear
FROM tblAppCode
ORDER BY tblAppCode.FiscalYear DESC;

qryAppCodeLookup Query:

SELECT DISTINCT tblAppCode.AppCodeID, tblAppCode.AppCode,
tblAppCode.AppCodeDesc, tblAppCode.FiscalYear
FROM tblAppCode
WHERE
(((tblAppCode.FiscalYear)=Nz([Forms]![ffrmWSDBgtToActByWBS]![lstFY],IIf(Month(Date())>6
And Month(Date())<=12,Year(Date())+1,Year(Date())))))
ORDER BY tblAppCode.FiscalYear DESC , tblAppCode.AppCode;




"xRoachx" <xRoachx@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1A453AB7-161A-4DC6-A2AD-F2231D388C5B@xxxxxxxxxxxxxxxx
> Hey Mike, I tested what you posted using 2 list boxes but I was unable to
> duplicate the issue. What are the underlying queries doing?
>
> "MikeC" wrote:
>
>> I previously posted the below question in
>> microsoft.public.access.formscoding and received no response, so I'm
>> reposting to microsoft.public.access.modulesdaovba in hopes that I can
>> find
>> someone who understands the problem I'm having.
>>
>>
>> "MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:XHe9f.7453$dO2.4516@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> > I'm developing an Access 2002 application on a Windows XP machine. I
>> > have
>> > two unbound listboxes, "lstFY" and "lstAppCd", on the same unbound
>> > form.
>> > (Some of the property settings are shown at the bottom of this post.)
>> >
>> > The lstFY listbox has one event procedure in its AfterUpdate event.
>> > The
>> > procedure requeries a second listbox, lstAppCd and 4 combo boxes.
>> > After
>> > lstAppCd is requeried, all items in lstAppCd are selected using another
>> > sub procedure (below).
>> >
>> > About 98% of the time the code works fine. However, during testing I
>> > discovered that when I use my mouse to change the selected value in
>> > lstFY
>> > and then change the value back to what it was previously and then click
>> > on
>> > the same selection a 2nd or 3rd time, all items in lstAppCd are
>> > deselected. See below example. Very strange. I also found that the
>> > problem goes away if I comment-out the line that requeries the lstAppCd
>> > listbox. This is an important clue, but I don't understand the
>> > underlying
>> > problem.
>> >
>> > 3-Step Example:
>> >
>> > 1) Click on 2005 in lstFY listbox. - lstAppCd list box is requeried
>> > and
>> > all items are selected.
>> > 2) Click on 2006 in lstFY listbox. - lstAppCd list box is requeried
>> > and
>> > all items are selected.
>> > 3) Click on 2006 again in lstFY listbox. - lstAppCd list box is
>> > requeried
>> > and all NONE of the items are selected. I have already verified via
>> > Debug.Print that the "Selected" property for each item is literally set
>> > to
>> > *False* despite the fact that I specified "True," not "False"!!!
>> > Normally, the value is set to True, but not in the above scenario.
>> >
>> > Has anyone experienced this type of problem with a listbox or know how
>> > to
>> > solve the problem?
>> >
>> > The VBA code is shown below:
>> > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>> > Private Sub lstFY_AfterUpdate()
>> >
>> > 'This procedure requeries the controls whenever the fiscal year
>> > 'selected in the lstFY listbox changes and selects all items in
>> > 'the lstAppCd listbox.
>> >
>> > On Error GoTo ERR_HANDLER
>> >
>> > With Me
>> > 'Requery the lstAppCd list box.
>> > !lstAppCd.Requery
>> >
>> > 'Select all items in the lstAppCd list box.
>> > SelectListBoxItems !lstAppCd
>> >
>> > 'Requery the comparison period combo boxes.
>> > !cmbPos1.Requery
>> > !cmbPos2.Requery
>> > !cmbPos3.Requery
>> > !cmbPos4.Requery
>> > End With
>> >
>> > EXIT_PROCEDURE:
>> > Exit Sub
>> >
>> > ERR_HANDLER:
>> > If Err.Number <> 2501 Then
>> > ErrorHandler Err.Number, Err.Description, "lstFY_AfterUpdate",
>> > Me.Name
>> > End If
>> > Resume EXIT_PROCEDURE
>> >
>> > End Sub
>> >
>> >
>> > Public Sub SelectListBoxItems(lstBox As ListBox)
>> > On Error GoTo ERR_HANDLER
>> >
>> > 'This procedure selects all items in a specified list box.
>> > '
>> > 'This code is part of a standard code module.
>> >
>> > Dim lngListCount As Long
>> >
>> > With lstBox
>> > For lngListCount = 0 To .ListCount - 1
>> > .Selected(lngListCount) = True
>> > Debug.Print .Selected(lngListCount)
>> > Next lngListCount
>> > End With
>> >
>> > EXIT_PROCEDURE:
>> > On Error Resume Next
>> > Set lstBox = Nothing
>> > Exit Sub
>> >
>> > ERR_HANDLER:
>> > If Err.Number <> 2501 Then
>> > ErrorHandler Err.Number, Err.Description, "SelectListBoxItems",
>> > mconModuleName
>> > End If
>> > Resume EXIT_PROCEDURE
>> >
>> > End Sub
>> > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>> >
>> > Some lstFY Listbox Properties:
>> >
>> > ColumnCount = 1
>> > RowSource = qryFiscalYearLookup
>> > Enabled = Yes
>> > Multiselect = None
>> > ColumnHeads = No
>> >
>> > Some lstAppCd Listbox Properties:
>> >
>> > ColumnCount = 4
>> > RowSource = qryAppCodeLookup
>> > Enabled = Yes
>> > Multiselect = Extended
>> > ColumnHeads = No
>> >
>> > The application has also been decompiled and recompiled, but the
>> > problem
>> > remains.
>> >
>> >
>>
>>
>>



.

Relevant Pages

  • RE: a special type of search form
    ... > Private Sub List1_DblClick ... > double-clicked on the main form ListBox List1 ... > You can also set criteria in your query based on values on ... >>to open up a form displaying the more extensive details. ...
    (microsoft.public.access.forms)
  • Re: Multi select list box to unbound textbox
    ... >module and then I call that function in the afterupdate of the listbox? ... function call right in the criteria line of the Query. ... >end sub ... >The result of ListToText isn't going anywhere and I'm sure its because I'm ...
    (microsoft.public.access.forms)
  • Re: Sorting listbox contents
    ... Sort list box by column when column is right-clicked ... 'Install call to this code in the Mouse Down event of a listbox. ... Dim iLoop As Integer ... MsgBox "List box must use a query as it's row source" ...
    (microsoft.public.access.modulesdaovba)
  • Re: How To Steamline My Queries
    ... Just select a month in the listbox, and the query will "read" ... > different queries which return anniversary dates. ... > FROM Clients ...
    (microsoft.public.access.formscoding)
  • Re: Help - Cant understand Code
    ... Listbox with MultiSelection used in Query ... I have the sample database in its orignial form. ... The subform doesn't have a SELECT statement however the query it is bound ...
    (microsoft.public.access.forms)