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

[Dakrat]

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.lastn ame) 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.

Private Sub Form_Load()
    
    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")

End Sub

[Dakrat]

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.

[MMcCarthy]

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:

[CODE=vb]
Private Sub txtLastFour_Bef oreUpdate(Cance l As Integer)
If IsNumeric(Me.tx tLastFour) = False Then
MsgBox "You must only enter the last four digits of your SSN Number", vbOKOnly
Cancel = True
ElseIf Len(Me.txtLastF our) > 4 Then
MsgBox "You can only enter the last four digits of your ssn number", vbOKOnly
Cancel = True
End If
End Sub
[/CODE]

Mary

[ADezii]

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.
[CODE=vb]Private Sub Text16_KeyPress (KeyAscii As Integer)
Const conFirstDigit As Byte = 48
Const conSecondDigit As Byte = 57
Const conBackSpace As Byte = 8

If (KeyAscii >= conFirstDigit And KeyAscii <= conSecondDigit) Or KeyAscii = conBackSpace Then
'do nothing, it is a Digit or Backspace
Else
KeyAscii = 0 'Negate the Keystroke
End If
End Sub[/CODE]

[Dakrat]

Thank you both! I hadn't thought about trying it that way. I'll put it together and let you know how it goes.

[ADezii]

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.