Only One Instance in Table

[burrina]

I am trying to ONLY allow One instance of a value in my table.
tblUserSecurity
admn
dev

If either of these exist (They are Yes/No) then do not allow.
This is being checked via a combo box on my form using the after update event.
Only One Administrator and One Developer.
DCount or DLookup ?

 

[plog]

You really need to be more explicit in your description. I have no clue if admn and dev are fields or values in your table. Then you sort of say they are Yes/No fields, which means they will always exist.

I'm not clear if a record can be either an adm or a dev or if your entire table you will allow only one adm and one dev.

Can you explain using some data as an example?

 

[burrina]

dev and admn are Yes/No Data Types in tblUserSecurity. They are fields in the table.
They should ONLY be checked, i.e. True once in the table, meaning there can Only be one developer and one administrator.

 

[jdraw]

I'm still confused.
Usually you would have a list of people who are permitted to login, and a permissions field to identify their role.. Admin or User.

 

[burrina]

Yes, I already have that. admn,dev,data,reado,sprvsr are ALL Yes/No Data Types and the security level is setup that way. I don't want to get off topic. The user can be one of those types or none to not allow access. All of this is working as designed. I can of course limit the combo box to eliminate dev and admn but then I would need another form to allow editing this or the developer or admin would have to drop down and open the table, BAD idea.

 

[plog]

I agree with jdraw--I don't see the downside of allowing more than one person to be designated a dev or admn.

But to do what you want, I would use a DSum (http://www.techonthenet.com/access/functions/domain/dsum.php).

DSum("adm", "tblUserSecurity")

If that returns 0, then nobody is an adm, if it returns -1 then there is a user designated with those credentials. If something other than 0 or -1 is returned then your data isn't behaving like you expect it too and you should investigate how many adms you have.

 

[burrina]

Well, I would have to disagree. For my design this can't be allowed. Never should there be more than one developer, possible more than one administrator, maybe???

Simple solution for now is to eliminate those values from the combo. I will have to give this some thought.

Thanks,

 

[jdraw]

In general, a person is assigned to a role, and a role is associated with permissions. It can get complex if you decide to create your own for multiple combinations.

Attached is a generic model that may be useful to you.

 

[burrina]

I Thank You. The db works very well as designed. It does different roles for each user and their permissions are set respectively. This happens when they Log in. I am VERY clear on all of this and understand it well. My goal was to eliminate duplicate developers and maybe multiple administrators, still unsure of my thought process on this.

Thanks Again everyone for your help!

 

[vbaInet]

If you want one instance of each in your table then set that field as the Primary Key. If this is going to change in the future then leave the table as is and use a DCount() function (as you already planned).

 

[burrina]

I already have a Primary Key. It is EmpID
I tried the DSum but could not get it to work.
Maybe can't get there from here.

Anyway, problem worked around for now.

 

[vbaInet]

If you want one instance of each in your table then set that field as the Primary Key. If this is going to change in the future then leave the table as is and use a DCount() function (as you already planned).

DCount() not DSum()

 

[jdraw]

Glad you have it working.

 

[burrina]

If DCount("admn", "tblUserSecurity") > 0 Then

I need the extra value because it displays my msgbox no matter what the value of EmpType is in the combo box.

 

[vbaInet]

You've not applied any criteria.

Just looked at your screenshot. I would have thought that Dev and Admin would be values and not separate fields. Not an ideal setup.

DCount("*", "tblSecurity", "admin = Yes")

 

[vbaInet]

You might just even use a DLookup() function since there should only be one instance. If it's Null it hasn't been assigned, otherwise it's been assigned.

 

[burrina]

Your NOT seeing the whole picture is why. This is a just an Administration form, that's all.
I chose to use a Value list but could have used a DLookup or query instead.
I am abandoning this thread. Can't get it to work anyway.

Thanks Again.

 

[burrina]

Final Solution:

'Only Allow 1 Developer and Caution if more than 1 Administrator.
If DCount("*", "tblUserSecurity", "dev = -1") And (Me.EmpType) = "dev" Then
Call MsgBox("Only One Developer is Allowed in this Application.", vbExclamation, Application.Name)
Me.Undo
End If

If DCount("*", "tblUserSecurity", "admn = -1") And (Me.EmpType) = "admn" Then
Select Case MsgBox("Having more than one Administrator is not recommended, " _
& vbCrLf & "Are you Sure?" _
, vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)

Case vbYes

Case vbNo
Me.Undo

End Select
End If