Form questions

Thanks to a few in the general forum, I have successfully used
Data->Validation. I have since then decided that the best way to have data
entered is by using a form as there are thousands of records.

My questions are: 1. How can I enforce a unique entry into a textbox? 2.
How can I enforce a date being entered (in mm/dd/yy format with the "/"). 3.
How can I enforce a phone number being entered ie 10 digits with no dashes.
I have another entry for a serial number which would be x amount of
characters, but I think that would be the same as the phone number scenario.

Thanks in advance,
Richard

Richard

You would need to validate the entry which can be done in many ways.

If you go to your form module and select a text box from the right hand side dropdown list then in the left hand drop down list you will be able to view all the available trigger events available for that text box.

You would nned to use one or several of them to validate your data or to have code validate each field when the OK button is pressed

Here is an exaple on testing the length of the entry

Private Sub TextBox1_AfterUpdate()
If Len(TextBox1.Text) <> 10 Then
MsgBox "Invalid Entry "
End If

End Sub

Hi Richard

1 You can not enforce an entry. Annoy the user long enough and she'll turn
off the computer without completing the form. Your best hope is to inform
that the entry already exists in a helpful way.

2 Same as 1, but also why the h*? A user should imo be allowed to enter
things like June 1 2006 into a date field and expect the computer to get it.
Entries should make sense to the /user/. Your job as a developer is to
translate anything of that kind into the stuff that your program needs.

3 same as 1 but also 2.
(Now if these three replies were a series of responses to your work from my
computer program, would you love using it ? ;-)

HTH. Best wishes Harald

"Richard" <[email protected]> skrev i melding
news:[email protected]...
> Thanks to a few in the general forum, I have successfully used
> Data->Validation. I have since then decided that the best way to have
data
> entered is by using a form as there are thousands of records.
>
> My questions are: 1. How can I enforce a unique entry into a textbox?
2.
> How can I enforce a date being entered (in mm/dd/yy format with the "/").
3.
> How can I enforce a phone number being entered ie 10 digits with no
dashes.
> I have another entry for a serial number which would be x amount of
> characters, but I think that would be the same as the phone number
scenario.
>
> Thanks in advance,
> Richard

1. Check the value against your saved values using. Assuming it is on a
worksheet , use the Application.MATCH worksheetfunction.

Dim iMatch As Long

On Error Resume Next
iMatch =
Application.MATCH(Textbox1.Text,Worksheets("Sheet1").Range("A1:A100"),0)
On Error Goto 0
If iMatch <> 0 Then
'not unique
End If

2. After the data has been completed, check the textbox text value with the
IsDate function. I would do this on a commit button, and it fails you reset
back to the textbox

With TextBox1
If Not IsDate(.Text) Then
'some sort of error message
.SelLength = Len(.Text)
.SelStart = 0
.SetFocus
End If
End With

3.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 45 Then '-
KeyAscii = 0
End If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Richard" <[email protected]> wrote in message
news:[email protected]...
> Thanks to a few in the general forum, I have successfully used
> Data->Validation. I have since then decided that the best way to have
data
> entered is by using a form as there are thousands of records.
>
> My questions are: 1. How can I enforce a unique entry into a textbox?
2.
> How can I enforce a date being entered (in mm/dd/yy format with the "/").
3.
> How can I enforce a phone number being entered ie 10 digits with no
dashes.
> I have another entry for a serial number which would be x amount of
> characters, but I think that would be the same as the phone number
scenario.
>
> Thanks in advance,
> Richard

Of course 2 only checks a valid date, it doesn't force an input in a
prescribed format. This can get very complex and is probably not worth the
effort.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> 1. Check the value against your saved values using. Assuming it is on a
> worksheet , use the Application.MATCH worksheetfunction.
>
> Dim iMatch As Long
>
> On Error Resume Next
> iMatch =
> Application.MATCH(Textbox1.Text,Worksheets("Sheet1").Range("A1:A100"),0)
> On Error Goto 0
> If iMatch <> 0 Then
> 'not unique
> End If
>
> 2. After the data has been completed, check the textbox text value with
the
> IsDate function. I would do this on a commit button, and it fails you
reset
> back to the textbox
>
> With TextBox1
> If Not IsDate(.Text) Then
> 'some sort of error message
> .SelLength = Len(.Text)
> .SelStart = 0
> .SetFocus
> End If
> End With
>
> 3.
>
> Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
> If KeyAscii = 45 Then '-
> KeyAscii = 0
> End If
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Richard" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks to a few in the general forum, I have successfully used
> > Data->Validation. I have since then decided that the best way to have
> data
> > entered is by using a form as there are thousands of records.
> >
> > My questions are: 1. How can I enforce a unique entry into a textbox?
> 2.
> > How can I enforce a date being entered (in mm/dd/yy format with the
"/").
> 3.
> > How can I enforce a phone number being entered ie 10 digits with no
> dashes.
> > I have another entry for a serial number which would be x amount of
> > characters, but I think that would be the same as the phone number
> scenario.
> >
> > Thanks in advance,
> > Richard
>
>