Fill a Listbox or DropDownMenu with data from a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

you can do

Set Me.Recordset = rs

but how can you use data from a recordset to be dispalyed in a ListBox or
DropDownMenu other than loop through the recordset and add each line manually.

..RowSource ony eats an sql string, but you cant run an sql sting on a
recordset I bet.
 
you can do

Set Me.Recordset = rs

but how can you use data from a recordset to be dispalyed in a ListBox or
DropDownMenu other than loop through the recordset and add each line manually.

.RowSource ony eats an sql string, but you cant run an sql sting on a
recordset I bet.
Click to expand...

have a look at the RecordSource of the form
this is the SQl string of the recordset

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
the sql string is not the exercise.

it is a recordet->listbox problem not a sql->recordset probblem.
 
Henry said:
you can do

Set Me.Recordset = rs

but how can you use data from a recordset to be dispalyed in a ListBox or
DropDownMenu other than loop through the recordset and add each line manually.

.RowSource ony eats an sql string, but you cant run an sql sting on a
recordset I bet.
Click to expand...


To quote Spock - "Fascinating!"

I've never actually done this in an application, but in
researching your question, I discovered (in AXP) that the
combo box object has a Recordset property. That's a big
surprise to me, so I gave it a try:

Set rs = CurrentDb.OpenRecordset("SELECT . . .")
Set Me.combobox.Recordset = rs
Set rs = Nothing

and, lo and behold, wonders of wonders, it worked just as I
think you want it to.

Now, as to why I never even thought to try this before, I
guess it's because I have never come across a reason to do
this. If you can open a recordset, at least in my
experience, you could also set the combo box's RowSource to
do the same thing. If you do have a good reason for this
odd(?) requirement, I would be very interested in knowing
what it is.
 
the sql string is not the exercise.

it is a recordet->listbox problem not a sql->recordset probblem.
Click to expand...

have you looked up the recordsource? NO , because then you would have
seen that this is the SQL-String from the recordset

listbox.Rowsource=me.recordsource



If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Hello Marsch !

The problem is: When you set up workgroup security by creating a .mdw file,
this will not work anymore:

Me!combobox.RowSource = "SELECT ShipCity FROM Cities " & _
"IN '' [MS
Access;database=C:\Access\test.mdb;pwd=test] " & _
"ORDER BY [ShipCity]; "

This is because the security needs also the UID etc.., but the 'IN'
statement will not eat uids.

therefore .rowsource is not an option anymore. At least I managed to open a
recordset with this crazy connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Password=test;User ID=UserName;Data
Source=C:\test.mdb;Mode=Share Deny None;Extended Properties="";Jet
OLEDB:System database=C:\Security.mdw;Jet OLEDB:Registry Path="";Jet
OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking
Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False

But how the hell would you do this in an access sql string like above - to
fill up a listbox ?

what a challenge !
 
Whoa. If that's what the problem takes, it's no wonder I
never ran into it :-\

Were you able to use the Recordset property?
--
Marsh
MVP [MS Access]


The problem is: When you set up workgroup security by creating a .mdw file,
this will not work anymore:

Me!combobox.RowSource = "SELECT ShipCity FROM Cities " & _
"IN '' [MS
Access;database=C:\Access\test.mdb;pwd=test] " & _
"ORDER BY [ShipCity]; "

This is because the security needs also the UID etc.., but the 'IN'
statement will not eat uids.

therefore .rowsource is not an option anymore. At least I managed to open a
recordset with this crazy connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Password=test;User ID=UserName;Data
Source=C:\test.mdb;Mode=Share Deny None;Extended Properties="";Jet
OLEDB:System database=C:\Security.mdw;Jet OLEDB:Registry Path="";Jet
OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking
Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale
on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False

But how the hell would you do this in an access sql string like above - to
fill up a listbox ?

what a challenge !

Marshall Barton said:
To quote Spock - "Fascinating!"

I've never actually done this in an application, but in
researching your question, I discovered (in AXP) that the
combo box object has a Recordset property. That's a big
surprise to me, so I gave it a try:

Set rs = CurrentDb.OpenRecordset("SELECT . . .")
Set Me.combobox.Recordset = rs
Set rs = Nothing

and, lo and behold, wonders of wonders, it worked just as I
think you want it to.

Now, as to why I never even thought to try this before, I
guess it's because I have never come across a reason to do
this. If you can open a recordset, at least in my
experience, you could also set the combo box's RowSource to
do the same thing. If you do have a good reason for this
odd(?) requirement, I would be very interested in knowing
what it is.
Click to expand...
Click to expand...
 
Marshall Barton said:
To quote Spock - "Fascinating!"

I've never actually done this in an application, but in
researching your question, I discovered (in AXP) that the
combo box object has a Recordset property. That's a big
surprise to me, so I gave it a try:

Set rs = CurrentDb.OpenRecordset("SELECT . . .")
Set Me.combobox.Recordset = rs
Set rs = Nothing

and, lo and behold, wonders of wonders, it worked just as I
think you want it to.
Click to expand...


I used this technique two days ago in one of my 2002 databases to fill a
combo box. Works well.... didn't know I'd stumbled on something < g >!
 
Ken said:
I used this technique two days ago in one of my 2002 databases to fill a
combo box. Works well.... didn't know I'd stumbled on something < g >!
Click to expand...

..Recordset definately is not a property of a combobox in A97. Pity :-(
looked nice.
 
Back
Top