Writing directly to the status bar

[Leif Eriksen]

I would like to keep the user informed on the status on
some long running updates. I would also like to use the
status bar.

Is there a way to do the update directly? I probably
could have a update the statusbartext property of a
control to update the display, but is there a way to do
that more directly through VBA?

Regards,
Leif

 

[Dirk Goldgar]

I would like to keep the user informed on the status on
some long running updates. I would also like to use the
status bar.

Is there a way to do the update directly? I probably
could have a update the statusbartext property of a
control to update the display, but is there a way to do
that more directly through VBA?

Regards,
Leif

To set the status bar text:

SysCmd acSysCmdSetStatus, "Your text here"

To clear it:

SysCmd acSysCmdClearStatus

 

[Leif Eriksen]

Thanks, that worked perfectly.

I have another question, on a another topic.

I've set up a form to allow users to select rows for
printing. I added a print (boolean) column to the table
and a check box to the form. Once they execute a report,
using a command button on the form, the report grabs the
selected records. That part works fine.

However, I'm encountering problems. When the user clicks
the print button that locks the record until the user
clicks the print button. In addition, since multiple
people may be viewing and printing information, the check
boxes from other users are showing up on other users
reports.

I cannot use an unbound control for print, since clicking
once check marks all the records. The only other way I
can think of is to set up another table, with the PK of
the original table, the user ID, and the print boolean
column. However, this seems ackward and difficult to
implement. Do you have any ideas on this? I would think
this might be a semi-common function for applications.

Thanks,
Leif

 

[Dirk Goldgar]

Thanks, that worked perfectly.

I have another question, on a another topic.

You really should have posted it as a new message, not as a reply.
However ...

I've set up a form to allow users to select rows for
printing. I added a print (boolean) column to the table
and a check box to the form. Once they execute a report,
using a command button on the form, the report grabs the
selected records. That part works fine.

However, I'm encountering problems. When the user clicks
the print button that locks the record until the user
clicks the print button.

I can't quite make sense of that. What do you mean?

In addition, since multiple
people may be viewing and printing information, the check
boxes from other users are showing up on other users
reports.

I cannot use an unbound control for print, since clicking
once check marks all the records. The only other way I
can think of is to set up another table, with the PK of
the original table, the user ID, and the print boolean
column. However, this seems ackward and difficult to
implement. Do you have any ideas on this? I would think
this might be a semi-common function for applications.

There are several ways you can go about this. If the number of records
is relatively small, you could use a multiselect list box to present the
records, letting the user select the records to be printed by clicking
them in the list box. Then your print button would loop through the
list box's ItemsSelected collection and build an "In"-list -- as in
"RecordID In (1, 3, 14, 36, 99)" -- to pass as a criterion to the
report.

Another way is to have the "SelectedRecords" table on the user's local
machine. If this is a multi-user database, it really should be split
into front-end (a copy on each user's workstation) and back-end (data
tables in a shared file on the network). In this scenario, although
almost all tables would actually reside in the back-end, the
"SelectedRecords" table would be in the front-end, with each record
containing two fields: RecordID (FK to the main table) and IsSelected, a
boolean field. Your selection form would then be based on a query that
joins this table to the main table, and the report would include join
the SelectedRecords to the original recordsource query so as to limit
the records to only those where IsSelected is True.

 

[Leif Eriksen]

-----Original Message-----


You really should have posted it as a new message, not

as a reply.

OK, next time...

However ...


I can't quite make sense of that. What do you mean?

Because the print field has been changed (from unmarked
to marked), and the print field is part of the larger
table, that record is locked until the user navigates to
another record or clicks the command button to execute
the print.

There are several ways you can go about this. If the number of records
is relatively small, you could use a multiselect list box to present the
records, letting the user select the records to be printed by clicking
them in the list box. Then your print button would loop through the
list box's ItemsSelected collection and build an "In"- list -- as in
"RecordID In (1, 3, 14, 36, 99)" -- to pass as a criterion to the
report.

My GUI is showing the records as a datasheet. The
easiest way for the user to work is to have a check box,
to the left of the datasheet row, that indicates the user
whats that row reported.

Another way is to have the "SelectedRecords" table on the user's local
machine. If this is a multi-user database, it really should be split
into front-end (a copy on each user's workstation) and back-end (data
tables in a shared file on the network). In this scenario, although
almost all tables would actually reside in the back-end, the
"SelectedRecords" table would be in the front-end, with each record
containing two fields: RecordID (FK to the main table) and IsSelected, a
boolean field. Your selection form would then be based on a query that
joins this table to the main table, and the report would include join
the SelectedRecords to the original recordsource query so as to limit
the records to only those where IsSelected is True.

I do not use a back end / front end. I've tried it, and
its too slow. Even using some of the tricks like
maintaining a constant connection, I found its too slow.

However, regardless of whether I did it local or on the
file server I still need to maintain this table (i.e.,
delete records when the user exits or changes units. If
I did that on the server I would need to add a user id to
the table.

Is there a way to keep the data in variables, or create a
record set that is not based on a table? Can I write the
information to a in memory structure? Oracle, using
PL/SQL allows a user to do this using table and record
types, which can be based on the structure (fields) of a
table. Does something like that exist in Access/VBA?
Even if I could, can I then display the information in a
datasheet/form?

Regards,
Leif

 

[Dirk Goldgar]

[I wrote:]

I can't quite make sense of that. What do you mean?

Because the print field has been changed (from unmarked
to marked), and the print field is part of the larger
table, that record is locked until the user navigates to
another record or clicks the command button to execute
the print.

Yes, that would be true. You could, of course, force an immediate save
in the check box's AfterUpdate event. But that doesn't solve the larger
problem.

My GUI is showing the records as a datasheet. The
easiest way for the user to work is to have a check box,
to the left of the datasheet row, that indicates the user
whats that row reported.

Suit yourself.

I do not use a back end / front end. I've tried it, and
its too slow. Even using some of the tricks like
maintaining a constant connection, I found its too slow.

I really don't see why the FE/BE setup would be slower than having
multiple users working in the same monolithic database file. There is
simply no reason for it to be any slower. The same data has to go
across the network to each user, and the same locking has to occur.
Furthermore, with the monolithic arrangement, all the design information
for forms, reports, and queries has to go across the network, too -- and
incidentally leads to a higher incidence of corruption.

I strongly urge you to revisit this question, and try to figure out why
your FE/BE arrangement is too slow. Maintaining the constant connection
to the back-end reliably fixes the only performance issue I know of with
this arrangement. Have you by any chance seen Tony Toews' Access
Performance FAQ page:
http://www.granite.ab.ca/access/performancefaq.htm ?

However, regardless of whether I did it local or on the
file server I still need to maintain this table (i.e.,
delete records when the user exits or changes units. If
I did that on the server I would need to add a user id to
the table.

Which is why I say this table should be on the local workstation. If
you have a FE/BE arrangement, you can put the table in the front-end
with very little bother. Clean-up code triggered by some suitable event
can empty the table by running a delete query.

If you insist on using a monolithic database, you can still use a local
table, by creating a temporary database file to hold this table. You
could create this database (in the current user's Temp folder), and
create the table in the database, in your startup code, and delete the
database in code that runs when the database is closed. Creating a
temporary "work" database is not difficult at all -- I have a simple
class that takes care of it for me, when I need to do it. Let me know
if you're interested. It could get complicated in this case, because
you must avoid creating a linked tabledef in the (monolithic) database,
but you could dynamically set the recordsource of your form to a SQL
statement that uses the IN clause to identify the work database that is
the source for the SelectedRecords table.

Is there a way to keep the data in variables, or create a
record set that is not based on a table? Can I write the
information to a in memory structure? Oracle, using
PL/SQL allows a user to do this using table and record
types, which can be based on the structure (fields) of a
table. Does something like that exist in Access/VBA?
Even if I could, can I then display the information in a
datasheet/form?

You *may* be able to create a disconnected ADO recordset and bind a form
to it, but I *think* such a recordset will be read-only. I could be
wrong about that. Aside from that, I can't think of any way you can
bind a form to a structure that exists only in memory, with no
underlying physical storage.

No, wait. I can imagine a global Collection of primary keys, and a
query with a calculated field that calls a function that returns True if
the current record's key is in the collection, false if not. The check
box on your form would be bound to this calculated field. Then code in
the Click event of the check box would add the record's key to the
collection, or remove it from the collection, depending on the current
state of the check box, and after doing that would refresh the record.
That might work. It seems rather fragile to me, though.

 

[Leif Eriksen]

Thanks Dirk.

-----Original Message-----

[I wrote:]

I can't quite make sense of that. What do you mean?

Because the print field has been changed (from unmarked
to marked), and the print field is part of the larger
table, that record is locked until the user navigates to
another record or clicks the command button to execute
the print.

Yes, that would be true. You could, of course, force an immediate save
in the check box's AfterUpdate event. But that doesn't solve the larger
problem.

My GUI is showing the records as a datasheet. The
easiest way for the user to work is to have a check box,
to the left of the datasheet row, that indicates the user
whats that row reported.

Suit yourself.

I do not use a back end / front end. I've tried it, and
its too slow. Even using some of the tricks like
maintaining a constant connection, I found its too slow.

I really don't see why the FE/BE setup would be slower than having
multiple users working in the same monolithic database file. There is
simply no reason for it to be any slower. The same data has to go
across the network to each user, and the same locking has to occur.
Furthermore, with the monolithic arrangement, all the design information
for forms, reports, and queries has to go across the network, too -- and
incidentally leads to a higher incidence of corruption.

I strongly urge you to revisit this question, and try to figure out why
your FE/BE arrangement is too slow. Maintaining the constant connection
to the back-end reliably fixes the only performance issue I know of with
this arrangement. Have you by any chance seen Tony Toews' Access
Performance FAQ page:
http://www.granite.ab.ca/access/performancefaq.htm ?

Yes, I have read Tony's site, along with a couple of
articles. I did try their ideas. I mostly notice a
problem on a datasheet refresh, or a resize of a form. I
have an event on a resize that adjusts the size of certain
parts of the form. Right now its near real time response,
with the link it was noticably slower. However, several
people have mentioned the advantages, so I think I should
try it again. Of course, I then have the additional
problem of keeping the front-end in sync with changes if
the front end is local. I recall someone, however, has a
freeware/shareware application that helps helps in syncing
up front end changes.

Which is why I say this table should be on the local workstation. If
you have a FE/BE arrangement, you can put the table in the front-end
with very little bother. Clean-up code triggered by some suitable event
can empty the table by running a delete query.

If you insist on using a monolithic database, you can still use a local
table, by creating a temporary database file to hold this table. You
could create this database (in the current user's Temp folder), and
create the table in the database, in your startup code, and delete the
database in code that runs when the database is closed. Creating a
temporary "work" database is not difficult at all -- I have a simple
class that takes care of it for me, when I need to do it. Let me know
if you're interested. It could get complicated in this case, because
you must avoid creating a linked tabledef in the (monolithic) database,
but you could dynamically set the recordsource of your form to a SQL
statement that uses the IN clause to identify the work database that is
the source for the SelectedRecords table.

Yes, I'm interesting in your class. You can email me at
LeifEriksen AT Hotmail (I didn't want to use the standard
way of displaying this and take a chance of even more
spam).

You *may* be able to create a disconnected ADO recordset and bind a form
to it, but I *think* such a recordset will be read-only. I could be
wrong about that. Aside from that, I can't think of any way you can
bind a form to a structure that exists only in memory, with no
underlying physical storage.

No, wait. I can imagine a global Collection of primary keys, and a
query with a calculated field that calls a function that returns True if
the current record's key is in the collection, false if not. The check
box on your form would be bound to this calculated field. Then code in
the Click event of the check box would add the record's key to the
collection, or remove it from the collection, depending on the current
state of the check box, and after doing that would refresh the record.
That might work. It seems rather fragile to me, though.

Clever. I'll experiment.

 

[Dirk Goldgar]

[...]
Yes, I have read Tony's site, along with a couple of
articles. I did try their ideas. I mostly notice a
problem on a datasheet refresh, or a resize of a form. I
have an event on a resize that adjusts the size of certain
parts of the form. Right now its near real time response,
with the link it was noticably slower. However, several
people have mentioned the advantages, so I think I should
try it again.

Hmm, I don't recall hearing of this as a problem before. I'd have to
have more specifics before I could even guess what's going on, or
whether this is a bug of some sort.

Of course, I then have the additional
problem of keeping the front-end in sync with changes if
the front end is local. I recall someone, however, has a
freeware/shareware application that helps helps in syncing
up front end changes.

Somewhere on Tony Toews' site there's a link to his Auto FE Updater.

Yes, I'm interesting in your class. You can email me at
LeifEriksen AT Hotmail (I didn't want to use the standard
way of displaying this and take a chance of even more
spam).

I'll send it along.