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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.