populate list box programmatically

I'm using a separate FE and BE. The BE is Jet, but the tables are not
linked. I'm using ADO to connect and retrieve records.

I need to populate a list box with info from a remote table. I
understand how to bind a list box to a query, but that only works for
tables in the FE. I understand how to loop through a recordset and
AddItem to the list box, but there is a limit on how many items you
can add this way.

I'm a little lost on how I should handle this.

Jennifer

Toxalot wrote:
I
understand how to bind a list box to a query, but that only works for
tables in the FE.

I think you should be able to do that also with information from another
db.

I think you need either .locktype adLockOptimistic or adLockPessimistic
and .cursorlocation adUseClient

Perhaps if you show the code?

--
Roy-Vidar

"Toxalot" wrote:

I need to populate a list box with info from a remote table.

This should be no problem.

Set the row source type to "value list". Query the BE and get the data you
need. Loop over this and construct a string with the values separated by
semicolons. Then set the dataSource of the list to that string. Presto!

I know this works for combos, I have to admit I have not tried it with lists.

Maury

There must be a limit to the size of the string. There are two list
boxes in particular that I'm thinking about. One would probably have
about 500 items and the other would probably have over 5000 items.

The 500 item list is a list of specialties and the user needs to
select up to 20.

The 5000 item list is a list of companies and used more as a way of
drilling down to a particular record. I'm recreating a database that
was done in FoxPro (I don't have the source code) and that was how it
was done before. I need to be able to click on a specific company and
then go to that record. I also need to be able to type into a text box
and have the list automatically scroll as I type (not sure if I'm
explaining that well enough.)

This is my first big Access database with FE/BE that doesn't use
simple bound forms. I'm open to suggestions.

Jennifer

On Aug 15, 10:36 am, Maury Markowitz
wrote:
"Toxalot" wrote:
I need to populate a list box with info from a remote table.

This should be no problem.

Set the row source type to "value list". Query the BE and get the data you
need. Loop over this and construct a string with the values separated by
semicolons. Then set the dataSource of the list to that string. Presto!

I know this works for combos, I have to admit I have not tried it with lists.

Maury

I'm new to this. I'm learning from "Beginning Access 2003 VBA". I'm
not sure I really understand when to use the different CursorType,
CursorLocation, and LockType. The default for the main form is to
have all controls locked and disabled so the user can browse, but
can't change anything. Then when they click an edit button, the
controls are enabled and unlocked. I'm not sure if I a should be
opening a read-only recordset and then opening a new recordset if the
user chooses to edit or if I should just open an editable recordset
from the beginning. At the moment, I'm supposed to be developing this
to be used by only one user, but I just know that they will come along
and want to add a new user and I want to understand how to handle both
situations as I learn and develop.

But regardless of what type of cursor and lock I use, the code still
returns a recordset. You can't just set the RowSource of the list box
to the recordset like you can a query.

Jennifer

On Aug 15, 4:50 am, RoyVidar wrote:
Toxalot wrote:
I
understand how to bind a list box to a query, but that only works for
tables in the FE.

I think you should be able to do that also with information from another
db.

I think you need either .locktype adLockOptimistic or adLockPessimistic
and .cursorlocation adUseClient

Perhaps if you show the code?

--
Roy-Vidar

Toxalot wrote:
I'm new to this. I'm learning from "Beginning Access 2003 VBA". I'm
not sure I really understand when to use the different CursorType,
CursorLocation, and LockType. The default for the main form is to
have all controls locked and disabled so the user can browse, but
can't change anything. Then when they click an edit button, the
controls are enabled and unlocked. I'm not sure if I a should be
opening a read-only recordset and then opening a new recordset if the
user chooses to edit or if I should just open an editable recordset
from the beginning. At the moment, I'm supposed to be developing this
to be used by only one user, but I just know that they will come along
and want to add a new user and I want to understand how to handle both
situations as I learn and develop.

But regardless of what type of cursor and lock I use, the code still
returns a recordset. You can't just set the RowSource of the list box
to the recordset like you can a query.

Jennifer

On Aug 15, 4:50 am, RoyVidar wrote:
Toxalot wrote:
I
understand how to bind a list box to a query, but that only works for
tables in the FE.
I think you should be able to do that also with information from another
db.

I think you need either .locktype adLockOptimistic or adLockPessimistic
and .cursorlocation adUseClient

Perhaps if you show the code?

--
Roy-Vidar



I am perhaps not so secretive about my code, here's a little
something that works on my setup

Dim rs2 As ADODB.Recordset

Set rs2 = New ADODB.Recordset
With rs2
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=path and name of db"
cn.Open
Set .ActiveConnection = cn
.CursorType = adOpenStatic
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.Open "SELECT id, test FROM table1", Options:=adCmdText


If Not .BOF And Not .EOF Then
Me!lstTest.ColumnCount = 2
Me!lstTest.RowSourceType = "table/query"
Set Me!cboTest.Recordset = rs2
End If
End With

BTW - for value list rowsource, the limitations are 2048 characters in
the 2000 version, and 32 750 characters in later versions.

--
Roy-Vidar

Oh dear

Set Me!cboTest.Recordset = rs2

should have been

Set Me!lstTest.Recordset = rs2
--
Roy-Vidar

Not secretive so much as unsure what bits of code to post. I didn't
know that Recordset was a property of a list box. I learn as I go.
Your code was very helpful. Thank you.

Jennifer

On Aug 15, 6:36 pm, RoyVidar wrote:
Toxalot wrote:
I'm new to this. I'm learning from "Beginning Access 2003 VBA". I'm
not sure I really understand when to use the different CursorType,
CursorLocation, and LockType. The default for the main form is to
have all controls locked and disabled so the user can browse, but
can't change anything. Then when they click an edit button, the
controls are enabled and unlocked. I'm not sure if I a should be
opening a read-only recordset and then opening a new recordset if the
user chooses to edit or if I should just open an editable recordset
from the beginning. At the moment, I'm supposed to be developing this
to be used by only one user, but I just know that they will come along
and want to add a new user and I want to understand how to handle both
situations as I learn and develop.

But regardless of what type of cursor and lock I use, the code still
returns a recordset. You can't just set the RowSource of the list box
to the recordset like you can a query.

Jennifer

On Aug 15, 4:50 am, RoyVidar wrote:
Toxalot wrote:
I
understand how to bind a list box to a query, but that only works for
tables in the FE.
I think you should be able to do that also with information from another
db.

I think you need either .locktype adLockOptimistic or adLockPessimistic
and .cursorlocation adUseClient

Perhaps if you show the code?

--
Roy-Vidar

I am perhaps not so secretive about my code, here's a little
something that works on my setup

Dim rs2 As ADODB.Recordset

Set rs2 = New ADODB.Recordset
With rs2
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=path and name of db"
cn.Open
Set .ActiveConnection = cn
.CursorType = adOpenStatic
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.Open "SELECT id, test FROM table1", Options:=adCmdText

If Not .BOF And Not .EOF Then
Me!lstTest.ColumnCount = 2
Me!lstTest.RowSourceType = "table/query"
Set Me!cboTest.Recordset = rs2
End If
End With

BTW - for value list rowsource, the limitations are 2048 characters in
the 2000 version, and 32 750 characters in later versions.

--
Roy-Vidar

What is the purpose/benefit of Options:=adCmdText? I've seen mention
of it in passing, but not much explanation.

Jennifer

.Open "SELECT id, test FROM table1", Options:=adCmdText

Toxalot wrote:
What is the purpose/benefit of Options:=adCmdText? I've seen mention
of it in passing, but not much explanation.

Jennifer

.Open "SELECT id, test FROM table1", Options:=adCmdText



If you don't specify CommandTypeEnum, ADO must resolve how the command
is to be interpreted (the default, I think, is adCmdUnknown), which
will add a tiny bit of time, and under some circumstances, where I
don't recall the details, not specifying CommandTypeEnum might make
the code fail.

You can find the different constants, if you go to the help file on
ADO .Open, and check out the CommandTypeEnum link. What I use most,
is adCmdText for dynamic SQL, and adCmdStoredProc for stored queries.

Also, if you're executing non-row-returning queries on an ADO connection
or command object, you might want to look into the ExecuteOptionEnum.

MyConn.Execute "DELETE FROM myTable WHERE someField = 100", , _
adCmdText + adExecuteNoRecords

Using adExecuteNoRecords ensure no recordset is returned, which
will make such processes significantly faster.

--
Roy-Vidar