Field Validation (Word 97 SR2)

Topic

New Reply

I need a piece of code in a form that returns a value from part of a line in a text file that is 6 units in length – ie CE0305 – this is a reference for a client name. I have it working just now but the structure of referencing is about to change and will could potentially start with a number including 0. When this is the case my code does not work – it will not accept the 0.

The code I have is

If txtClientNum.Value “” And Not IsNull(txtClientNum.Value) Then
Open “s:eliteftpclient.txt” For Input As #2
Do While Not EOF(2) And clnum txtClientNum.Value ‘ Loop until end of file.
Input #2, clrec
clnum = Mid$(clrec, 1, 6)
‘ Loop
‘ If Not EOF(2) Then
Close #2 ‘ Close file.
Else
frmFileRequest2.Hide
MsgBox “Invalid Client Number. Please try again”
Close #2 ‘ Close file.
frmFileRequest2.Show
End
End If
End If

If anyone can shed some light on how I can get it to validate with a zero it would be much appreciated

Reply | Quote

Replies

It’s not clear to me what you mean by “it will not accept the 0”.
Do you mean that a leading 0 will not be stored in clnum? If so, how are clrec and clnum declared? (You do have Option Explicit in your modules, I hope.) They should be declared as String:

Dim clrec As String
Dim clnum As String

By the way, why are the lines
‘ Loop
‘ If Not EOF(2) Then
commented out? As it stands, your code is not syntactically correct.

Reply | Quote

in answer to it being commented out first – I had just forgot to remove them before pasting it in – they are not present in the live form code. Both clrec and clnum are declared as strings but when the client number starts with a zero it knocks it off. This causes a problem because it then doesn’t appear to be 6 digits – for example one of our client numbers is 00002T. I need to get the code not to knock of any numbers. I dont feel I am explaining this very well but hopefully it now makes a little more sense

Reply | Quote

Strange; usually, when leading zeros disappear it’s because variables are declared as numeric. If the leading zeros are present in the text file (are you sure?) and if the variables are declared as strings, they should be preserved. I knocked together a small text file and a macro based on your post, and it extracted the 6 character value OK.

Where do you find out that the leading zeros are missing?

Reply | Quote

Hi there

the text file definitely has client numbers with leading zeros here is a snippet

NCH003|CL-Non ChargeableFiles|
NCH004|PC Unit Non Chargeable Files|
00002B|Bank of India|
00002C|Sheraton Caltrust |

if I put a client number which does not start with a zero it accepts it but with a zero it tells me it is invalid (I have programmed a message box in so if the person filling it in enters the wrong number in the field they cant get past unless they correct it). This is the point I find it is incorrect and it was one of the guys in the department who does SQL programming that suggested the dropping of the leading zeros was most likely the problem.

Reply | Quote

I got your last reply emailed to me but for some reason it cant find your last post

Here is the piece of code you were asking for

Dim clrec, clnum As String

If txtClientNum.Value “” And Not IsNull(txtClientNum.Value) Then
Open “s:eliteftpclient.txt” For Input As #2
Do While Not EOF(2) And clnum txtClientNum.Value ‘ Loop until end of file.
Input #2, clrec
clnum = Mid$(clrec, 1, 6)
Loop
If Not EOF(2) Then
Close #2 ‘ Close file.
Else
frmFileRequest2.Hide
MsgBox “Invalid Client Number. Please try again”
Close #2 ‘ Close file.
frmFileRequest2.Show
End
End If
End If

Reply | Quote

Hi,

I’m sorry about the confusion; I decided my reply was stupid, so I deleted it immediately after posting it, but it had already been sent to you.
It’s good that you read it after all, because I believe I can solve your problem now.

In VBA, the declaration

Dim clrec, clnum As String

means that the type for clrec is not defined explicitly, and therefore clrec is declared as a Variant, not as string. (This is different from languages like Pascal, and many programmers find it an irritating feature, because you tend to forget about it. But I’ll stop ranting.)

Because clrec is a variant, VBA tries to find the appropriate type. In this case, if the line begins with 00002B, it interprets it as the number 2 and discards the rest .

If you declare clrec explicitly as a string, you will get what you want (I hope):

Dim clrec As String, clnum As String

Reply | Quote

I made the changes you suggested but I am still getting the same error – if that should be working I am now wondering if it is giving me an error due to another problem . Any other thoughts?????

Reply | Quote

Sorry, I’m out of ideas. I have attached the document and text file I used to test your code. Note that I had to change the path to the client.txt file. Also note that you must enter the client number exactly as it is in the text file: 00002b will be invalid, 00002B is OK. If you want to get around this, change clnum txtClientNum.Value to UCase(clnum) UCase(txtClientNum.Value)

Reply | Quote

Thanks for your help – I will play around with it, this is not the only piece of code behind the OK so it might be being affected by something else.

Thanks again

Reply | Quote