MS Access - Form - Add user name after Importing data from Excel

Hi,

I have a form that Import data from Excel file. There are two fields as Timestamp and Username. I n time stamp default value is set as Now(), so when data is imported for all the record in Timestamp field a date and time is added, somehow similar i want to enter User name also with each record. There is a login form where user enter their login and password, so this import form get User name from that form. I want that when data comes in Access it should get that user name also in field User name. I try working with default value but it's not working similar to i did for timestamp but no success. Any idea on how to make this work?

Thank you.
LVL 1
WSStudentAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
That is right. The default value is controlled by the form and not used at the table level.

So update the field after import. Expand your import function to hold this code block:

    MsgBox "Import Successful"
    MsgBox "Click on Add to add data to Tables"
    Me.sfrmImport.Requery
 
    're-enable ms access warnings
    DoCmd.SetWarnings True
    
    Dim rs  As DAO.Recordset
    Set rs = Me!sfrmImport.Form.RecordsetClone
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While rs.EOF = False
            If IsNull(rs!Entered_by.Value) Then
                rs.Edit
                    rs!Entered_by.Value = Me.txtUName.Value
                rs.Update
            End If
            rs.MoveNext
        Wend
    End If
    rs.Close
 
Exit Sub

Open in new window

Tested and works.
0
 
Gustav BrockCIOCommented:
Set the default value after entering/updating the login name:

Public Sub LoginName_AfterUpdate()

    Me!UserName.DefaultValue = Nz(Me!LoginName.Value)

End If

Open in new window

0
 
WSStudentAuthor Commented:
it's not working, any idea why?

Private Sub Entered_By_AfterUpdate()
 Me!Entered_By.DefaultValue = Nz(Me!txtUName.Value)
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
It's the other way round:

Private Sub txtUName_AfterUpdate()
    Me!Entered_By.DefaultValue = Nz(Me!txtUName.Value)
End Sub

Open in new window


or:

Private Sub Entered_By_AfterUpdate()
    Me!txtUName.DefaultValue = Nz(Me!Entered_By.Value)
End Sub

Open in new window

0
 
WSStudentAuthor Commented:
This
Private Sub txtUName_AfterUpdate()
Me!Entered_By.DefaultValue = Nz(Me!txtUName.Value)
End Sub

Open in new window


is also not working. I want this Entered_By field which is in subform to get the Username form text box (txtUName).
0
 
Gustav BrockCIOCommented:
Then it is:

Private Sub txtUName_AfterUpdate()
    Me!NameOfYourSubformControl.Form!Entered_By.DefaultValue = Nz(Me!txtUName.Value)
End Sub

Open in new window

0
 
WSStudentAuthor Commented:
Attach is a sample DB. That would be really helpful if you check this out as the above code in not working. Thank you.
Sample-DB.accdb
0
 
Gustav BrockCIOCommented:
Ah, forgot it has to be a string expression, so quotes are needed:

    Me!sfrmImport.Form!Entered_By.DefaultValue = "'" & Nz(Me!txtUName.Value) & "'"

Open in new window

0
 
PatHartmanCommented:
1. there is no date stamp in the table.
2. If you want to add the two values as you append the data rather than having to scroll through every single record and update it, you need to change your import procedure.  In stead of importing the spreadsheet, link to it.  Then run an append query to copy the data from the spreadsheet to the permanent table.  You can define the Date field on the table to have a default of now() so as each row is added, Access will automatically add the date.  However, you will have to take care of the username yourself.  In the append query, append the form field to the table field.  This is the append query:

INSERT INTO tblImportTemp ( Country, Product, Flow, Years, [Values], Show, [Current], Source, Notes, DataType, Entered_by )
SELECT s.Country, s.Product, s.Flow, s.Years, s.Values, s.Show, s.Current, s.Source, s.Notes, s.DataType, Forms!frmImport!txtUName
FROM sheet1 AS s;
0
 
WSStudentAuthor Commented:
@Gustav, it's not working. For new record it shows the User name but for the record that i import from excel it doesn't show User name at all.

@PatHartman,

1. As it a sample DB so didn't added the Timestamp field.

2. Data and time is working fine, i don't have issue with that but User Name is not working. Can you explain more in detail about  scrolling to each record for User Name?

Also attach is a sample DB with Timestamp.
Sample-DB.accdb
0
 
WSStudentAuthor Commented:
Thanks a lot Gustav it work.

Thanks PatHartman for help.
0
 
PatHartmanCommented:
You're welcome.
The query I posted should have worked.  Rather than importing the data directly to your permanent table (which is always a bad idea), you link to the spreadsheet and then use append queries that allow you to validate data and add additional data as the modification I made did.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.