forms - Accesslevel

[Sheba]

Hey Steve,



I didn't have access over the weekend and I appreciate your taking the time to analyse the issues with my program. thank you so much
Please see my response to your questions:

1) How many Access levels do you have? Four levels of access
2) Is it a fixed number Access levels or will/can the number of access levels change? For now it is fixed and I don't see this changing until possibly much, much later - when the BE is converted to SQL Server

3) How many Categories are there? There are 10 categories and each one has a unique form. Right now I have created only four forms and testing the program with them.
4) Will/can the number increase/decrease?

5) How do the access levels and categories relate? There are 6 potential users. All of them will have Data Entry access to the forms. None of them should be able to delete any record.

6) If I have an AccessLevelID of 4 and a CategoryID of "PD02", does that mean that I can't enter data for "CR03"? Correct! Four users will have DE access exclusively to one form each. The remaining 2 users will have exclusive DE access to two forms each.

[Sheba]

OK, problems.....

1) Look up fields in the tables. IMO, a major waste of time. Not sure how I came about the Lookup fields p'rarps because the users in my User table has been assigned access?!
I initially had 5 access levels (Admin, Dev, Supervisor, DataEntry, Reader) but realised that I didn't need "supervisor" and deleted it. I also removed Developer just in the version I uploaded for you because I was testing for DataEntry access. The ID column is autonumber and due to these deletions, the accessLevelIDs are not in a logical sequence.

The Ten Commandments of Access
See: http://access.mvps.org/access/tencommandments.htm

and The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm


2) The PK for the table "Category" is a text field. All of my PK fields are Long Integers (actually Autonumbers). There are issues with using text fields as the PK. (or is it just me??) okay this has been noted. I guess I'll have to change the data type to number and get rid of the "OB01", "PD02", etc. then...

3) As a rule, I do not use the data validation in tables. I validate in the controls and forms. Easier because I can handle the error however I see fit, not just get an error . I do not allow anyone access to tables for any reason. And I very rarely (almost never) use a form in datasheet mode. I do "create" my own datasheet display using a form in continuous view with control packed tightly together. Again, for controlling data entry and validation. (issues with control.... Why, yes.. is that bad???)
okay but for the form, the "Datasheet view" property is set to No...

[Sheba]

Code:

The openform command is looking for a filter named "acSaveNo". Doesn't throw an error, but is the wrong argument. You have this code: DoCmd.Close acForm, "PaediatricForm", acSaveNo
Answer: I have replaced it with DoCmd.OpenForm "PaediatricForm", , , acFormAdd
DoCmd.close acForm, "PaediatricForm", acSaveYes

and the biggie:
5) Right now it looks like it is the DELETE macro that is causing the problems. I never use macros so I am having to convert the macro to VBA. Then do more testing.... many thanks

[Sheba]

hey Steve, I've deleted the command "GoTo Control". (=[Screen].[PreviousControl].[Name]) in the delete macro....

Access has a bad habit of naming bound controls the same name as the control source. I renamed the subform control to "sfPaediatric". Then I added 3 lines
Question: Not sure I understand this part, but the delete action will be attempted from the main form not the subform...

[June7]

A subform/subreport is created by placing subform/subreport container control on a form and setting the control's SourceObject property. The SourceObject can be table, query, form, report. If built with wizard, it will name the container the same as the SourceObject. Like Steve, I always name controls different from the field or object they are bound to.

[ssanfu]

DoCmd.close acForm, "PaediatricForm", acSaveYes

Let me repeat....
"acSaveYes" does not save data. It only applies IF the form was opened by code in DESIGN view.
"acSaveYes" means save the changes made in the design of the form..
Closing the form automatically saves data if data was changed or added. (Default behavior)

I guess I'll have to change the data type to number and get rid of the "OB01", "PD02", etc. then...

Not necessarily. Add an autonumber field to the "Category" table as the PK. Change any FK fields in other tables to Longs.
The reason I was asking about the number of Access levels/Catagories is because I was thinking SELECT CASE construct would be better than a lot of IF statements....
Is there an "OB02" and "OB03"?

The ID column is autonumber and due to these deletions, the accessLevelIDs are not in a logical sequence.

Doesn't matter if the ID (PK) is in logical sequence. It is an autonumber - not meant to mean anything in the real world... just be unique so the records can be linked in a PK/FK relationship.

There are 6 potential users. All of them will have Data Entry access to the forms. None of them should be able to delete any record.

Then, in design view, set "Allow Deletes" to FALSE for the main form and the subForm. Problem solved. If someone NEEDS to delete a record, use code to set "Allow Deletes" to TRUE.

Question: Not sure I understand this part, but the delete action will be attempted from the main form not the subform...

When you drag a form (sub form) onto another form (main form), Access names the subform control the same name as the subform. This gets confusing....(at least to me).
I rename the controls on forms/reports - when adding a text box, combo box, list box.
If I have fields "LastName" and "FirstName" Access will name the control "LastName", "FirstName". I rename then something like "txtLastName"/"txtFirstName".
Access names a combo box Combo36 - I change the name that has more meaning: "cboCatagory".
A list box control name changes from "List10" to "lstEmployees".
For me, it is easier to understand whether I am referring to a field or control.
So I renamed "Paediatricsubform" to "sfPaediatric".

[Sheba]

Many thanks for that June7 and Steve

1) okay I'll replace with DoCmd.close acForm, "formName" wherever it applies
2) No, there is no "OB02" or "OB03". Just OB01
3) I have set property AllowDeletions to "No". That means I'll use code enable the Admin or developer do this.
4) Yes it confuses me too. It makes good sense to give the subform a different name.

I'm making the changes and will repost an update

[Sheba]

okay, so I made the changes and have the following in my form's OnOpen event. I have similar code for Developer, DataEntrant and ReadOnly users but with slight changes. Sigh!...on the first line of code, I am getting error 3464: data type mismatch in criteria expression I'm so looking forward to a vacation right now.

"admn" is a field in tblUser with Yes/No data type
"userID" is also a field in the table but with number data type
frmLogin is my login form and it has a combo box with names of the users (I'll change this to a textbox later on)

Your help is invaluable to me.

'****If user has Administrator access level**************
If DLookup("admn", "tblUser", "userID='" & Forms!frmLogin.cmbUser.Value & "'") = True Then

With Me

.DataEntry = True

.AllowEdits = True
.AllowDeletions = True
.AllowAdditions = True
.AllowFilters = True

End With

End If

[ssanfu]

"userID" is also a field in the table but with number data type

Since "userID" is numeric, it does not require delimiters.


If the bound column of the combo box "cmbUser" is numeric, try this

If DLookup("admn", "tblUser", "userID= " & Forms!frmLogin.cmbUser) = True Then

Dates require the # delimiter.
Text uses single or double quotes as delimiters.


BTW, it is not necessary to add ".Value" because ".Value" is the default property. It doesn't hurt but you can save some typing.

[Sheba]

okay, I'll try this. Thank you.

[Sheba]

hey Steve, I made the changes and got error 2465. Please see the attachment.

[June7]

The literal text for the fieldname argument must be in quotes: "[categoryID]"

[Sheba]

brilliant! thank you June7. Now, to figure out why it is telling me I'm not authorized to look at the form!

[June7]

Sounds like more step debugging.

[Sheba]

okay this program is not allowing me to access the form even though I used the correct user login. Your help is greatly appreciated

'****If user has Administrator access level**************

If DLookup("admn", "tblUser", "userID= " & Forms!frmLogin.cmbUser) = True Then

With Me

.DataEntry = True
.AllowEdits = True
.AllowDeletions = True
.AllowAdditions = True
.AllowFilters = True

End With

End If


'****If user has Data Entry access level*****************

If DLookup("data", "tblUser", "userID= " & Forms!frmLogin.cmbUser) = True And

If DLookup("categoryID", "tblUser", "userID= " & Forms!frmLogin.cmbUser) = 133 Then

With Me

.DataEntry = True
.AllowEdits = True
.AllowDeletions = False
.AllowAdditions = True
.AllowFilters = False

End With

Else
MsgBox "You are not authorized to view this form"
Cancel = True
DoCmd.close acForm, "ObstcsForm"
End If

End If

'****If user has Read Only access level*****************

If DLookup("reado", "tblUser", "userID= " & Forms!frmLogin.cmbUser) = True And

If DLookup("categoryID", "tblUser", "userID= " & Forms!frmLogin.cmbUser) = 133 Then

With Me

.DataEntry = False
.AllowEdits = False
.AllowDeletions = False
.AllowAdditions = False
.AllowFilters = True

End With

Else

MsgBox "You are not authorized to view this form"

Cancel = True

DoCmd.close acForm, "ObstcsForm"

End If

End If