• Field Validation (Word 97 SR2)

    Author
    Topic
    #377985

    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

    Viewing 0 reply threads
    Author
    Replies
    • #624276

      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.

      • #624278

        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

        • #624279

          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?

          • #624545

            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.

            • #624551

              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

            • #624553

              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. doh
              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 bummer.

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

              Dim clrec As String, clnum As String
            • #624560

              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 bummer sigh . Any other thoughts?????

            • #624571

              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)

            • #625005

              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

    Viewing 0 reply threads
    Reply To: Field Validation (Word 97 SR2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: