Re: Column Heads

From: Dan M (dannerinoremove_at_aol.com)
Date: 03/15/04 

Date: Mon, 15 Mar 2004 09:56:46 -0800

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.
>-----Original Message-----
>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
>
>
>
><anonymous@discussions.microsoft.com> wrote in message
>news:bf3901c40859$8d6737c0$a401280a@phx.gbl...
>> 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.
>> >-----Original Message-----
>> >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
>> >
>> >
>> >"Dan M" <dannerinoremove@aol.com> wrote in message
>> >news:864001c407a9$c4722960$a601280a@phx.gbl...
>> >> 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!
>> >
>> >
>> >.
>> >
>
>
>.
>