467,910 Members | 1,593 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,910 developers. It's quick & easy.

How do I restrict the data users can enter in an inputbox?

Allow me to preface this post by saying that this is my first database project, and while I have learned a lot, any concepts I have learned are hit and miss as I have found new requirements and researched solutions.

That said, I have a "training" database with PowerPoint briefings which I have users access and complete training. The form then records the date and time they completed training in a relevant field. I have a separate "master" database that uses an update query which runs on opening to make sure this master database receives any changes from the training database.

The problem is that I have to make sure there is an identical matching field in both databases to ensure the integrity of the update query results. The fields I currently use are the network username, pulled using UserName from the Wscript.Network object in my Training Database (which returns firstname.lastname) and match that against the conjoined firstname and "." and lastname as manually entered in the Master Database when the member inprocesses.

Unfortunately, due to the size of my organization, there are occasions in which the UserName has to have a number added to the end to make it unique (where two or more individuals have the same first and last name). I figured out how to drop the number off the end of the string, but now have two separate users who cannot be distinguished from each other.

Ideally, I would like to prompt each new user to input the last four digits of their SSN, which I can then add to the end of the UserName and create a unique match with the Master Database. I have found the input box function which does everything I need it to except restrict the data entry (at least I don't know how to make it restrictive). The closest I can get is to set the Input Mask in the table for the field "LastFour" to four zeros (0000). This is closer to what I want, but still allows a zero-length string or any combination of 1 to 4 numbers (it simply adds zeros to the front if necessary).

I am probably trying to do this the wrong way, but can't figure out how to do it any better. The code I am using for the input box is listed below. Once I can figure out how to restrict the entry to four numbers (no more and no less) I will be able to fill out the rest of the code. Help please.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Load()
  3.  
  4.     Me.LastFour = Inputbox("In order to create a unique account, please enter the last four digits of your Social Security Number.", "New User Account Setup")
  5.  
  6. End Sub
  7.  
  8.  
Jun 13 '07 #1
  • viewed: 7633
Share:
5 Replies
After rereading my post I realized my question isn't as clear as it should be. My specific question is: Using an input box (or better method if one exists), how do I force the user to enter four numbers (no more or less, and no letters, spaces, special characters, etc.)? I hope this clarifies the issue. Thank you.
Jun 13 '07 #2
MMcCarthy
Expert Mod 8TB
Don't use the Input box.

Put an unbound textbox on the form (txtLastFour).

In the BeforeUpdate event of the textbox you can put the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub txtLastFour_BeforeUpdate(Cancel As Integer)
  3.     If IsNumeric(Me.txtLastFour) = False Then
  4.         MsgBox "You must only enter the last four digits of your SSN Number", vbOKOnly
  5.         Cancel = True
  6.     ElseIf Len(Me.txtLastFour) > 4 Then
  7.         MsgBox "You can only enter the last four digits of your ssn number", vbOKOnly
  8.         Cancel = True
  9.     End If
  10. End Sub
  11.  
Mary
Jun 15 '07 #3
ADezii
Expert 8TB
After rereading my post I realized my question isn't as clear as it should be. My specific question is: Using an input box (or better method if one exists), how do I force the user to enter four numbers (no more or less, and no letters, spaces, special characters, etc.)? I hope this clarifies the issue. Thank you.
As a supplement to Mary's, as always, excellent advice, you can actually capture each Keystroke as they are entered into the Text Box to ensure that each one is Numeric. Any Keystroke other than 0 to 9, Backspace, Arrow Keys, etc. will be negated. You will still need the BeforeUpdate() Event, not to test if the value is Numeric (already done for each character), but to check for the length of the entry. This method is advantageous because it evaluates each and every character entered and ensures they will be Numeric before attempting to Update.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text16_KeyPress(KeyAscii As Integer)
  2. Const conFirstDigit As Byte = 48
  3. Const conSecondDigit As Byte = 57
  4. Const conBackSpace As Byte = 8
  5.  
  6. If (KeyAscii >= conFirstDigit And KeyAscii <= conSecondDigit) Or KeyAscii = conBackSpace Then
  7.   'do nothing, it is a Digit or Backspace
  8. Else
  9.   KeyAscii = 0    'Negate the Keystroke
  10. End If
  11. End Sub
Jun 15 '07 #4
Thank you both! I hadn't thought about trying it that way. I'll put it together and let you know how it goes.
Jun 18 '07 #5
ADezii
Expert 8TB
Thank you both! I hadn't thought about trying it that way. I'll put it together and let you know how it goes.
You're quite welcome.
Jun 18 '07 #6

Post your reply

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

Similar topics

3 posts views Thread by Paul | last post: by
5 posts views Thread by Jonny | last post: by
3 posts views Thread by Bruce Lawrence | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.