Filling ListBox from Recordset

[David Paynter]

I am attempting to fill a ListBox from a recordset which has been generated
from a DB that has been opened within a Workspace/Transaction. However,
setting the RowSource property to the table containing the records (which
have just been appended, but not yet committed) does not load these records
into the Listbox. I assume that this is because the ListBox is looking at
the same table, but accessing it directly and not within the workspace.

I can generate a recordset using code to hold these records, but can't
transfer this into the Listbox? Is this possible?

Is another control (that will automatically define columns, etc) more
appropriate to display the records on a Form? How would this be implemented?

 

[Graham R Seach]

David,

The ListBox can't see the records inside the transaction because they don't
exist outside the transaction yet.

It is a bad idea to do anything with the UI whilst holding a transaction
open. Commit or rollback the transaction - then work with the UI.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[David Paynter]

Hi Graham

Thanks for the response. The rationale for interacting with the user while
within the transaction is to enable the user to check the appended records
before committing them. So I need an object to place on the form to present
the recordset of pending records, without having to design a specific form.

The easiest would bethe ability to programmatically transfer the contents of
the recordset to the UI object (eg ListBox) on the form.

Any suggestions? DBListBox?? FlexGrid?? Subform?? Save RS as table then
point Listbox to it (messy)???

Thanks

David

 

[Graham R Seach]

David,

No, nothing outside the transaction can see the changes, because they only
exist in a hidden (and secured) temp file; they don't actually exist in the
database until they are committed.

My only suggestion would be to chuck the proposed changes into a temp table,
and show *that* to the user. If the user opts to "commit" the changes, run
extra code (within a tranaction) to make the changes to the "real" table.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[David C. Holley]

Why not move the approval to before the process begins by providing the
user with a checkbox or something that flags record as OK to append and
then run a query selecting the records with the flags set to TRUE?

David H