REPOST: Problem With Listbox Selected Property

  • Thread starter Thread starter MikeC
  • Start date Start date
M

MikeC

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.
 
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?
 
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;
 
After some testing, I found that the easiest way to control the contents of
the lstAppCd listbox was to set lstAppCd.RowSource = SQL code that I modify
during the AfterUpdate event of the lstFY listbox. This way, I completely
avoid having to reference a listbox *control* from within a query. Instead,
I just use the current *value* of the control. Now it works perfectly.
Thanks for redirecting my attention to the query...which was the source of
the problem.

Below is the updated lstFY_AfterUpdate procedure:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim strSQL As String

With Me
strSQL = "SELECT DISTINCT tblAppCode.AppCodeID, tblAppCode.AppCode,"
_
& " tblAppCode.AppCodeDesc, tblAppCode.FiscalYear" _
& " FROM tblAppCode WHERE tblAppCode.FiscalYear = " & !lstFY _
& " ORDER BY tblAppCode.FiscalYear DESC, tblAppCode.AppCode;"

!lstAppCd.RowSource = strSQL

'Select all items in the lstAppCd listbox.
SelectListBoxItems !lstAppCd

'Requery the comparison period combo boxes.
!cmbPos1.Requery
!cmbPos2.Requery
!cmbPos3.Requery
!cmbPos4.Requery
End With
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


MikeC said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top