Need help with listing field names in listbox

  • Thread starter Thread starter Daama via AccessMonster.com
  • Start date Start date
D

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,
 
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"
 
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
 
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"
[quoted text clipped - 29 lines]
 
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)


Daama via AccessMonster.com said:
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"
[quoted text clipped - 29 lines]
 
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top