Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    BIRMINGHAM, UK
    Posts
    4

    Question Unanswered: Access 97 (can't Be Zero Length)

    Hi,

    I have a Access 97 Dbase that is saved on a network. When one particular user attempts to run a query he gets the message

    "Field .... can't be a zero length string".

    I have a bit of script that registers where a user has been and I think this is causing this problem. But what I can't understand is why it works on everyone elses machine.

    Dim rs As Recordset

    Set rs = CurrentDb().OpenRecordset("UserTable")
    rs.AddNew
    rs("User") = Environ$("UserName")
    rs("Date") = Date
    rs("Time") = Time
    rs("Action").Value = "Drug Tariff - Search By Link Code"
    rs.Update

    Can anyone offer any help please.

    Dave

  2. #2
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39

    Re: Access 97 (can't Be Zero Length)

    Could it be, that in that machine the User Name is a null string??? If your access table is designed so that a UserName cannot be zero-length, then a error would give when updating the recordset with a zero-length value.

  3. #3
    Join Date
    Mar 2004
    Location
    BIRMINGHAM, UK
    Posts
    4
    I would I be able to tell and how could I adjust for this?

  4. #4
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    If I understood your question, try this:
    before the rs.AddNew, make a

    Msgbox Environ("UserName")

    if it is a zero-length string, then you will see a blank message. If so, then you will have to use a "default" username that is set for blank user names. Another thing you could do is modifying the table so that it accepts blank Users (if the User field is the key, then that is not posible)
    ask me if you have questions...

    j.i.

  5. #5
    Join Date
    Mar 2004
    Location
    BIRMINGHAM, UK
    Posts
    4
    Thank you J.I.

    You are correct, the UserName is coming up blank. I checked the table and I just have "ID" setup as key not the "User" field, the "User" field is also not setup as a required field so I assumed it would just input a blank field.

    I am a bit inexperienced in VB, still learning I'm afraid, but is it not possible to setup the "UserName" to pickup "CurrentUser" if the field is null. I did try that originally but that comes up with an error as well. If this is not possible, how do I fix the table to except null strings?

    I appreciate any help you can give on this.

    Dave

  6. #6
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    1) That the field is not Requiered does not mean that it allows zero-length values... Every field has another property "Allow Zero length values" or something like that (i dont have Access 97)... that you can set to YES. Then, the field will allow zero-length values.

    2) If you dont want to modify the design of the table (i wouldnt, the User should not be zero-length), then you can set a "Default" value to the user... the code could be like this:

    Dim User as string
    User=Environ$("UserName")
    if User="" then
    User="UnknowUser" 'write what you want here
    end if
    Set rs = CurrentDb().OpenRecordset("UserTable")
    rs.AddNew
    rs("User") = User 'do not user environ..
    rs("Date") = Date
    rs("Time") = Time
    rs("Action").Value = "Drug Tariff - Search By Link Code"
    rs.Update

    Ask me for questions

  7. #7
    Join Date
    Mar 2004
    Location
    BIRMINGHAM, UK
    Posts
    4
    Cheers mate. Thanks for all your help that works a treat.

    Dave

Posting Permissions

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