UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Record Selection - Filter By Logged In User, Access 2016    
 
   
glennbullion
post Sep 12 2018, 03:29 PM
Post#1



Posts: 25
Joined: 12-June 15



I might not explain this correctly, but I'll try my best.

I have a continuous form with a checkbox that changes a field, [SelectRecord], from a 0 to a -1. A query then runs that selects anything with that -1, and prints a report. All works well there. On Form close, an update query runs which resets [SelectRecord] back to 0. I'm sure it's crude, but is simple enough and works. It's just a basic way for a user to select a bunch of records, print, and then "undoes" those checkboxes.

But now we have multiple users in the database at the same time. Got a call from a user that when they opened the form, there were already records checked off for printing. This is obviously because another user was in before him, and was selecting his own records. He hadn't closed the form yet, so those records still had -1 for [SelectRecord], and User B saw what User A was selecting.

Not the end of the world. User B knew what was happening, closed the database, and waited.

I'm wondering though, if there's a solid method to divide this down a bit so that User A doesn't see what User B is selecting. Copy data to temporary tables, perhaps? Any ideas?
Go to the top of the page
 
tina t
post Sep 12 2018, 04:05 PM
Post#2



Posts: 5,586
Joined: 11-November 10
From: SoCal, USA


if the records you're concerned with include a field that identified which user each record "belongs" to, then Yes you can filter the form records by user. if each user must log into the db to use it, then you need to capture the value that identifies a specific user - and is also the value stored in the data records for the same reason - and then just filter the form on opening it.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
glennbullion
post Sep 24 2018, 09:59 AM
Post#3



Posts: 25
Joined: 12-June 15



Sorry for delay, just getting back to this now.

Don't really have a field for the record "belonging" to anyone. And there is no logon. Just a front end database pointing to the backend.

I do capture the logon name to the system (active directory domain) to display a "Welcome User A" message. In my head, I'm trying to think if that could separate with a "User A selected these records" somehow.
Go to the top of the page
 
tina t
post Sep 26 2018, 05:17 PM
Post#4



Posts: 5,586
Joined: 11-November 10
From: SoCal, USA


okay, i re-read your first post; i missed the boat on what you're trying to do, but i think i got it now.

QUOTE
I'm wondering though, if there's a solid method to divide this down a bit so that User A doesn't see what User B is selecting.

your mistake here is that you included the SelectRecord field in your data table. since the data table is in the backend database (shared by all users), of course one user's checkmark(s) will be seen by all other users, and will drive the report printed by all users. also, and btw, repeatedly changing the SelectRecord fields from False to True - and back again (with the update query) - will bloat your backend database over time.

what you need instead is a either a table in the frontend database (which each user should have a copy of on his/her hard drive), or in a temporary database that is stored on each user's hard drive and with a table in it that is linked to the frontend database. putting a table in the frontend database is the easier solution, but note that records will be added to and deleted from the table repeatedly, and over time this will cause the frontend database to bloat. that's remedied easily enough by downloading a fresh copy of the frontend db to a user's hard drive, but it is something that you need to be aware of and keep in mind.

creating the table is easy enough. i'll call it tblSelect. open the data table in Design view and copy the field you're using as a primary key. open a new table in Design view and paste the copied field into the new table. note that this procedure copies/pastes the field, not the data that's in the original field. next, add a Yes/No field called SelectRecord. you can delete the SelectRecord field from the data table, but you'll need to make sure all users are out of the backend database, and preferably you should back up the backend database and then copy it to your hard drive, remove the field from the data table, compact the database, and copy it back to the hard drive. again, you want to do this when there are no users connected to the backend database. to avoid all this fuss, you can simply leave the SelectRecord field in the data table - just don't use it in the form.

next, set the relationship between the two tables in the Relationships window (or A2016's version of it), from the data table to tblSelect - you should get a one-to-one relationship between the two primary key fields of the two tables.

if your form is based directly on the data table, then create a query based on the data table, add tblSelect to the query, and link from the primary key field of the data table to the primary key field of tblSelect. you want to "select all records from the data table, and only related records from tblSelect". that's the plain English definition of a LEFT JOIN in your query. make this new query the RecordSource of your form. if your form is already based on a query, then add tblSelect to that existing query as described above. either way, make sure that there is only one SelectRecord field in the query output, and that field is from tblSelect.

all of the above may sound complicated, but once you've gone through it once you'll see that it's actually easy to set up. now when the user selects specific records in the form, s/he is adding records to tblSelect - with the primary key in each record that is tracking which records in the data table are wanted. the important point here is that tblSelect is not in the shared backend database; instead each user is working with his/her own local copy of that table.

final step is to include tblSelect in the report's RecordSource - same as described above for the form. you can filter records for the report by setting the criteria of True on field SelectRecord, again making sure you're using the SelectRecord field from tblSelect.

QUOTE
On Form close, an update query runs which resets [SelectRecord] back to 0.

oh yes, and btw, instead of using an update query on Form close, instead use a Delete query to delete all records from tblSelect. (don't delete records from the data table!) now the table's empty again, ready for the next time.

hth
tina
This post has been edited by tina t: Sep 26 2018, 05:22 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
glennbullion
post Nov 5 2018, 03:29 PM
Post#5



Posts: 25
Joined: 12-June 15



Thank you much Tina. I went through and did all of your suggestions. It does work as expected.

There is an issue that this created though, that I made another thread for. Ever since moving to this method of separating a user's selections, we get "This record has been changed. Please re-edit" messages now. For example, a users select a checkbox, but realizes he didn't want that one. He goes back to uncheck check, and gets the error message.
Go to the top of the page
 
tina t
post Nov 5 2018, 06:14 PM
Post#6



Posts: 5,586
Joined: 11-November 10
From: SoCal, USA


well, that is unexpected, at least in my experience. have you uploaded a "sanitized of sensitive data", and zipped, copy of your db for review, on the new thread you started? that's probably the quickest way to figure out what's causing the msg.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
glennbullion
post Nov 6 2018, 08:41 AM
Post#7



Posts: 25
Joined: 12-June 15



Not yet. I'll do so. smile.gif

If I slip in a "DoCmd.RefreshRecord" after the "SelectRecord" check box update, it works. But this causes some screen flickering that is driving me crazy. Always something.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2018 - 08:53 PM