I have created two tables: Employees & TimeClock. I have created a form called ClockIn.
What I am trying to do is have the form to check if the employeename and password matches what is in the Employees Table. if that matches then the employee is able to clockin. When the employee clocks in, the data is sent to the TimeClock Table. Once the Employees Table has been checked, the data matched, then the form determines if today matches any value in the TimeClock table with the employeename. If it does not match, then a new row is suppose to be created.
As of right now, a new record is not created. The row containing the employeename is just updated.
Here is the code I have so far:
Option Compare Database
Private intLogonAttempts As Integer
Public MyEmpName As String
Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.txtEmployeename.Value = ""
Me.txtClockInDate.Value = Format(Now, "mmm d yyyy")
intLogonAttempts = 0
Private Sub Form_Timer()
Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
Private Sub txtEmployeeName_AfterUpdate()
'After selecting user name set focus to password field
Private Sub cmdClockIn_Click()
Dim blnFound As Boolean
blnFound = False
'Check to see if data is entered into the UserName Textbox
If IsNull(Me.txtEmployeename) Or Me.txtEmployeename = "" Then
MsgBox "Employee Name is a required field.", vbOKOnly, "Required Data"
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "Password is a required field.", vbOKOnly, "Required Data"
'Check value of password in Employees to see if this matches value entered into textbox
If Me.txtPassword = DLookup("Password", "Employees", "EmployeeName='" & Me.txtEmployeename.Value & "'") Then
MyEmpName = Me.txtEmployeename.Value
'blnFound = True
MsgBox "Employee Name or Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtEmployeename = ""
Me.txtPassword = ""
'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 your system administrator.", vbCritical, "Access is Denied!"
Dim fldItem As ADODB.Field
Dim fldItem2 As ADODB.Field
Dim rstTimeClock As ADODB.Recordset
Dim strDate As String
'Me.txtClockIn.Value = Format(Now, "dddd, mmm d yyyy")
Set rstTimeClock = New ADODB.Recordset
"SELECT * FROM TimeClock WHERE EmployeeName = '" & _
txtEmployeename & "'", _
adOpenStatic, adLockReadOnly, adCmdText
'Check each record
While Not .EOF
'Check the name of the column
For Each fldItem In .Fields
'if the current column is Password
If fldItem.Name = "ClockIn" Then
'Check the value
'If the current column holds the same employee password
'the employee entered
If fldItem.Value <> Me.txtClockInDate.Value Then
'... then get the record and display
'the full name in the controls
'Me.txtEmployeeName = .Fields("EmployeeName")
'.Fields("EmployeeName") = Me.txtEmployeeName.Value
blnFound = True
MsgBox "You have all ready clocked in today."
'In case it was not found, move to the next record
If blnFound = True Then
On Error GoTo Err_cmdClockIn_Click
DoCmd.GoToRecord , , acNewRec
can you just highlight where you have a problem to make it easier ? most of ppl doesn't have time to look inside all this codeing .
Try putting a break on the routine and stepping through it to see what is actually happening when it runs.
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
Please use [Code]your code goes in here[/Code] tags when posting code.
Before posting your question, did you look here
Got a question on Linux? Visit our Linux sister site.
Modifications Required For VB6 Apps To Work On Vista
1) I would think that adLockReadOnly might mean exactly what it says: read only (can't add records). As written, this is a moot point because...
>DoCmd.GoToRecord , , acNewRec
This would be used to move a *bound form* to a new record, not for movement within a recordset.
You are trying to add a record to the Form's recordset and, afaik, the form doesn't have a recordset to add to.
If you want to add a record to the Recordset you opened in code, you want something like:
3) You want to at least change your loop so that a) you actually exit the loop if a match is found b) avoid looping through fields names (!!!) and c) set & test blnFound properly:
rstTimeClock!EmployeeName = MyEmpName
' (Add Date, Time and other data to record..)
Alternatively you could skip the recordset entirely:
While Not .EOF And Not blnFound
If blnFound = False
If clng(!ClockIn) <> cdate(Me.txtClockInDate.Value) Then
'No match, keep looking
blnFound = True
MsgBox "You have already logged in today."End If
strResult = nz(Dlookup("empname","TimeClock", EmpMatch? AND DateMatch?),"")
if strResult = "" Then
'No Emp & Date match.
' Add record with INSERT INTO else
'Display message end if
Last edited by GeorgeN; 11-29-2007 at 02:41 PM.
By sandhya.o.k in forum VB Classic
Last Post: 10-01-2009, 05:42 AM
Last Post: 06-26-2006, 04:13 PM
By obinna eleanya in forum VB Classic
Last Post: 09-01-2005, 11:45 AM
By Peter Merwood in forum VB Classic
Last Post: 03-01-2002, 02:42 PM
By Demo in forum VB Classic
Last Post: 07-25-2000, 08:10 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL