Access form userid / password against SQL table then write to table

[DaveCrate]

Access2007 front:I want make a log in form (frmLogIn)and validate the userid and password against the the SQL2005 table (tblUserList). How would you suggest this being done? I have messed around with a dlookup statement (with no success) but wonder if a SQL statement would be better. after checking if the user id and password match I'd like to write to a SQL table (tblUserLogOn w/userid and date).

SQL2005
tblUserList table
fldUser char(6) - primary
fldUserLast char(20)
fldUserFirst char(20)
fldUserStatus char(10)
fldPassword char(10)
fldAccess char(1)
fldUpDate datetime


SQL2005 table
tblUserLogOn
fldLogInUser char(6)
fldLogInDate datetime

Access form
frmNewUserSetUp
txtUser
txtUserLast
txtUserFirst
txtUserPassword

Access form
frmLogin
txtLogInUser
txtLogInPassword

I'm a little confused about the validation. Can you put a SQL statement in a IF statement to do the checking? or dlookup statement would work better

thanks for any help you may provide

here's vba code:
--------------------------------------------------------
Private Sub cmdLogIn_Click()

'fldLogInUser char(6)
'fldLogInDate DateTime

'Check to see if data is entered

If IsNull(Me.txtLogInUser) Or Me.txtLogInUser = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.txtLogInUser.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtLogInPassword) Or Me.txtLogInPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtLogInPassword.SetFocus
Exit Sub
End If

Dim strLogInUser As String
If txtLogInUser.Visible = True Then
Me.txtLogInUser.SetFocus
strLogInUser = Me.txtLogInUser.Text
Else
End If

Dim strLogInPassword As String
If txtLogInPassword.Visible = True Then
Me.txtLogInPassword.SetFocus
strLogInPassword = Me.txtLogInPassword.Text
Else
End If

'Dim strSQL As String

Dim adoLogInConnection As ADODB.Connection
Dim adoLogInRecordset As ADODB.Recordset

Set adoLogInConnection = New ADODB.Connection
Set adoLogInRecordset = New ADODB.Recordset

Dim pw As String
pw = "password"

adoLogInConnection.Open "Driver={SQL Server};" & _
"Server=servername;" & _
"DataBase=databasename;" & _
"User ID=username;" & _
"Password=" & pw

adoLogInRecordset.CursorType = adOpenKeyset
adoLogInRecordset.LockType = adLockOptimistic

adoPackLogRecordset.Open "tblUserList", adoLogInConnection, , , adCmdTable

MsgBox ("User '" & strLogInUser & "' is logged on now!")
'Else

'MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
' "Invalid Entry!"
' Me.txtLogInPassword.SetFocus

If DLookup("fldUser", "tblUserList", "[fldUser] ='" & Forms!frmLogIn) Then
MsgBox ("the user is " & txtLogInUser & " ")

'strSQL = "Select tblUserList.[fldUser] " & _
'"From tblUserList " & _
'"Where tblUserList.[fldUser]='" & strLogInUser & "';"

' adoLogInRecordset.CursorType = adOpenKeyset
' adoLogInRecordset.LockType = adLockOptimistic

'adoPackLogRecordset.Open "tblUserLogOn", adoLogInConnection, , , adCmdTable
'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1

If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
adoPackLogRecordset.AddNew
adoPackLogRecordset!fldLogInUser = strLogInUser



'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogIn", acSaveNo
DoCmd.OpenForm "frmWork"

adoLogInRecordset.Close
adoadoLogInConnection.Close
Set adoLogInRecordset = Nothing
Set adoLogInConnection = Nothing

Application.Quit
End If
End If

End Sub
-----------------------------------------------------

Thanbks Again