thanks Dave. The data types are matching and yes I'm aware that the count starts from 0...
super
thanks Dave. The data types are matching and yes I'm aware that the count starts from 0...
super
Can you upload a DEMO for us to look at?
Hi Dave,
Sorry for the delayed response. Please see the attached file. I tried as best as I could to capture the problem. You can log in as Jay (echo1 as PW) or as Laura (echo2 as PW) or as Michael (echo3 as PW). If you double click on the TripID field of the subform, an old record comes up. Click on DELETE.
**The data entry user should not be able to delete a record**
There is not a login form, subform or a field "TripID" that I can find in the example dB "test-database2"
Sheba,I did not download your db. Steve (ssanfu) says somethings are missing. It has been downloaded 2 times. Please upload a complete Demo. People here are really ready to help!
a thousand apologies I think I uploaded the wrong one. Apparently, a previous upload was still hanging around and since they had the same file name, I must selected it again?
Yes this is the correct file. Dave and Steve, I apologise for the delay and really appreciate your help.
Steve has already downloaded it. I bow out. Good Luck With Your Project!
many thanks Dave I really appreciate the support I have received thus far from this site. I look forward to Steve's feedback
Have a great evening
In which event are you running the code? Sometime the problem comes fromThe event. Is it the forms current, on open event, on load event. Check that out. The code should work better in the on current event of the form.
All your code does is prevent record deletion with the keyboard and ribbon Delete on the main form. Does nothing for the subform. Set those properties in Property Sheet for PediatricSubform to No.
Also, does not prevent programmatic deletion. The Delete command button code will have to validate user has this permission. Or disable the Delete command button in the code.
I don't use macros, only VBA.
Last edited by June7; 08-24-2014 at 12:40 AM.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
I've found a couple of things that are a problem - still looking at the dB.
But first, Questions:
1) How many Access levels do you have?
2) Is it a fixed number Access levels or will/can the number of access levels change?
3) How many Categories are there?
4) Will/can the number increase/decrease?
5) How do the access levels and categories relate?
6) If I have an AccessLevelID of 4 and a CategoryID of "PD02", does that mean that I can't enter data for "CR03"?
Description
AccessLevelID AllowAdditions DataEntry AllowEdits AllowDeletions Administrator 1 YES YES YES YES ? 2 ? 3 Data Only 4 YES YES no no Read Only 5 no no no no
-----------------------------------------------
OK, problems.....
1) Look up fields in the tables. IMO, a major waste of time.
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??)
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???)
4) So opening the form "frmLogin" and entering the correct user id, Password and clicking on the OK button executes some code. here is a snippet:
The openform command is looking for a filter named "acSaveNo". Doesn't throw an error, but is the wrong argument.Code:If Me.txtPassword.Value = DLookup("strPassword", "tblUser", "[UserID]=" & Me.cmbUser.Value) Then UserID = Me.cmbUser.Value DoCmd.OpenForm "PaediatricForm", acNormal, acSaveNo 'DoCmd.OpenForm acForm, "PaediatricForm", acSaveYes
You have this code:
The parameter "acSaveNo" (or acSaveYes) does not have anything to do with saving the data.Code:DoCmd.Close acForm, "PaediatricForm", acSaveNo
"acSaveNo" and "acSaveYes" only apply IF the form was opened in design view. If changes were made, then "acSaveNo" / "acSaveYes" determine if the changes to the form are saved or not.
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....
Testing continues........
In the delete macro, delete the command "GoTo Control". (=[Screen].[PreviousControl].[Name])
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
Code:Private Sub Form_Open(Cancel As Integer) If Forms!frmLogin!cmbUser.Column(3) = 4 Then If Forms!frmLogin!cmbUser.Column(4) = "PD02" Then With Me .DataEntry = True .AllowEdits = True .AllowDeletions = False .AllowAdditions = True .AllowFilters = True End With '-----------subform properties ----------- Me!sfPaediatric.Form.AllowDeletions = False Else MsgBox "You are not authorized to view this form" Cancel = True DoCmd.Close acForm, "PaediatricForm", acSaveNo End If Else If Forms!frmLogin!cmbUser.Column(4) = "All" Then If Forms!frmLogin!cmbUser.Column(3) = 1 Then With Me .DataEntry = True .AllowEdits = True .AllowDeletions = True .AllowAdditions = True .AllowFilters = True End With '-----------subform properties ----------- Me!sfPaediatric.Form.AllowDeletions = True Else With Me .DataEntry = False .AllowEdits = True .AllowDeletions = False .AllowAdditions = False .AllowFilters = True End With '-----------subform properties ----------- Me!sfPaediatric.Form.AllowDeletions = False End If End If End If End Sub
Hello demerit, the code is in the form's Open event...
hey June7 - thanks for your post. Records CAN be deleted only not by Data entry users. If I set the form's delete property to NO will that not affect everyone?