Looking for Suggestions - how to create unique updatable recordset?

[carlnewton]

I'm working with Access 2003.

I've created a database which will help to maintain test equipment. I have a subform with recordset = 'Equipment' table. This table includes a binary field titled 'Export'. The purpose of that field is to allow users to select one or more records for 'export' to a word mailmerge or to a shipper report.

In order to make each users 'export' selections unique in this multi-user environment I use the CopyObject method to copy the underlying Equipment table to a temporary new table titled 'Temp <Now()>' (within the Form_Open Event). I then set subform.recordsource = 'Temp Now()'. In this way, the original Equipment table never really has it's 'Export' field set to = True. Only the temporary table does. Each user is using a unique dataset at runtime (with the very small possibility that two users might open their front-end during the same second in time and create the same Now() suffix). I delete the Temp table from the database from within the Form_Unload event and set the subform.recordsource = 'Equipment'.

This worked fine until I split the database. The Temp table and the Equipment table seem to be linked. If I make Export = True on the temp table, that record is also updated within the Equipment table!

I don't expect to find a solution to this problem (Microsoft would call this a feature?) so I've started to search for an alternate way to create this unique recordset. My first attempt was to try and apply a client side ADO Open connection, but my first crack at it didn't work out as expected.

Do any of you have suggestions as to how I might create this unique recordset that can have this one binary field enabled\disabled within the multi-user environment? I need to be able to apply a query or sql statement that will filter the 'Export = True' records as the recordset must be passed to Access Reports and to Word Mailmerge.

Access 2003, split database.

Thanks in advance.

Carl

 

[carlnewton]

Thanks for your suggestion. It's a good one that didn't occur to me. I haven't used list boxes often but I see just what you mean.

Since posting, the only way that I could see to work-around this problem was to create an entirely new table. So I wrote some code (Form_Open event) to use the CreateTableDef and use the Create rather then the Copy methods. This way the links were broken. Pretty messy, I know. The recordset is only a couple of hundred at this time and won't grow quickly at all, so the startup time for the Form isn't bad at all.

But I like your suggestion and I think that I may put some time into that. The record selection method is somewhat less intuitive to the average pc user but it's a much more elegent design.