Access DB - How to have records approved before others can see them

[Stuart]

Hi there,

I have a database where someone enters data onto the database.

I dont want anyone else to see the records until they have been
approved by someone who is a member of the admins group.

I dont know where to start with this. The database has already been
created so this is the only part missing.

Hopefully someone can provide a quick response.

Thank you in advance,

Stuart

 

[Guest]

Add a boolean (Yes/No) field to the table and name it something like
blnApproved. Set the default value to 0 (false). On the form that you wish to
display approved records only, add this new field as a criteria to a query
that serves as the form's recordsource. You don't need to check the Show box,
but you do need to set the criteria to -1 (or True, or even better yet, <> 0).

For your Admin people, you will need to provide a different form that does
not apply this criteria. Alternatively, you could use VBA code to
conditionally include such records in the recordset, but that's getting more
advanced than I suspect you are willing to deal with.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Stuart]

Hi Tom,

i dont know if this does exactly what I want it to do.

I want one person to enter a record entry.

I want want that record entry approved by someone in the admins group
before the record can be seen in reports or anywhere else.

Is this possible?

I'm quite new to access so detailed explanations would be appreciated.

thanks in advance,

Stuart

 

[Guest]

Hi Stuart,

I want one person to enter a record entry.

Set the Data Entry property on a copy of the form to Yes. Have this person
use this form. That way, they will only see new records that they add, until
they close the form. The next time they open the form, they will only see
record 1 of 1. You can use VBA code to set open the form in Data Entry mode
on-the-fly, depending on who the user is, but that's an advanced technique
that is beyond the scope of this issue for now.

I want want that record entry approved by someone in the admins group
before the record can be seen in reports or anywhere else.

Is this possible?

Yes, it is possible. Your reports need to include a similar criteria in the
underlying recordsource to only include records where blnApproved <> 0. The
form that you present to your Admins needs to include a checkbox control that
is bound to the blnApproved field, so that they can approve a record. In
fact, you might even include the exact opposite criteria for the Admins form:
to only include records where blnApproved = 0. That way, their form would
only display those records that need to be approved.

There's lots more that you can do as well if you are willing to dabble in
some VBA code, such as recording Who approved the record, the date and time
that the record was approved, and even the computer that was used when the
record was approved.



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Stuart]

well can you go into detail about the VBA code?

I will try and work on the adding the approved records.

Thanks

Stuart

 

[Guest]

No, because I would likely have to write a couple of chapters minimum. I
simply don't have that kind of time available. However, I am willing to send
you a sample database that shows similar functionality. It would be up to you
to "reverse engineer" this sample to see how it worked.

If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

For your Admin people, you will need to provide a different form
that does not apply this criteria. Alternatively, you could use
VBA code to conditionally include such records in the recordset,
but that's getting more advanced than I suspect you are willing to
deal with.

It's pretty simple. All you'd need to do is have a function that
checks if someone is a member of the administrative group, and if
so, return *, and if not, return True. Then put that as the
criterion in your recordsource for the APPROVED field.

That function would be most efficient it if used a Static variable,
something like this:

Public Function IsAdmin() As Variant
Static strCurrentUser as String
Static bolIsAdmin As Boolean

If Len(strCurrentUser) = 0 Then
strCurrentUser = CurrentUser()
bolIsAdmin = IsGroupMember(strCurrentUser, "Admin")
End If
IsAdmin = bolIsAdmin
End Function

Public Function IsGroupMember(strUser As String, _
strGroup As String) As Boolean
Dim varGroup As Variant
Dim bolIsGroupMember As Boolean

For Each varGroup in DBEngine(0).Users(strUser).Groups
If varGroup.Name = strGroup Then
bolIsGroupMember = True
Exit For
End If
Next varGroup
Set varGroup = Nothing

IsGroupMember = bolIsGroupMember
End Function

You'd perhaps want to add error handling for the case where you pass
a user name that's invalid, but if you're getting it from
CurrentUser(), it can never go wrong.

Of course, if you're not using Jet user-level security, none of this
will work. You could use the Windows logon, but then you need some
group authority, and I haven't as yet figured out how to use Active
Directory in Access to be able to get that kind of information. It's
easy to get security groups, but not so easy to get AD
Organizational Units, which often tell you more than the security
groups (for instance, in an organization with multiple sites,
everyone will be in the Users group, but each person will likely be
in a particular Organizational Unit -- you likely would want to know
the OU more often than the group).

If anyone has VBA code for getting OU's from AD, I'd be thrilled to
see it. The examples on MSDN just confuse the hell out of me.