Recordset ??

[Christie.B]

Hello,

I am using a SQL call (see below) to fill in a list
box... I'm allowing the user to choose the fields they
want (this is actually a part of a case statment to
handle upto 4 choices). The tbl_header is linked from a
BE db.

My problem is, the usefullness of this doesnt allow a
very fast paced multi user environment. The on load
event that establishes the format of the listbox takes
about 30 seconds to load (and thats if one user is
already in the db).

How can I display what i want without tying up the
recordset for the second user??? Is there a way to
display the info without locking it down?

Thanks in advance, Christie

Here's my attempt at code:

Select Case ToggleCase
Case 1 'two headers
List0.RowSource = "SELECT tbl_header.ProjID, tbl_header.
[" & UserH2 & "] FROM tbl_header WHERE
(((tbl_header.ProjStatus)=[Forms]![frm_projectSelect]!
[cmb_StatusType]));"
List0.ColumnCount = "2"
List0.ColumnWidths = "0.0 in;" & UserW1
Case 2 ....


ToggleCase is a dlookup value for the # of listbox fields
UserH2 is the field choice of the user in tbl_A by way of
dlookup
Userw1 is a double in tbl_A by way of dlookup

 

[Christie]

Thank you Frank! dbOpenSnapshot was it!!!

Christie

-----Original Message-----
You could have the query saved so it is pre-compiled.
You can also have several list boxes formatted for each scenario present,
and make them visible as and when they are needed
But it depends where the bottle neck is . ..
How long do the queries take to run independant of the form. How long does
the form take to load without the queries. . ./??

Hello,

I am using a SQL call (see below) to fill in a list
box... I'm allowing the user to choose the fields they
want (this is actually a part of a case statment to
handle upto 4 choices). The tbl_header is linked from a
BE db.

My problem is, the usefullness of this doesnt allow a
very fast paced multi user environment. The on load
event that establishes the format of the listbox takes
about 30 seconds to load (and thats if one user is
already in the db).

How can I display what i want without tying up the
recordset for the second user??? Is there a way to
display the info without locking it down?

Thanks in advance, Christie

Here's my attempt at code:

Select Case ToggleCase
Case 1 'two headers
List0.RowSource = "SELECT tbl_header.ProjID, tbl_header.
[" & UserH2 & "] FROM tbl_header WHERE
(((tbl_header.ProjStatus)=[Forms]![frm_projectSelect]!
[cmb_StatusType]));"
List0.ColumnCount = "2"
List0.ColumnWidths = "0.0 in;" & UserW1
Case 2 ....


ToggleCase is a dlookup value for the # of listbox fields
UserH2 is the field choice of the user in tbl_A by way of
dlookup
Userw1 is a double in tbl_A by way of dlookup

.

 

[Tim Ferguson]

SELECT tbl_header.ProjID,
tbl_header.[" & UserH2 & "]
FROM tbl_header WHERE
(((tbl_header.ProjStatus)=
[Forms]![frm_projectSelect]![cmb_StatusType]));

This is a nasty query, suggesting that tbl_header has several columns with
duplicated UserH2-type names

tbl_header(ProjID, ProjStatus, Eric, Nancy, Sam, George, Wills)

I vote for normalising it and taking the substitition out of the query.

All the best


Tim F