Column Heads

[Dan M]

Access XP adp file with SQL Server 2K back end.

Have a list box whose rowsource is a stored procedure.
The stored procedure is programmed to return one of two
recordsets from SELECT statements based on user's input on
a combo box. The column aliases are different between the
two select statements, but my column headings on the list
box don't change.

Here's a simplified version of my two SELECT statements:

SELECT EmployeeName as Name, EmployeePhone as Phone FROM
tblEmployees

SELECT EmployeeName as Name, EmployeeFax as Fax FROM
tblEmployees

The data does change, and fax numbers are displayed
instead of phone numbers when the user selects the Fax
Numbers option from the combo box. But the column
headings do not. So, I'm looking at fax numbers with a
column entitled Phone. Anyone ever seen this behavior?
Help!

 

[Michel Walsh]

Hi,


If you set the Headers property to true, you see the alias (or fields)
name in the LIST of the combo box, meaning by that when the list is
displayed, down.

You may have to change the Caption properties of Label control if you
wish the "text" associated to a control to change:


Me.LabelName.Caption = "New text"


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks Vanderghast. But perhaps I didn't explain my
problem clearly enough.

I'm not dealing with a combo box, nor do I have any labels
whose caption I can change. I'm dealing with a listbox.
There is a combo box, but it is used to choose criteria
and its AfterUpdate event fires to requery the listbox.
It is the listbox whose headers aren't changing, even
though the rowsource SQL is changing.

So, when the event fires, and my listbox rowsource is
changed, the data changes fine but the headers do not.

 

[Michel Walsh]

Hi,


It seems to work fine with Access 2003 + Jet: I created a form, one
list, two command buttons, one combo box:

==================================
Option Compare Database
Option Explicit

Private Sub Command2_Click()

Me.List0.ColumnHeads = True
Me.Combo4.ColumnHeads = True
Me.List0.RowSource = "SELECT CustomerID As AAAAA FROM Customers"
Me.Combo4.RowSource = "SELECT CustomerID As AAAAA FROM Customers"
End Sub

Private Sub Command3_Click()


Me.List0.RowSource = "SELECT OrderID As ZZZZZ FROM Orders"
Me.Combo4.RowSource = "SELECT OrderID As ZZZZZ FROM Orders"
End Sub

===========================



List and combo box behaved the same way, the CAPTION defined in the table is
displayed, Customer ID or Order ID; if no caption is defined in the
table, or if I use a computed expression, the ALIAS is used, AAAAA or
ZZZZZ:

===========================
Option Compare Database
Option Explicit

Private Sub Command2_Click()

Me.List0.ColumnHeads = True
Me.Combo4.ColumnHeads = True
Me.List0.RowSource = "SELECT CustomerID & ' ' As AAAAA FROM Customers"
Me.Combo4.RowSource = "SELECT CustomerID & ' ' As AAAAA FROM Customers"
End Sub

Private Sub Command3_Click()


Me.List0.RowSource = "SELECT OrderID & ' ' As ZZZZZ FROM Orders"
Me.Combo4.RowSource = "SELECT OrderID & ' ' As ZZZZZ FROM Orders"
End Sub
===========================




Are you using JET? or MS SQL Server? Is it the same behavior observable on
a brand new database, or with Northwind, or just in your actual application?



Vanderghast, Access MVP

 

[Dan M]

I'm using SQL Server, and the rowsource of my list box is
a stored procedure. Using a new database file, requerying
the list box works fine. Using an adp file, if I use code
to change my listbox rowsource from one stored procedure
to another, it works fine. It's when I have both SQL
statements within a single stored procedure that the
column heads don't seem to be returned.

Here's a more specific description of what's happening.
My form is bound to a simple SQL statement. The combo box
is used to select criteria that changes the display of the
list box. The combo box is actually bound to a field in
the form's recordsource (I store user selections so my app
will "remember" where they left off).

Once the value of the combo box has been changed, the
after update event calls the list box requery method. The
list box requeries based on my stored procedure, which
does 2 things. Within the sproc, a UDF is called to
retrieve the value (and other values) selected by the user
from the combo box. Based on this value, IF statements
return one of two different SQL statements and the list
box is requeried.

I realize there may seem to be a few ways to simplify this
whole process. However, this form has a lot of
functionality that the company wants. And I'm trying to
streamline things by not writing a different sproc every
time I need to return a set of records.

 

[Michel Walsh]

HI,


I tried to repro your problem, without success.

With Northwind, I created a new stored procedure:

===========
ALTER PROCEDURE StoredProcedure1

(@param int)

AS

IF @param=0
BEGIN
SELECT OrderID As ZZZZ FROM Orders;
END
ELSE
BEGIN
SELECT CustomerID AS AAAA FROM Customers;
END

RETURN
============

In a new form, one list box (with its headers, ColumnHeads set to true) and
two buttons. The code behind the form:

============
Option Compare Database
Option Explicit

Private Sub Command2_Click()
Me.List0.RowSource = "EXEC StoredProcedure1 1"
End Sub

Private Sub Command3_Click()
Me.List0.RowSource = "EXEC StoredProcedure1 0"
End Sub
=============



This setting displays the caption AAAA or ZZZZ as appropriate.



Is it possible for you to try it in a new project, just to be sure if your
problem is about your actual code ( you won't be able to repro with the
simple example) or if it is about your overall setting/installation ( in
which case, even this simple case would not work).



Hoping it may help,
Vanderghast, Access MVP

 

[Dan M]

I was able to reproduce the behavior in a new project if I
use the same stored procedure. The behavior is NOT
reproduced if I use a new stored procedure similar to your
simple example.

Simply passing a parameter in to the sproc and choosing
the SQL statement based on the parameter doesn't cause a
problem. But choosing the SQL statement based on the
value of a variable that is set to the value of a column
from a UDF does. I don't see why it makes a difference.
Either way, the value of the variable is determined and
the appropriate SQL statement is returned. It doesn't
make sense that it would have any effect on refreshing the
column headings.

Here's the syntax of my sproc:
DECLARE @theVariable int
SELECT @theVariable = EmployeeType FROM getEmployeeInfo()
--the getEmployeeInfo UDF is working. It pulls one record
matching the current user and returns the EmployeeType
column.
IF @theVariable = 1
SELECT PhoneNo AS Phone FROM tblEmployees

IF @theVariable = 2
SELECT FaxNo AS Fax FROM tblEmployees

This doesn't work. But if I eliminate the UDF and simply
pass a parameter in, it works fine.

Microsoft has credited me with finding a flaw in stored
procedures and how they work with list boxes (because of
my find, they've documented a fix). Perhaps I've found
another.

 

[Dan M]

I have found that setting the column headings property to
true in my code alleviates the problem. Just seems like a
waste of time having to code this in so many places, but
it solves the problem.

 

[Michel Walsh]

Hi,


Interesting. Forcing a property may indeed force a reading from which
a notification of change have been missed (or not issued). It is great to
discover a bug, even greater to find a fix to it!


Vanderghast, Access MVP