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

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.

I would I be able to tell and how could I adjust for this?

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.

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

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

Cheers mate. Thanks for all your help that works a treat.

Dave