Login Screen in Access with forms, macros and queriesms-

Question

I have an embarrassing question regarding Access. I can build relational databases in my sleep but I have always used PHP, mySQL and HTML to build my applications.

I've hated Access with a passion for various reasons ever since I can remember but now I'm stuck using it for a particular project.

I have a number of tables, one of which is customer, which among other things has a username and password field.

When I start the database, I want a login form to appear, which I can do using the AutoExec macro, I've made the form with a username and password field. After that, I get stuck with the logic of querying for the username/password and then showing a new form if correct or an error if not.

Could anyone help me out with making the macro and query work together?

Clarification: I am trying to do this without coding whole Visual Basic macros, if at all possible, I want to be able to do it using the macro builder thingumy.

Thanks

Answer 1

Given a form frmLogin, with 2 text boxes, txtUserName and txtPassword, and a command button, you can try the following

Private Sub Command0_Click()
Dim rec As Recordset2

    Set rec = CurrentDb.OpenRecordset("SELECT * FROM Customer WHERE username = """ & txtUserName.Value & """ AND password = """ & txtPassword.Value & """")
    If (rec.RecordCount > 0) Then
        DoCmd.OpenForm "frmMain"
        DoCmd.Close acForm, "frmLogin"
    End If
End Sub

Answer 2

A slight tweak to the above as the code above would be open to SQL injection attacks (yes I know it is only access but it never hurts)

Public Function CheckUserPassword(strUserName As String, strPassword As String) As Boolean
Dim rst As DAO.Recordset
Set rst = DBEngine(0)(0).OpenRecordset("tblUsers", dbOpenTable)
With rst
    .Index = "UserName"
    .Seek "=", strUserName
    If .NoMatch = False Then
      If !Password = strPassword Then
        CheckUserPassword = True
    Else
        CheckUserPassword = False
    End If
Else
    CheckUserPassword = False
End If
End With
rst.Close
Set rst = Nothing

End Function

Answer 3

Malphas -

It is actually possible to do this without using VBA, but I am wondering whether the reason why you don't want to use VBA is because of the Trust issue. In which case, this won't be possible, because the macro actions Close and Quit are disallowed if the database is not trusted.

Whilst you can to run actions in the AutoExec macro beyond the point where you use the OpenForm command, I think it is neater to continue the next actions on the form itself. First because you can't really do branching in a macro; secondly because it is more modular to keep actions to do with the form actually on the form.

In the example below, my sample login form is called LoginForm, and the username text box is txtUserName, and the password text box is called txtPassword.

The first thing to do is to protect the dialogue from the simple act of letting the user close the dialogue and escape into the database design screen. The best way to do this is to set a flag called ValidLogin when the form loads. You will set this flag during the login process. When the form is closed, check whether the flag is true. If ValidLogin is false, then close the database.

On the OnLoad event of the Login form, click on the ellipsis button, and choose Macros Builder. In the Macro screen, use the following actions (note that the Condition column is hidden by default - but you'll only need for the next two macros):

Line Condition     Action/Arguments

1                  SetTempVar, Name = ValidLogin, Expression = False

On the OnUnload event of the Login form, do the same as above, and add:

Line Condition     Action/Arguments

1  Not [TempVars]![ValidLogin]
                   Quit, Options = Exit.

If you run this now, as soon as you close the form, the database will close. To be useful, you need to add the following macro actions to the OnClick event of your Login button:

Line Condition     Action/Arguments

1                  SetTempVar, Name = Valid Login, Expression = DCount("*","Customer","[Username]=[Forms]![LoginForm]![txtUserName] And [Password]=[Forms]![LoginForm]![txtPassword]")>0

2 Not [TempVars]![ValidLogin]
                   MsgBox, Message = Invalid Login
3 ...              StopMacro

4                  OpenForm, Form Name = MainForm

5                  Close, Object Type = Form, Object Name = LoginForm, Save = No

Note that in all these examples, I have used embedded macros, not named macros, so you can keep them together with the form. Also note the ellipsis (...) in the last macro, which represents the value of the last condition.