Automatic Time/Date stamp

[drowe005]

I've been using a file for a while in excel and it has worked fine, but I am now in the need to transfer the file over to Access and am unsure how to do it.

What I will have is a form with 2 spots for entry (1 space to enter employee number and one space to enter a MO number). I will then need a 3rd column that will be a unique number to determine whether there are multiple entries or not (This will not be a user defined entry, it will be automatically made). If the Employee Number is 132 and the MO Number is 453, the unique 3rd field would then say 132453 (Employee Number & MO Number).

So...When a new entry is made into the database through the form, it will look to see if that same set of 2 entries were entered before or if it is a new entry. If it is a new entry, it will give a time/date stamp to a column as a TIME IN. If it a duplicate of that record, it will then give a TIME OUT in another column.

Hopefully I didn't make this too confusing sounding and I appreciate any help that I can get. Here is a copy of the VBA I used in excel for this, but not sure how to transfer it over to work in Accesss. Maybe there's an easier way than transferring the VBA over, but I'm not sure. Thanks!

Option ExplicitDim rStart As Range


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:C")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            Application.EnableEvents = False
            On Error Resume Next
            Range("A" & Target.Row).Value = Range("B" & Target.Row) & Range("C" & Target.Row)
            Application.EnableEvents = True
        End If
    End If
    
    If Not Intersect(Target, Range("B2:B7500")) Is Nothing Then
        Application.EnableEvents = False
        Cells(Target.Row, "C").Select
        Application.EnableEvents = True
        Exit Sub
    End If
    
    If Intersect(Target, Range("C2:C7500")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    Dim lc As Long, fr As Long, n As Long, nr As Long
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        n = Application.CountIf(Columns(1), Cells(Target.Row, 1))
        If n = 1 Then
            lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
            If lc = 1 Then
                Cells(Target.Row, lc + 2) = Format(Now, "m/d/yyyy h:mm")
                ElseIf lc > 2 Then
                Cells(Target.Row, lc + 2) = Format(Now, "m/d/yyyy h:mm")
            End If
        Else
            fr = 0
            On Error Resume Next
            fr = Application.Match(Cells(Target.Row, 1), Columns(1), 0)
            On Error GoTo 0
            If fr > 0 Then
                lc = Cells(fr, Columns.Count).End(xlToLeft).Column
                If lc = 1 Then
                    Cells(fr, lc + 2) = Format(Now, "m/d/yyyy h:mm")
                    ElseIf lc > 2 Then
                    Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
                End If
                Target.ClearContents
            End If
        End If
        On Error Resume Next
        Me.Range("C2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
        nr = Me.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        Me.Cells(nr, 2).Select
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

** In this VBA code for excel, column A was the unique Employee Number/MO Number. Column B was Employee Number. Column C was MO Number. And Column E and F were Time IN and Time OUT respectively. **

 

[TerryHogarth21]

Here's what I mocked up based on the info you gave.

Table -> TableA (Fields ID, Employee_Num, MO_Num, Time_In, Time_Out)
Form -> TimeCard (TextBox -> EEID, TextBox -> MOID)
Button -> TButton and in the Buttons On Click Event the below

Option Compare Database

Private Sub TButton_Click()
'Declare variables
Dim Emp_Num As Long, Mod_Num As Long
Dim strSQL As String, LValue As String

LValue = Now

'dcount based on form inputs
Emp_Num = DCount("Employee_Num", "TableA", "Employee_Num = " & Me.EEID)
Mod_Num = DCount("Mo_Num", "TableA", "Mo_Num =" & Me.MOID)

If Emp_Num = 1 And Mod_Num = 1 Then
    'This would mean there is 1 record based on the newly inputted info
        
            'SQL string for updating the record with Emp_Num and Mod_Num
            strSQL = "UPDATE TableA SET TableA.Time_In = '" & LValue & _
                     "' WHERE ID = (SELECT MAX(ID) FROM TableA);"
                     
            'uncomment if you need to see sql string in immediate window for errors
            'debug.Print strsql
            DoCmd.RunSQL strSQL
            
Else:
    'This would imply that the record number has a count of 2 or more
            'SQL string for updating the last record added based on max ID
            strSQL = "UPDATE TableA SET TableA.Time_Out = '" & LValue & _
                     "' WHERE ID = (SELECT MAX(ID) FROM TableA);"
                     
            'uncomment if you need to see sql string in immediate window for errors
            'Debug.Print strSQL
            DoCmd.RunSQL strSQL

End If

End Sub

I don't exactly know your steps in creating your record, but how I had envisioned that you can take this code after your add records procedure.

So my assumption is that you will add your records based on the forms (regardless if there are records existing or not existing) and that only that last record will be updated with the time stamp.

Basically the SQL would always update the last record (assuming you have a primary key and it goes up incrementally, but if you don't we'll need to change the query logic) but put in the date/time stamp to either the Time_In(new record, 1 count) or Time_Out(Existing record, 2+ count) field.

Does this concept get you close?

 

[drowe005]

Thanks for the reply Terry. From what I can tell, it sounds like your idea is the same as mine. I have tried running this, but am getting an error that points the line ending in Me.EEID. Do you know where this problem would be from?

I created a table named TableA with columns named Fields ID, Employee_Num, MO_Num, Time_IN, & Time_OUT. I then created a form named TimeCard with two text boxes named EEID and MOID bound to Employee_Num and MO_Num respectively. I then added a button and renamed it to TButton. This is all correct, right?

So my assumption is that you will add your records based on the forms (regardless if there are records existing or not existing) and that only that last record will be updated with the time stamp.

Yes that is correct, only the last record entered will need a time stamp added.

For Example: Employee 123 working on MO 387 enters there info in the form and then clicks the submit button. A Time In would then be added to their respective row in the table. 20 more people may log in or out before 123 comes back along to log out, and when employee 123 logs out of 387, a time out will then be added to their row and their row only.

 

[TerryHogarth21]

Thanks for the reply Terry. From what I can tell, it sounds like your idea is the same as mine. I have tried running this, but am getting an error that points the line ending in Me.EEID. Do you know where this problem would be from?

I created a table named TableA with columns named Fields ID, Employee_Num, MO_Num, Time_IN, & Time_OUT. I then created a form named TimeCard with two text boxes named EEID and MOID bound to Employee_Num and MO_Num respectively. I then added a button and renamed it to TButton. This is all correct, right?



Yes that is correct, only the last record entered will need a time stamp added.

For Example: Employee 123 working on MO 387 enters there info in the form and then clicks the submit button. A Time In would then be added to their respective row in the table. 20 more people may log in or out before 123 comes back along to log out, and when employee 123 logs out of 387, a time out will then be added to their row and their row only.

Is TableA field data type for the Employee_Num and MO_Num set to numbers? (that would require some apostrophe's before and after the actual value if that was not the case and set to text) I tested this out and got everything to work - make sure the naming conventions match up in the syntax - feel free to switch them up with underscores if you want in the form and in the code like 'MO_ID' , 'EE_ID' to make it easier on the eyes.

When you ran into the error, if you hover over the variable MoD_Num, does it give you the correct lookup based on the form's value that you inputted?

Ultimately your user inputs will likely be a combo box that is a distinct query of the employee number and MO numbers so that user's can't type text into number fields or what not.

 

[drowe005]

Yes you were right. I ran into some problems and started the project from scratch again, and forgot to update the inputs from text to number. That solved the error I was getting.

Another problem that I seem to have is that each entry isn't added to a new row in the table. If I enter the first employee number and mo number, it adds that to a row in the table and adds a time in as it should. When I go to enter new employee number and mo number combinations, it only updates the original row's time out column, but does not add the new rows of info like it should. Do you know why it would do this? And thanks again for the help so far!

 

[drowe005]

If this would be of any help, here is a copy of the file so you can see how I have things set up:

https://app.box.com/s/6ca7xwiecxw9llxosvggikeznxmpfa4h

 

[drowe005]

BUMP

Anyone else have any input on this?

 

[drowe005]

Bump