Need help with listing field names in listbox

[Daama via AccessMonster.com]

Hi All,

I am trying to display a list of all field names in a particular query/table.
I am trying the code below but it only gives me one single name rather than
the whole list.
I would appreciate any help! Table name=buyer, listbox= lstSelectFrom



Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String
strSQL = "Select * from buyer"

Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
strMsg = strMsg & fld.Name & vbNewLine
Next

strStart = "" _
& "" & vbNewLine & vbNewLine

Me.lstSelectFrom.RowSource = strStart & strMsg
Me.lstSelectFrom.RowSourceType = "value list"

If I display the list on a msgbox, it will work just fine

e.g MsgBox strStart & strMsg, vbInformation, ""

Thanks,

 

[Douglas J. Steele]

Value List Row Sources expect all of the values on a single line, and
delimited with semi-colons.

Try:

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String
strSQL = "Select * from buyer"

Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
strMsg = strMsg & fld.Name & ";"
Next

strMsg = Left(strMsg, Len(strMsg)-1)

Me.lstSelectFrom.RowSource = strMsg
Me.lstSelectFrom.RowSourceType = "value list"


Note, though, that you could simply set the RowSourceType to "Field List",
and the RowSource "Buyer"

 

[inkman04]

Hello,

What are you trying to display the "field" list in?
A list box, combo box,???

If a list box, just select "Field List" in the row sourc
type property and put your table/query as the source
in the "Row Source" property.

Hope this helps. No code required.

Regards

 

[Daama via AccessMonster.com]

Dear Douglas,

The code looks its going to work. But it still show the following error:

( Compile Error: Ambiguous name detected:Left. )

on the line strMsg = Left(strMsg, Len(strMsg)-1)

Do you have an idea.

Thanks for the help.

Daama

Value List Row Sources expect all of the values on a single line, and
delimited with semi-colons.

Try:

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String
strSQL = "Select * from buyer"

Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
strMsg = strMsg & fld.Name & ";"
Next

strMsg = Left(strMsg, Len(strMsg)-1)

Me.lstSelectFrom.RowSource = strMsg
Me.lstSelectFrom.RowSourceType = "value list"

Note, though, that you could simply set the RowSourceType to "Field List",
and the RowSource "Buyer"

Hi All,

[quoted text clipped - 29 lines]

Thanks,

 

[Douglas J. Steele]

Sounds as though you've used Left for something yourself: a variable, a
field name, a function, a module, etc.

If you cannot find it, you can try VBA.Left instead of just Left, but you
really need to find the object so that it doesn't cause you problems!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Douglas,

The code looks its going to work. But it still show the following error:

( Compile Error: Ambiguous name detected:Left. )

on the line strMsg = Left(strMsg, Len(strMsg)-1)

Do you have an idea.

Thanks for the help.

Daama

Value List Row Sources expect all of the values on a single line, and
delimited with semi-colons.

Try:

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String
strSQL = "Select * from buyer"

Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
strMsg = strMsg & fld.Name & ";"
Next

strMsg = Left(strMsg, Len(strMsg)-1)

Me.lstSelectFrom.RowSource = strMsg
Me.lstSelectFrom.RowSourceType = "value list"

Note, though, that you could simply set the RowSourceType to "Field List",
and the RowSource "Buyer"

Hi All,

[quoted text clipped - 29 lines]

Thanks,

 

[Daama via AccessMonster.com]

Douglas, Thanks a bunch!!!

Daama

Sounds as though you've used Left for something yourself: a variable, a
field name, a function, a module, etc.

If you cannot find it, you can try VBA.Left instead of just Left, but you
really need to find the object so that it doesn't cause you problems!

Dear Douglas,

[quoted text clipped - 41 lines]