Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Validation (Word 97 SR2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Validation (Word 97 SR2)

    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.

  3. #3
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Validation (Word 97 SR2)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Validation (Word 97 SR2)

    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?

  5. #5
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Validation (Word 97 SR2)

    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.

  6. #6
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Validation (Word 97 SR2)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Validation (Word 97 SR2)

    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. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    It's good that you read it after all, because I believe I can solve your problem now.

    In VBA, the declaration
    <pre>Dim clrec, clnum As String</pre>

    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 <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>.

    If you declare clrec explicitly as a string, you will get what you want (I hope):
    <pre>Dim clrec As String, clnum As String</pre>


  8. #8
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Validation (Word 97 SR2)

    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 <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> . Any other thoughts?????

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Validation (Word 97 SR2)

    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 <font face="Georgia">clnum <> txtClientNum.Value</font face=georgia> to <font face="Georgia">UCase(clnum) <> UCase(txtClientNum.Value)</font face=georgia>
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Validation (Word 97 SR2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •