How to have AutoFilter vba code (1 Viewer)

[Rakesh935]

Hello,

I have a form dirrectly linked to a table.

The table has a particular column which has only unique Ids (Lets say the column name as "ABC"), to be shared and processed by multiple users later on at the same time.

All I want to do is execute a autofilter vba code on column "ABC" so that all the users must see the entire row data of a unique id and none of the users should get the same record.

In other words, if one user received a row item (record) based upon the unique ids for process then the same record must not come to any other user for process.

Thank you,
Rakesh

 

[Isskint]

Do you need this to happen every time the record is accessed or just 1 time? Do you want only the first user to access the record to be able to subsequently edit it?

 

[Rakesh935]

Hi Isskint,

Thanks for the revert...

Just for the knowledge i have 30k data which needs to get processed by around 10 users at the same.

Hence, for the first question I believe the autofilter should happen everytime.

And from the user prospective, all the users will open the access file at the same time to process the record and all the users should get 1 record to process and once after completion of the record they should get automatically get one more new record from the table to form for process without duplicaton between multiple users.

Hope I am able to explain you about the requirement.

Thank you
Rakesh

 

[marlan]

Hi,
It sounds like you need a special process to manege the work.
how about:
1. Add a Boollian field named "Processed" - False by default.
2. Have the form_Load method update the field in the current record to "True".
3. Link the form to a Query, that Selects the record "WHERE Processed = False"
that way the form only opens records never opend befor.

Hope I understud the question, hope I got the right answer...

Good luck!

 

[Rakesh935]

Hello marlan,

I did the same way as you instructed but unfortunatly i figured out that all the users are getting the same record for process at the same time.

Hence, in order to avoid that I thought may be if i can execute the autofilter then my problem would get ressolved.

Hence, requesting if you could suggest me for some alternatives...

Thank you,
Rakesh

 

[Isskint]

Hi,
It sounds like you need a special process to manege the work.
how about:
1. Add a Boollian field named "Processed" - False by default.
2. Have the form_Load method update the field in the current record to "True".
3. Link the form to a Query, that Selects the record "WHERE Processed = False"
that way the form only opens records never opend befor.

Hope I understud the question, hope I got the right answer...

Good luck!

yeah that is where i was sort of heading marlan, however Rakesh does not need this. You just need to set the forms Record Locks property to Edited Record. This way when a second user accesses a record he will be unable to edit. Now this will only stop them editing (and give them a very vague MS error message). If you want a friendlier method you could add a boolean field to your table (EDITING), then in your form OnCurrent event check the boolean value and if FALSE set TRUE or inform the user that the record is being edited and move to the next record. You would also need to capture the moment the user finishes editing the record to set the flag back to FALSE.

 

[marlan]

Hi,
I could think of 2 resons:
1. the update works to late.
2. the filter is applied to early...
If all users get the same record the first time the form is opend, that could point out to option 1. I couldn't help you on this.
but, if not - the I guess it is option 2. in this case...
(I'm not 100% about this A form when linked to a table or query, actualy has a DAO.RecordSet (referd to by Me.RecordSet ), wich has all the data in it. You could try adding to the On_Current Method Me.Requry or Me.Refresh (look it up). It may slow down the moving to the next record, but should do the job.