List box not displaying more than two columns

[Mark]

I am struggling to make a listbox display more than two columns. The
list box is set to value and I use the following VBA to populate it:

Dim myrs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT tblTempContractNoteIDs.* FROM tblTempContractNoteIDs;"
Set myrs = New ADODB.Recordset

myrs.Open SQL, CurrentProject.Connection, adOpenKeyset

Me.lstChoices.ColumnCount = 7
Do While Not myrs.EOF
Me.lstChoices.addItem (myrs.Fields(1)
& ";" & myrs.Fields(2) & ";" & myrs.Fields(3) & _
";" & myrs.Fields(4) & ";" &
myrs.Fields(5) & ";" & myrs.Fields(6) & ";" & myrs.Fields(7))
myrs.MoveNext
Loop

When manually running the SQL statement all 8 columns are returned?
Any suggestions would be greatly appreciated.

 

[XPS350]

I am struggling to make a listbox display more than two columns.  The
list box is set to value and I use the following VBA to populate it:

Dim myrs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT tblTempContractNoteIDs.* FROM tblTempContractNoteIDs;"
                Set myrs = New ADODB.Recordset

                myrs.Open SQL, CurrentProject.Connection,adOpenKeyset

                Me.lstChoices.ColumnCount = 7
                Do While Not myrs.EOF
                                Me.lstChoices.addItem (myrs.Fields(1)
& ";" & myrs.Fields(2) & ";" & myrs.Fields(3) & _
                                ";" & myrs.Fields(4) & ";" &
myrs.Fields(5) & ";" & myrs.Fields(6) & ";" & myrs.Fields(7))
                                myrs.MoveNext
                Loop

When manually running the SQL statement all 8 columns are returned?
Any suggestions would be greatly appreciated.

Check the listbox properties (ColumnCount, ColumnWidths).

Peter
http://access.xps350.com

 

[Mark]

Check the listbox properties (ColumnCount, ColumnWidths).

Peterhttp://access.xps350.com

Hi Peter,

As far as I know I have all the properties setup correctly! Here are
the properties I have setup:

Column count = 7
Column Widths = [blank]
Column Heads = No
Control Source = [blank]
Row Source = [blank]
Row Source Type = Value list
Bound Column = 1
Inherit Value List = Ho
Show Only Row Source Value = No

Am I missing anything obvious? I've been looking at this for days! :-(

 

[Ken Snell]

Set your Column Widths property:

Column Widths = 1";1";1";1";1";1";1";1"

Set Column Count to 8.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Check the listbox properties (ColumnCount, ColumnWidths).

Peterhttp://access.xps350.com

Hi Peter,

As far as I know I have all the properties setup correctly! Here are
the properties I have setup:

Column count = 7
Column Widths = [blank]
Column Heads = No
Control Source = [blank]
Row Source = [blank]
Row Source Type = Value list
Bound Column = 1
Inherit Value List = Ho
Show Only Row Source Value = No

Am I missing anything obvious? I've been looking at this for days! :-(

 

[Mark]

Set your Column Widths property:

Column Widths = 1";1";1";1";1";1";1";1"

Set Column Count to 8.
--

        Ken Snellhttp://www.accessmvp.com/KDSnell/

Check the listbox properties (ColumnCount, ColumnWidths).

Peterhttp://access.xps350.com

Hi Peter,

As far as I know I have all the properties setup correctly!  Here are
the properties I have setup:

Column count = 7
Column Widths = [blank]
Column Heads = No
Control Source = [blank]
Row Source = [blank]
Row Source Type = Value list
Bound Column = 1
Inherit Value List = Ho
Show Only Row Source Value = No

Am I missing anything obvious?  I've been looking at this for days! :-(- Hide quoted text -

- Show quoted text -

Tried setting the column widths correctly and column count to 8, still
no joy.

 

[XPS350]

Set your Column Widths property:

Column Widths = 1";1";1";1";1";1";1";1"

Set Column Count to 8.
--

        Ken Snellhttp://www.accessmvp.com/KDSnell/

"Mark" <[email protected]> wrote in message

Hi Peter,

As far as I know I have all the properties setup correctly!  Here are
the properties I have setup:

Column count = 7
Column Widths = [blank]
Column Heads = No
Control Source = [blank]
Row Source = [blank]
Row Source Type = Value list
Bound Column = 1
Inherit Value List = Ho
Show Only Row Source Value = No

Am I missing anything obvious?  I've been looking at this for days! :-(- Hide quoted text -

- Show quoted text -

Tried setting the column widths correctly and column count to 8, still
no joy.

I looked at your code again and I wonder why you don't use just:
Me.lstChoices.RowSource="SELECT * FROM tblTempContractNoteIDs"
to populate the list?

I am not sure it is the solution but I looks better anyway.


Peter
http://access.xps350.com

 

[Mark]

On 31 Aug, 17:08, "Ken Snell" <[email protected]>
wrote:

Set your Column Widths property:
Column Widths = 1";1";1";1";1";1";1";1"
Set Column Count to 8.
--
        Ken Snellhttp://www.accessmvp.com/KDSnell/
I am struggling to make a listbox display more than two columns. The
list box is set to value and I use the following VBA to populate it:
Dim myrs As ADODB.Recordset
Dim SQL As String
SQL = "SELECT tblTempContractNoteIDs.* FROM tblTempContractNoteIDs;"
Set myrs = New ADODB.Recordset
myrs.Open SQL, CurrentProject.Connection, adOpenKeyset
Me.lstChoices.ColumnCount = 7
Do While Not myrs.EOF
Me.lstChoices.addItem (myrs.Fields(1)
& ";" & myrs.Fields(2) & ";" & myrs.Fields(3) & _
";" & myrs.Fields(4) & ";" &
myrs.Fields(5) & ";" & myrs.Fields(6) & ";" & myrs.Fields(7))
myrs.MoveNext
Loop
When manually running the SQL statement all 8 columns are returned?
Any suggestions would be greatly appreciated.
Check the listbox properties (ColumnCount, ColumnWidths).
Peterhttp://access.xps350.com
Hi Peter,
As far as I know I have all the properties setup correctly!  Here are
the properties I have setup:
Column count = 7
Column Widths = [blank]
Column Heads = No
Control Source = [blank]
Row Source = [blank]
Row Source Type = Value list
Bound Column = 1
Inherit Value List = Ho
Show Only Row Source Value = No
Am I missing anything obvious?  I've been looking at this for days!:-(- Hide quoted text -
- Show quoted text -

Tried setting the column widths correctly and column count to 8, still
no joy.

I looked at your code again and I wonder why you don't use just:
Me.lstChoices.RowSource="SELECT * FROM tblTempContractNoteIDs"
to populate the list?

I am not sure it is the solution but I looks better anyway.

Peterhttp://access.xps350.com- Hide quoted text -

- Show quoted text -

I am using two list boxes as a way to select values. Basically you
select an item from one list box and click the ">" button and the item
moves to the selected list box. I therefore need to be able to
manipulate the values in the list box and it can't be bound to a
source, this is why I am using VBA to populate the listbox.

 

[John W. Vinson]

I am using two list boxes as a way to select values. Basically you
select an item from one list box and click the ">" button and the item
moves to the selected list box. I therefore need to be able to
manipulate the values in the list box and it can't be bound to a
source, this is why I am using VBA to populate the listbox.

What makes you say that "it can't be bound to a source"?

It's perfectly straightforward to use action queries to add or remove records
from a Table which is used as the rowsource of a listbox; or - often better -
to change the values in a Table so that the Query used as the rowsource
returns different rows. You just need to Requery the listbox after changing
the data.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David W. Fenton]

m:

I am using two list boxes as a way to select values. Basically
you select an item from one list box and click the ">" button and
the item moves to the selected list box. I therefore need to be
able to manipulate the values in the list box and it can't be
bound to a source, this is why I am using VBA to populate the
listbox.

Are you using value lists or a function?

 

[Mark]

What makes you say that "it can't be bound to a source"?

It's perfectly straightforward to use action queries to add or remove records
from a Table which is used as the rowsource of a listbox; or - often better -
to change the values in a Table so that the Query used as the rowsource
returns different rows. You just need to Requery the listbox after changing
the data.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks John, think I will try updating the table rather than the
listbox as I'm sure this will work. Still not sure why I can only
display two columns when adding the rows programatically.