Is it possible to control what records a user sees on a split form? E.g. Only their record entries (1 Viewer)

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
Just wondering if there is a way to set permissions or some VBA that will render records viewable and editable only to the user that has created it on the split form?

Currently I have it set as a single form. But surely there's a way of doing the above mentioned? My goal is that I don't want users to be prying eyes on others records. But I still want them to be able to view their records and make changes if required.

In saying that, if that is possible, is there also a way to make those records viewable but not editable? In case I don't want them making changes but they can still view their work in case they need to. Just deciding which would be better for my database. Thanks.

P.S. I am receiving Computer Name and their Username via Environ code. I know this can be spoofed, so will work on WShell.Network module. But just wondering if that information could help with the above?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
13,739
Hi. If you're saying each user should only see their own records, then that's very possible even for a split form. All you have to do is use a filtered query as the record source of the form. The criteria should be their username.
 

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
Hi. If you're saying each user should only see their own records, then that's very possible even for a split form. All you have to do is use a filtered query as the record source of the form. The criteria should be their username.
Oh ok, so my form's record source is to a table, so how does this work exactly?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
13,739
Oh ok, so my form's record source is to a table, so how does this work exactly?
One way is to go to your form's design view. Go to the Data tab. In the Record Source property, click on the three dots to the right. Click 'Yes' to the popup prompt. The query designer should open. Drag the asterisk from the table to the query grid. Drag the username field to the query grid and add a criteria for Environ("username") and uncheck the Show checkbox.
 

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
One way is to go to your form's design view. Go to the Data tab. In the Record Source property, click on the three dots to the right. Click 'Yes' to the popup prompt. The query designer should open. Drag the asterisk from the table to the query grid. Drag the username field to the query grid and add a criteria for Environ("username") and uncheck the Show checkbox.
1604282254857.png


How do I define this?
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
11,146
you need to add Field to your Table/Tables and save the Username on that field on every New Record.
now, on the form, you use the Current Event of the form to Enable/Disable Edits/Deletion of the record
if the field (that holds the username) is not the same as the user.

create a query that will include this field and use this query as the rowsource of your form.
add the field to your form (visible=no) and on the beforeUpdate event, insert the username to this textbox.

on the current event:

private sub form_current
with me
.AllowEdits = (![txtUserName] = Tempvars!tvarUserName)
.AllowDeletions = (![txtUserName] = Tempvars!tvarUserName)
end with
end sub
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
11,146
on post#5 and "=" to your criteria:

=Environ("username")
 

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
you need to add Field to your Table/Tables and save the Username on that field on every New Record.
now, on the form, you use the Current Event of the form to Enable/Disable Edits/Deletion of the record
if the field (that holds the username) is not the same as the user.

create a query that will include this field and use this query as the rowsource of your form.
add the field to your form (visible=no) and on the beforeUpdate event, insert the username to this textbox.

on the current event:

private sub form_current
with me
.AllowEdits = (![txtUserName] = Tempvars!tvarUserName)
.AllowDeletions = (![txtUserName] = Tempvars!tvarUserName)
end with
end sub
So I have a username field on my form (CreatedBy). On the click event of my 'New Record' button, I've written:

Code:
Private Sub cmdNewRecord_Click()
If Me.NewRecord Then
Me.CreatedBy = Environ("username")
End If
End Sub

So whenever an user creates a new record, the username field automatically fills on the form. Is that what your first sentence means?

After this, the next step is to invoke a query for the form's rowsource. Do what I showed in Post#5 but use =Environ("username")

And then to do the deletions and edits, use the form_current event?

Correct?
 

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
In your original post, you said the following:

Use that same code in your query criteria.

PS. You could also try this code for grabbing the username.
Yep I've now changed my Environ("username") to a module with the WShell.Network code. Declared the function to GetUserName() As String. Specified my text field to = GetUserName(). Tested it and that's working fine with the form.

Now I've invoked the query for the record source of the form.

The query design has the * of the table and the field that stores the username. The criteria for that column is =GetUsername(). I have unchecked the box for that column as well.

The query didn't give an error.

Now to test everything. I created a few records as a test. As it's a splitform. I could see all of them in the datasheet at the bottom. Suddenly my database closes. I open it up and the records are not viewable on the split form. I've checked and the records are in the table, so no issues there. But how come I can't see them on the form anymore?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
13,739
Yep I've now changed my Environ("username") to a module with the WShell.Network code. Set it to the variable GetUserName(). Specified my text field to = GetUserName(). Tested it and that's working fine with the form.

Now I've invoked the query for the record source of the form.

The query design has the * of the table and the field that stores the username. The criteria for that column is =GetUsername(). I have unchecked the box for that column as well.

The query didn't give an error.

Now to test everything. I created a few records as a test. As it's a splitform. I could see all of them in the datasheet at the bottom. Suddenly my database closes. I open it up and the records are not viewable on the split form. I've checked and the records are in the table, so no issues there. But how come I can't see them on the form anymore?
If you created a few records, they must all have your username in the createdby field, correct?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:50
Joined
Jan 20, 2009
Messages
12,172
Best avoid doing anything funky on a Split Form. They have serious limitations.

You can emulate a Split Form with subforms and retain all the automation capacity.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
13,739
Yep that's correct. All of them have my username in the CreatedBy field.
Okay. Go back to the query and add a new column as User: GetUsername().

Remove the criteria and then run the query. Do the usernames match?
 
Last edited:

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
Ikay. Go back to the query and add a new column as User: GetUsername().

Remove the criteria and then run the query. Do the usernames match?
Done, but it's doing the same thing. Ok so what I'm understanding from the issue, is that the records can be viewed on the split form after they are made. But when I close the form or database, the splitform shows as blank and the records created are no longer viewable.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
13,739
Done, but it's doing the same thing. Ok so what I'm understanding from the issue, is that the records can be viewed on the split form after they are made. But when I close the form or database, the splitform shows as blank and the records created are no longer viewable.
Hi. It's doing what same thing. I told you to remove the criteria from the query, did you do that? The purpose of adding the calculated column was to compare what's in the records to the one returned by your function to make sure they are the same. You should see all the records.
 

Sampoline

Member
Local time
Tomorrow, 04:50
Joined
Oct 19, 2020
Messages
119
Sorry, so I got rid of the entire CreatedBy column that was created earlier and created the one you have asked with User: GetUsername() so there is no criteria specified.

When I run the query, it shows me the all the records.

What I meant by same thing, was it was showing me the records in the splitform even when we created the previous query with the column CreatedBy and the criteria =GetUserName(), but they would disappear if I closed the form or database. This is also happening with the new column you've asked me to create instead. I don't want those records to disappear from the form when it is closed.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
11,146
sample db for you.
 

Attachments

  • db1.zip
    94.6 KB · Views: 15

arnelgp

error reading drive A:
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
11,146
another sample. Only "His" record will be showing.
 

Attachments

  • db1.zip
    125.6 KB · Views: 16

Users who are viewing this thread

Top Bottom