Populating multiple form fields based on another value of a combo box

[BradCustom]

Hi everyone,
I've been reading a lot of posts on this subject but I haven't found one that is the same as what I'm trying to accomplish.

Here's what I'm trying to do.

I have a linked SQL table called Employee, I'm trying to create a form that will enable the user to select the last name of a new employee. Once the name is selected three text or list boxes are populated with First_Name, Employee_ID and Department. After those fields are populated I'd like to save these fileds into the Access table tbl_Student_Info.

Any ides on how to accomplish this will be greatly appreicated.
Thanks!

 

[dhookom]

Add the three columns to the employee combo box and then in the After Update of the combo box, use a little vba to set the values of the fields in tbl_Student_Info to column values.

If you can't figure this out, please come back with the combo box Row Source as well as the field names in tbl_Student_Info

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

Thanks for your responce, I've been trying to figure this out based on other posts but so far I haven't had any luck.

The name of the combo box is Employee and my current row source is below:

SELECT [dbo_Employee].[Employee], [dbo_Employee].[Last_Name], [dbo_Employee].[First_Name], [dbo_Employee].[Department] FROM dbo_Employee ORDER BY [Last_Name];

The table I'm trying to store the data into is tbl_Student_info

The fields in the access table and linked SQL table are listed below:

tbl_Student_Info / dbo_Employee
Student Number / Employee
Last Name / Last_Name
First Name / First_Name
Company / Department

Hope this helps and please let me know if you need anymore information.
Thanks

 

[dhookom]

In the After Update event code for Employee, try code like:

Me.[Last Name] = Me.Employee.Column(1)
Me.[First Name] = Me.Employee.Column(2)
Me.Company = Me.Employee.Column(3)

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

Thanks Duane that worked!!

Now how do I get the remaining columns to polulate either a text/list or combo box?

That's the part that has me stumped.

Thanks!

 

[dhookom]

What other columns? You have mentioned only a few that I think we have taken care of.

Also, do you have a good reason for storing the same information in multiple tables? This is typically not a good design.

Duane
Hook'D on Access
MS Access MVP

 

[BradCustom]

I was talking about the four columns I've already mentioned.

The more I think about it, the more I think you're right about storing the data in another table.

The only reason for doing that is because I didn't want to use the linked SQL table but now I'm thinking why don't I use it. I guess sometimes I can't see the forest through the trees.

I may still have a need for this function but on a different form.

Thanks for your help!

 

[dhookom]

The code that I provided does populate last name, first name, and company.

Duane
Hook'D on Access
MS Access MVP