By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,045 Members | 2,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,045 IT Pros & Developers. It's quick & easy.

Access Input Forms - field validations & password over ride

P: 10
Hi:

I hope someone can help me.

Is there a way to add a password to a particular field in the event that the validation rule must be over written?

I've created an input form in a database that I'm creating. One field in particular has an input validation of " <=125000 " with a warning message that states "THE NET WEIGHT MUST BE LESS THAN OR EQUAL TO 125,000 lbs OR VALIDATION IS REQUIRED."

The user would like the ability to override this rule, when necessary, via a password or some other type of login validation. In other words he does not want just anyone to have the ability to override this rule.

I am using Access 2000 with service pack 3.

Thanks,

JMKC
Feb 25 '07 #1
Share this Question
Share on Google+
10 Replies


100+
P: 1,646
Hi. Yes this is indeed possible. You would need to step away from the automatic nature of the validation you are using right now and code everything by hand.

You could either hard code a particular word as a password or you could store user passwords in a table and look them up when validating the input.
Feb 25 '07 #2

ADezii
Expert 5K+
P: 8,628
Hi:

I hope someone can help me.

Is there a way to add a password to a particular field in the event that the validation rule must be over written?

I've created an input form in a database that I'm creating. One field in particular has an input validation of " <=125000 " with a warning message that states "THE NET WEIGHT MUST BE LESS THAN OR EQUAL TO 125,000 lbs OR VALIDATION IS REQUIRED."

The user would like the ability to override this rule, when necessary, via a password or some other type of login validation. In other words he does not want just anyone to have the ability to override this rule.

I am using Access 2000 with service pack 3.

Thanks,

JMKC
Here is an example straight from the Help Files as to how you would accomplish this via VBA. It sets the Validation Rule in the [Age] Field to > 65 with accompanying Validation Text:
Expand|Select|Wrap|Line Numbers
  1. Dim strTblName As String, strFldName As String
  2. Dim strValidRule As String
  3. Dim strValidText As String, intX As Integer
  4.  
  5. strTblName = "Customers"
  6. strFldName = "Age"
  7. strValidRule = ">= 65"
  8. strValidText = "Enter a number greater than or equal to 65."
  9. intX = SetFieldValidation(strTblName, strFldName, strValidRule, strValidText)
Expand|Select|Wrap|Line Numbers
  1. Function SetFieldValidation(strTblName As String, _
  2.     strFldName As String, strValidRule As String, _
  3.     strValidText As String) As Integer
  4.  
  5.     Dim dbs As Database, tdf As TableDef, fld As Field
  6.  
  7.     Set dbs = CurrentDb
  8.     Set tdf = dbs.TableDefs(strTblName)
  9.     Set fld = tdf.Fields(strFldName)
  10.     fld.ValidationRule = strValidRule
  11.     fld.ValidationText = strValidText
  12. End Function
Feb 25 '07 #3

P: 10
Thanks so much!


JMKC
Feb 25 '07 #4

100+
P: 1,646
Here is an example straight from the Help Files as to how you would accomplish this via VBA. It sets the Validation Rule in the [Age] Field to > 65 with accompanying Validation Text:
I must be going blind in my old age. Can you please point out the password override in this code. Thanks
Feb 25 '07 #5

ADezii
Expert 5K+
P: 8,628
I must be going blind in my old age. Can you please point out the password override in this code. Thanks
Your eyes are fine, willy. I posted just the basic Validation Rule code, to provide a Password Override would be a simple modification.
Feb 25 '07 #6

100+
P: 1,646
Your eyes are fine, willy. I posted just the basic Validation Rule code, to provide a Password Override would be a simple modification.
Excellent. Now, given that the op asked for such a modification, perhaps you could show how it is done. :)
Feb 25 '07 #7

ADezii
Expert 5K+
P: 8,628
Excellent. Now, given that the op asked for such a modification, perhaps you could show how it is done. :)
Expand|Select|Wrap|Line Numbers
  1. 'Must use 1 of several Methods to ensure User cannot modify this code
  2. Dim strPrompt As String, strTitle As String, strResponse As String
  3. Dim strValidationRule As String, strValidationText As String
  4.  
  5. strPrompt = "Please enter a Password to override the Validation Rule for <some field>"
  6. strTitle = "Validation Rule Override"
  7.  
  8. strResponse = InputBox$(strPrompt, strTitle, "Enter Validation Override Password")
  9.  
  10. If Len(strResponse) > 0 And strResponse <> "Enter Validation Override Password" Then
  11.   If StrComp("PassWOrd", strResponse, vbBinaryCompare) = 0 Then   'Exact 'Binary' Match
  12.     strValidationRule = InputBox$("Enter new Validation Rule" & vbCrLf & vbCrLf & _
  13.                         "[e.g. <=125000, >=10000, etc.]", "Validation Rule Override")
  14.       If Len(strValidationRule) = 0 Then Exit Sub
  15.     strValidationText = InputBox$("Enter new Validation Text", "New Validation Text")
  16.   Else
  17.     Exit Sub
  18.   End If
  19. Else
  20.   Exit Sub
  21. End If
  22.  
  23. 'Hook into Validation Rule/Text code using strValidationRule and strValidationText
Feb 26 '07 #8

100+
P: 1,646
Wonderful. Good work!
Feb 26 '07 #9

P: 10
Hi Guys. I know it's been a few weeks . . .I tried the above code but cannot get it to work properly.

It seems that no matter what I type in the field (either greater than or less than 125000) I get the message to supply a password and then, I can type in anything as a password and it will move on to the next field.

Needless to say I am rather frustrated. It also goes without saying that I am a complete novice at Access. I can create a database but when it comes to VBA I am totally lost.

I used the code in an event procedure before update. I also tried it on exit.

I made the following modifications:

strValidationRule = InputBox$(">125000" & vbCrLf & vbCrLf & _
"[e.g. <=125000, >=10000, etc.]", "Validation Rule Override")

The whole routine is below.

Private Sub NetWeight_BeforeUpdate(Cancel As Integer)

Dim strPrompt As String, strTitle As String, strResponse As String
Dim strValidationRule As String, strValidationText As String

strPrompt = "Please enter a Password to override the Validation Rule for NetWeight"
strTitle = "Validation Rule Override"

strResponse = InputBox$(strPrompt, strTitle, "Enter Validation Override Password")

If Len(strResponse) > 0 And strResponse <> "Enter Validation Override Password" Then
If StrComp("PassWOrd", strResponse, vbBinaryCompare) = 0 Then 'Exact 'Binary' Match
strValidationRule = InputBox$(">125000" & vbCrLf & vbCrLf & _
"[e.g. <=125000, >=10000, etc.]", "Validation Rule Override")
If Len(strValidationRule) = 0 Then Exit Sub
strValidationText = InputBox$("Enter new Validation Text", "New Validation Text")
Else
Exit Sub
End If
Else
Exit Sub
End If


End Sub


Any help would be appreciated.
Apr 3 '07 #10

ADezii
Expert 5K+
P: 8,628
Hi Guys. I know it's been a few weeks . . .I tried the above code but cannot get it to work properly.

It seems that no matter what I type in the field (either greater than or less than 125000) I get the message to supply a password and then, I can type in anything as a password and it will move on to the next field.

Needless to say I am rather frustrated. It also goes without saying that I am a complete novice at Access. I can create a database but when it comes to VBA I am totally lost.

I used the code in an event procedure before update. I also tried it on exit.

I made the following modifications:

strValidationRule = InputBox$(">125000" & vbCrLf & vbCrLf & _
"[e.g. <=125000, >=10000, etc.]", "Validation Rule Override")

The whole routine is below.

Private Sub NetWeight_BeforeUpdate(Cancel As Integer)

Dim strPrompt As String, strTitle As String, strResponse As String
Dim strValidationRule As String, strValidationText As String

strPrompt = "Please enter a Password to override the Validation Rule for NetWeight"
strTitle = "Validation Rule Override"

strResponse = InputBox$(strPrompt, strTitle, "Enter Validation Override Password")

If Len(strResponse) > 0 And strResponse <> "Enter Validation Override Password" Then
If StrComp("PassWOrd", strResponse, vbBinaryCompare) = 0 Then 'Exact 'Binary' Match
strValidationRule = InputBox$(">125000" & vbCrLf & vbCrLf & _
"[e.g. <=125000, >=10000, etc.]", "Validation Rule Override")
If Len(strValidationRule) = 0 Then Exit Sub
strValidationText = InputBox$("Enter new Validation Text", "New Validation Text")
Else
Exit Sub
End If
Else
Exit Sub
End If


End Sub


Any help would be appreciated.
I've been away for a few days, give me a little time to look at this.
Apr 4 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.