populate list box programmatically

T

Toxalot

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
 
R

RoyVidar

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

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?
 
M

Maury Markowitz

Toxalot said:
I need to populate a list box with info from a remote table.
Click to expand...

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
 
T

Toxalot

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
 
T

Toxalot

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
 
R

RoyVidar

Toxalot said:
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
Click to expand...

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.
 
R

RoyVidar

Oh dear

Set Me!cboTest.Recordset = rs2

should have been

Set Me!lstTest.Recordset = rs2
 
T

Toxalot

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
 
T

Toxalot

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

Jennifer
 
R

RoyVidar

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

Jennifer
Click to expand...

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.
 
M

Maury Markowitz

Toxalot said:
There must be a limit to the size of the string.
Click to expand...

Perhaps, but it's not the combobox itself, I have data-bound combos with
15000 rows in them. So I'd guess the limit is the String itself, but I don't
really know the length limit for them.
The 5000 item list is a list of companies and used more as a way of
drilling down to a particular record.
Click to expand...

So it's basically a filter?
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.)
Click to expand...

No, I know exactly what you mean, I had to do the same thing myself. Do a
search in this forum for "scrolling to a field in a continuous form".
This is my first big Access database with FE/BE that doesn't use
simple bound forms. I'm open to suggestions.
Click to expand...

Ahh, good!

What I do, a lot, is use code to build an ADOBD recordset, and then use that
to put items into combos. If I understand your description correctly, the
data you need to put into the combo is in a database, but you can't get to it
"easily" because its in a separate one, the BE.

So try this: Get Properties on the control, set it to a table/query
datasource, and type in a query that is syntactically valid but returns no
rows... "SELECT * FROM someLocalTable WHERE 1=0".

Now add an OnOpen event handler to the form itself. In that handler do the
query you need to return the rows, and then set the RowSource of the combobox
to that query. For example...

MyCombo.RowSource = "SELECT notes FROM tblAccounts ORDER BY notes"

I use this technique mostly when the query has to change based on some other
input. That way I get tight control over the query, and don't have to rely on
Access getting the WHERE syntax correct.

Now to be honest, I've never tried this with two different data sources.
It's not clear to me that the combo won't simply assume the connection is the
default one, to the FE. But no harm in trying.

Maury
 
M

Maury Markowitz

Toxalot said:
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.
Click to expand...

Oh, don't do this in the recordset, do it in the form! Open the form in
Design Mode and turn everything to disabled, except the Edit button. Now add
code to that that simply turns on all the controls by name...

someTextField.enabled = True
someComboBox.enabled = True

Unless you have hundreds of controls, in which case you can try using the
Controls collection:

foreach aControl in me.controls
aControl.enabled=True
next aControl

The only problem with this approach is that you most likely want to leave
some off, or have controls with no Enabled at all. You have to mess up the
loop with lots of Ifs.

I also recommend having the same Edit button allow you to return to the
uneditable mode and cancel all changes. That's NOT so easy, because Access
saves out changes to the data as you type. I really needed this feature
though, so I made temp tables to hold the data while it's being edited. I
copy the row into the temp table, bind the form to that instead, and only
copy back if the user hits Save.

Maury
 
R

Rob Parker

If you're only wanting to prevent changes to data in the form, and not
disable all controls, including command buttons, then there's no need to set
the Enabled property for all the controls individually. Simply set the
AllowEdits property of the form itself to No, and then change it to Yes when
the Edit button is clicked. If you want to prevent additions and/or
deletions of records (assuming that your form's features allow you to do
so), you can also set the AllowAdditions and/or AllowDeletions properties in
the same fashion.

Rob
 
T

Toxalot

I haven't started working on the edit features yet. I'm still working
on browse features. But I think I already have this covered. I'm using
a disconnected data set. So I have to actually do an update before
changes are saved. This is my understanding so far anyway.

Jennifer
 
T

Toxalot

I had thought of doing this, but it doesn't work for my form. I'm
using a combo box as a way to choose what data to display. If I set
the form to not allow edits then I can't use that combo box.

Jennifer
 
M

Maury Markowitz

Rob Parker said:
the Enabled property for all the controls individually. Simply set the
AllowEdits property
Click to expand...

The problem with this approach is that it turns off basic interaction with
all controls. I tried this in the past, but found that it invariably it
turned off something I still wanted to work with.

Maury
 
Top