Error 3315

[db-noob]

For a long time, we've had no issue with this, but we've been getting this error a few times now, and I'm not sure what's going on.

Just in case, I checked my table:

Here's the segments of code I use for this:

Option Compare Database
'Username used for updating several forms
Public GUserName As String

'Log into database with username and password
Private Sub cmd_login_Click()
Dim db As dao.Database
  Dim rst As dao.Recordset
  Dim strSQL As String
 
  If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, Title:="Username Required"
    Me.txt_username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, Title:="Password Required"
    Me.txt_password.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  'updated query to include UserName 10/13/2020
  strSQL = "SELECT FirstName, LastName, UserName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    'MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error" --- commented out 10/13/2020
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error" & "strSQL = " & strSQL 'added 10/13/2020
    Me.txt_username.SetFocus
  Else
    'MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, Title:="Login Successful" --- commented out 10/13/2020
    GUserName = rst.Fields("UserName").Value 'added 10/13/2020
    MsgBox prompt:="Hello, " & GUserName & ". .CurrentUser=." & CurrentUser(), buttons:=vbOKOnly, Title:="Login Successful"  'added 10/13/2020
    DoCmd.Close acForm, "frm_login", acSaveYes
  End If
 
 Set db = Nothing
 Set rst = Nothing
 DoCmd.OpenForm "Main Menu"
End Sub

 

[CJ_London]

according to the field properties you are not allowing a zero length string, I would check the value of GUserName.

According to your login code, if a valid username/password is not found then the main menu form still opens - and GUserName is not assigned a value

Also recommend you put Option Explicit below Option Compare Database and compile your code

 

[db-noob]

according to the field properties you are not allowing a zero length string, I would check the value of GUserName.

According to your login code, if a valid username/password is not found then the main menu form still opens - and GUserName is not assigned a value

Also recommend you put Option Explicit below Option Compare Database and compile your code

Well, I know the data type is string based on the public module. But how would I find the value or assign a value?

I had no idea about the failed login. But in any case, they can't access the Main Menu with a failed login. The only options they have is to enter a current username/password or click Cancel which will close Access. They can't click on an object or a button on the ribbon.

Put Option Explicit for the module with GUsername? Also, how do you compile the code? All I've ever seen is Run Sub/User Form, Break, and Reset.

Also, I played around with my test record and made changes to it and the Before Update event worked. Not sure what the user is doing to get that error.

 

[db-noob]

@CJ_London

I'm not sure if this helps, but in my login for code:

GUserName = rst.Fields("UserName").Value

 

[CJ_London]

I was referring to

If rst.EOF Then
    'MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error" --- commented out 10/13/2020
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error" & "strSQL = " & strSQL 'added 10/13/2020
    Me.txt_username.SetFocus
  Else

you do not exit sub

But how would I find the value

when the code errors and the line is highlighted, with the mouse, hover over GUserName

Put Option Explicit for the module with GUsername?

No, all modules. You can have this auto included for new modules by going to Tools>Options and on the Editor tab tick the 'require variable declaration' option

Also, how do you compile the code?

in the vba window, select Debug>Compile

repeat until all errors are resolved. Also when you make changes to the code, compile before running it

 

[gemma-the-husky]

Do you have a user table. Maybe one of the users has a login initials string, but doesn't have all the fields set up correctly, so the name is blank. Something like that.

Alternatively, maybe the user encountered an unhandler error, which caused the program to reset, and the login credentials to be cleared to blank values. Zero for numbers, and ZLS for strings.

 

[db-noob]

Do you have a user table. Maybe one of the users has a login initials string, but doesn't have all the fields set up correctly, so the name is blank. Something like that.

Alternatively, maybe the user encountered an unhandler error, which caused the program to reset, and the login credentials to be cleared to blank values. Zero for numbers, and ZLS for strings.

Yes, I do have a user table with username and password. Could you explain that bit about login initials?

I checked my user table and both username and password allow ZLS. 1) Is that like default settings when you create a Short Text field? And 2) Would it make a difference if I went back to my linked table and changed it to disallow zero-length strings?

Now that's interesting. I've never heard of an unhandler error.

 

[db-noob]

@CJ_London

you do not exit sub

This login sub is code I found online. I'm not sure where to have it exit. Come to think of it, I don't have any error handling. *facepalm*

when the code errors and the line is highlighted, with the mouse, hover over GUserName

The error hasn't appeared yet, so I'll keep this in mind!

No, all modules. You can have this auto included for new modules by going to Tools>Options and on the Editor tab tick the 'require variable declaration' option

Ah, gotcha. What is the reason for putting Option Explicit for all modules? FYI, I ran the compiler (without including Option Explicit or changing it to require variable declaration) and it said something about invalid use of Me(dot) (Me.expression). It's a piece of code I played around with when I first built the database but ended up not using it, so I commented it out. Now, the Debug>Compile button is grayed out....so either there's nothing else wrong or everything is wrong?

 

[gemma-the-husky]

I meant, you might have userinitials, username and password.
If you login with userinitials, but the username is blank you would get an error when you need the username, but it's blank.
Alternatively, if you only have a username that you use to login, and the login is working correctly, it's not that. If the login isn't working correctly, and someone can still use the dbs without logging in, that would cause this error.

An alternative is that a user might log in, and GUserName might get set and stored by the login process. However, if you then have an error somewhere in the programme that's not intercepted and reported within the programme, the GUserName (and other variables) might go out of scope and get cleared to a blank - so the next time your code tries to read the value, it's now blank. Now you get an error that's actually caused by the first error. It might not be apparent to a user that he needs to report the first error. Your code might just be ignoring it and carrying on.

It might be something else completely causing the problem, so you have a sort of error that can be quite difficult to trace. You can actually fix it easily by testing for a blank and setting a default value for the username in the event you a have a blank username. That "overcomes" the error but doesn't really help you find the cause, and can allow bad data to be entered into your database.

 

[db-noob]

Hmm. I'm not sure I understand. *sigh*

testing for a blank and setting a default value for the username in the event you a have a blank username. That "overcomes" the error but doesn't really help you find the cause, and can allow bad data to be entered into your database.

Perhaps that's something I can try if this problem persists, but I've been playing with my test records for the past day and it hasn't come up. Plus, that last part about might overcoming error but doesn't exactly help and can allow bad data isn't something I want to risk at the moment.

Thanks for your time!

 

[gemma-the-husky]

As a general catch, there is an important function you can use to cast any "blank" object into a value of your choosing. Technically a "null".

Note that a variable can't be blank/null. A Numeric will default to zero, and a String will default to a zero length string. This isn't blank/null, but you can't visually tell the difference between a blank/null and a string of zero length (or even a space), but the computer can.

Anyway the function is nz(). I won't elaborate here, but it is something you will most likely use regularly, and often helps deal with the sort of issue you have.

 

[The_Doc_Man]

1) Is that like default settings when you create a Short Text field? And 2) Would it make a difference if I went back to my linked table and changed it to disallow zero-length strings?

I would like to try to answer these narrow questions.

1. Yes, "Allow ZLS" = YES is the default for a Short Text field. Also "Required" = NO and "Indexed" = NO. All defaults for new Short Text cases.

2. You can do that. Access allows you to change those properties after-the-fact. Note, however, that it will refuse (and complain vehemently) if even one record violates the constraint. So IF you succeed in setting "Allow ZLS" = NO then you know you don't have any of those.

 

[gemma-the-husky]

The problem you are getting is

me.editedby.value = GUserName

editedby is a field in your table which is set to "allow zero length = false"
GUserName is either null or a zero length string

so you get an error trying to set editedy to the the value GUserName., as GUserName has no value currently.
However if GUserName is the username of the logged in user, it probably shouldn't be blank.
So if you use nz() to coerce GUserName to a non blank value, it doesn't solve the real question of whether GUserName being blank is a problem in itself. If it shouldn't be blank, how did it get to be blank? Changing editedby to allow zero length might hide the fact that there really is another problem.

 

[CJ_London]

What is the reason for putting Option Explicit for all modules

it means when you compile it checks for each module that has option explicit that you have explicitly created each variable. If you don't include it, then errors that would have been caught, aren't until you actually run the application - a runtime error.

The compile is greyed out because it has compiled successfully - but successful from a stable application perspective is dependant on Option Explicit - per previous paragraph, if not included there is a risk that your app is not stable. e.g. very simplistically

Option Compare Database

Function Test()
var1="Hello"
var1=2
End Function

will compile without an error - var1 will be treated as a variant. But

Option Compare Database
Option Explicit

Function Test()
var1="Hello"
var1=2
End Function

will not compile because you have not dimmed var1

a variable can only be one type - so you declare it

Option Compare Database
Option Explicit

Function Test()
Dim var1 as Integer
var1="Hello"
var1=2
End Function

now it will fail on var1="Hello" because var1 is a number datatype, not a string

A very basic illustration, but hopefully you can see how not including Option Explicit can lead to runtime errors which can be difficult to track down.

As advised in post #2 and subsequently Gemma in post #13, the error you highlighted in post #1 implies that GUserName has not been assigned a value and it is not clear from your code snippets whether where you have declared it as type string in the first snippet is in the same module as the second snippet.

 

[db-noob]

@CJ_London Thank you for explaining and with an example. It's easier to learn with examples/visuals. I'll go ahead and implement those recommendations.

So the bit where I have

Option Compare Database
'Username used for updating several forms
Public GUserName As String

is saved as a module: Module2. I have a Module3 where I've used the DB Guy's code to backup the BE from the main menu. The Public GUserName is not included in the class object for my login form. Should have I included Dim GUserName as String for the login button?

 

[CJ_London]

Should have I included Dim GUserName as String for the login button?

No, it's OK in a general module. But if your code breaks for some reason with an unhandled error, it will lose any value assigned to it. You might want to consider using a tempvar instead

 

[db-noob]

So yesterday the error popped up for one of my users and highlighted the line Me.EditedBy.Value = GUserName like in post #1. I hovered over Me.EditedBy.Value and it said Me.Edited.Value = "<user's username>". When I hovered over GUserName it said GUserName = ""

You might want to consider using a tempvar instead

I'm so new at VBA, so I'm not sure how to use that. How would I go about that?

 

[gemma-the-husky]

well, as I said before
either they didn't log on, but were still able to use the dbs OR
They logged on, but the lgon process didn't set a GUserName OR
Something else went wrong and cleared GUserName

 

[db-noob]

So in my database, I have 4 forms that have a Editor and Edit Date fields: Work Order, Purchase Order, Customer, and Inventory. They all have a Before Update event. I've noticed that some of them have Me.<date field>.Value = Date where others don't have the .Value bit. Secondly, I went back to the tables for these forms. My Work Order table has the Editor field has Yes for ZLS, while the Purchase Order table has No for ZLS.

 

[db-noob]

well, as I said before
either they didn't log on, but were still able to use the dbs OR
They logged on, but the lgon process didn't set a GUserName OR
Something else went wrong and cleared GUserName

With my luck, it's probably a mix of all 3.