login form error 91

[HS_1]

I wonder if somebody can help me with this one. I have minimal knowledge of vba so when I needed login form I copied one from the internet, followed the instructions but ended up with this error message:
Run-time error '91':
Object variable or with block variable not set.

The code is:



Private Sub CmdLogon_Click()
Dim rsUser As Recordset
Dim strUser As String
'Set db = CurrentDb
strUser = "select initial,group from TB_User where initial = '" & UCase(Me!UserInitial) & "' and password = '" & UCase(Me!Password) & "'"
'Debug.Print strUser
Set rsUser = db.OpenRecordSet(strUser)

'rsUser.MoveFirst
If rsUser.EOF Then
MsgBox ("Invalid user name or password, please try again!")
Else
U_initial = rsUser.Fields("initial")
U_Groups = rsUser.Fields("group")
DoCmd.OpenForm "Tracking", acNormal

rsUser.Close
Set rsUser = Nothing
DoCmd.Close acForm, "FrmLogin", acSaveNo

End If
'Debug.Print U_Initial
'Debug.Print U_Groups
End Sub

It stops at
Set rsUser = db.OpenRecordSet(strUser)
I have also created user table, and new module as instructed with the following code:
Public db As Database
Public U_initial As String
Public U_Groups As String

but the form still doesn't work.
Tx

[Micron]

you have commented out this line
'Set db =
then tried to use the db object. Fix that first and we'll see if anything else pops up.

P.S. please surround your code with code tags if there's more than one or two lines.
Edit: sorry, just noticed that you might be saying that you declare db in a module as a public variable. If so, is that a standard module (i.e. one that is not behind a form or report)? If so, it is not the current db.

[HS_1]

Micron
I removed the ' before set db and still have the same error message.
Tx

[davegri]

Private Sub CmdLogon_Click()
Dim rsUser As DAO.Recordset
Dim strUser As String
Set db = CurrentDb
strUser = "select initial,group from TB_User where initial = '" & UCase(Me!UserInitial) & "' and password = '" & UCase(Me!Password) & "'"
'Debug.Print strUser
Set rsUser = db.OpenRecordSet(strUser)

'rsUser.MoveFirst
If rsUser.EOF Then
MsgBox ("Invalid user name or password, please try again!")
Else
U_initial = rsUser.Fields("initial")
U_Groups = rsUser.Fields("group")
DoCmd.OpenForm "Tracking", acNormal

rsUser.Close
Set rsUser = Nothing
DoCmd.Close acForm, "FrmLogin", acSaveNo

End If
'Debug.Print U_Initial
'Debug.Print U_Groups
End Sub

I think you need to dim the recordset as DAO. See the Red.

[Micron]

Well, you didn't answer the question about the module. So I'd try it this way (make sure you read the code notes after the code block before using).

Private Sub CmdLogon_Click()
'Dim db as DAO.Database 'this is how it should be; explicitly either DAO or ADO
'Dim rsUser As DAO.Recordset 'ditto
Dim db as Database
Dim rsUser As Recordset
Dim strUser As String

strUser = "Select [initial], [group] from TB_User where TB_User.[initial] = ' " & UCase(Me.UserInitial) & ' " and TB_User.[password] = ' " & UCase(Me.Password) & " ' "
'Debug.Print strUser
Set db = Currentdb
Set rsUser = db.OpenRecordSet(strUser)

'rsUser.MoveFirst
If rsUser.EOF Then
   MsgBox ("Invalid user name or password, please try again!")
Else
   U_initial = rsUser.Fields("initial")
   U_Groups = rsUser.Fields("group")
   DoCmd.OpenForm "Tracking", acNormal

   rsUser.Close
   Set rsUser = Nothing
   Set db = Nothing
   DoCmd.Close acForm, "FrmLogin", acSaveNo

End If
'Debug.Print U_Initial
'Debug.Print U_Groups
End Sub

Using NotePad, it seemed that one embedded single quote is in the wrong order in your sql string, like '" and '" instead of '" and "' so I entered spaces in between for clarity. You need to remove them. You could first try without all the other changes I implemented, such as [ brackets, but at least check the quotes first. I would also reference the table in the sql statement. I specified the objects as DAO - these should always be explicitly declared, but you need a reference to DAO. It should work without doing so, as Access will use late binding without being explicit. Also, password is a reserved word and should not be used for db objects or fields, which is another reason for enclosing in brackets. http://allenbrowne.com/AppIssueBadWord.html

Other not so much code related comments:
First, because you've indicated your knowledge level is novice, then you should know that there are ways around this if you haven't taken the necessary steps. Even then, the degree of security you can implement with this approach is low. If you don't have snoopy people using this db, then maybe no worries. At the very least, your password field has to have a unique index so that no two persons can have the same password. I wouldn't use it as the primary key though.

Second, why bother with passwords? Put the users into a user table with their system name as one of the fields and get their system name on startup. If that name is not found they don't get in. Some use the Environ function for this, I use fosUserName. There was only one case I came across where the Environ function would not work on the network; cant' recall why, but the db creator started using fosUserName afterwards. Examples of either are readily available. You can also get the machine id and use them anywhere in the db, such as recording who altered what record and on what computer. Not hard to steal or borrow passwords, but to fake the system name would require you to share your network login credentials with someone who logs on to the network as you, and most people won't do that. You can also test if the db is where it is supposed to be (Application.Path) and if not, don't allow them to open unauthorized copies - because people will copy a db for easy access to it. All the foregoing assumes you have split your db into front and back ends.

Other comments:
- you also have this commented out 'rsUser.MoveFirst. Probably good since I would not try to move first without testing that there was at least one record in the rs.
- Ucase is not necessary unless you are using Option Binary. Option Compare means that A = a.
- you will likely find that without a way to reset the two variables, an error during db use can cause their values to be lost, even though declared at the db level. If you then try to use those values, you might raise additional errors which you will need to trap and then reset the variables.

[HS_1]

Micron and Davegri Thank you for the reply and useful comments, I have added the DAO object as you suggested and it works perfectly.
HS_1