Current Date and time in a form when record is created

[haylau]

We are considering switching from MS Access to Excel for a simple (??) time logging system

Using our access database, when a worker starts a new part of a job they go to the computer, grab the barcode scanner. They scan their name, the process the are embarking on (e.g Bending, or threading) they then scan the job number of the job they are working on. A record is then created and the record creation time is automatically added to the record. We use the time to calculate how lon a particular job has been worked on for our costings

So looking at excelI can see we can build a table, and use a form for data entry. So the process is identical- BUT i cannot think how to put the current date and time into the record automatically.

Any ideas? (See screen shot for more details)

https://www.dropbox.com/s/keixdzt5u04drv9/05-02-2019 13-05-47.png

 

[Logit]

.
Can you post your existing code ?

 

[haylau]

We don't have any code. I was talking about the inbuilt data entry form. The screen print shows the data as I need it, with the defaul data entry form. But the date and time cannot be entered manually and i can't put a formula in that box either. So the date and time does not actually need to be in the form but it does need to be recored in the correct cell at the time the record is created

 

[Logit]

.
Well .... we could create a User Form that will do the same thing (enter data into the sheet) however, if you are using a handheld scanner to
input some of the data into the form fields ... that is beyond my abilities. If you are ok with manual entry of info into the form's fields, we can
handle that and the date/time would be automatic.

??

 

[ExcelUserFoster]

.
however, if you are using a handheld scanner to
input some of the data into the form fields ... that is beyond my abilities.

??

Most barcode scanners can be programmed to scan a barcode, then simulate the TAB key being pressed. With that in mind, Logit's abilities should be able to complete this

 

[haylau]

You can ignore the scanner part. That just saves on typing for the workers and as mentioned includes the "tab" to move to the next field. No, the only issue is how to automate the current date and time into the required cell

 

[Norie]

There are no events associated with the built-in data entry form so I think you would need to go with Logit's suggestion of building your own userform.

If you do that it would be straightforward to add a date/time stamp to the code that transfers the data from the form to the sheet.

 

[Joe4]

We are considering switching from MS Access to Excel for a simple (??) time logging system

All things equal, Access seems like the better tool for the task. Access is a "database" program, where Excel is not (though people often coerce it to be - it can work, but is a bit clunky and inefficient).
Also, creating Forms in Access is much easier than Excel, as you can easily bind the Table fields to the Form fields (whereas in Excel, you need to explicitly set each one in your VBA code).
And Access handles multiple users being in the program at the same time much better than Excel does.

If you already have a working Access process for this, what is the impetus behind moving it to Excel?

 

[haylau]

Yes, Access is probably better. Just playing with alternatives really and looking for a more straighforward system that others can take care of after my day

 

[Logit]

.
See if this works for you :

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    ws.Cells(newRow, 3).Value = Me.TextBox1.Value
    ws.Cells(newRow, 4).Value = Me.TextBox2.Value
    
    Me.txtFirstName.Value = ""
    Me.txtSurname.Value = ""
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub


Private Sub CommandButton2_Click()
    Me.TextBox2.Value = Now
End Sub


Private Sub UserForm_Initialize()
    Me.TextBox2.Value = Now
End Sub

Download wokbook : https://www.amazon.com/clouddrive/share/iZR3v8dsgqETEMEGL6GCw8ZqsrkpQepgFVJ1wrE9tTx