Update multiple table fields based on single value entered into form

[mindbender]

I have a login form - user enters ID and Password and hit go button
When they hit go the login form moves to the back but stays open and the main form opens and filters the records returned based on the id entered in the login form
When they create a new record I have the id field auto-populating with the id they entered into the login form like this [Forms]![Form]![Field].

I have an employees table that has the login id stored along with many other employee related fields, I am wondering if there is a way when they hit the add record button to not only auto-populate their id but also their name and their manager's name into their respective fields?

Currently under the Add record button is a macro
On Error - Go to next
Record - New
If [Macro Error]<>0 Then
messageBox Message = [MacroError].[Description]


End If

and I have the id field auto-populating by setting the Default Value of the form field to the value entered into the Login Form (=[Forms]![Logon_Form]![cboEmployee])

Hope this all makes sense

Thanks in advance for your help

B

[ranman256]

you could use Dlookups to fill it in

txtUserID = Environ("Username")
txtUserName = Dlookup("[username]","tUsers","[userID]='" & txtUserID & "'")
txtMgr = Dlookup("[Manager]","tUsers","[userID]='" & txtUserID & "'")

[ssanfu]

Why would you want to store data redundantly?
I understand saving the employee ID when creating a new record, but you shouldn't also save the emp name/super name.

If you want to see the emp name/super name on the form, have unbound text box controls and put the names in them.

[orange]

I agree with Steve. If you have an Employees table, that's where you'd store EmpId, EmpFirstName, EmpLastName etc.
In related tables all you need to store is the EmpId. When using the related table, you join it/them with the Employee table and all the fields in the Employee table are available to you (via the join).
Good luck

[mindbender]

I am a bit lost. the data entry form is pulling from the main table. So should I update the form to pull from a query that links the main table and the employee table? And how would I update the employee fields on the form when a new record is created?

thanks for the help on this

[NTC]

Don't confuse display of data with storage of data. You have the full name, etc. data already stored in the Employee data. So in any other table all you need is the unique ID stored. On any form or report in terms of presentation you can call in the info and one method is given to you via 256's post.

Depending on the situation one can also join it in via the query to make it part of the record set, or if a combobox is involved you can include those fields and call them from the combobox control - - more than one way to do this

[mindbender]

Thank you for the help.