Auto Populate Form using Combo Box's

[busterbaxter]

Hello,

I'm using access 2007 and have one table (Assets). In my table there is a User field (Assets.User) and Deptartment. field (Assets.Department). On my form I have a combo box for User (cboUser) and a combo box for Department (cboDepartment). Currently they are both individual drop downs with the User and Group names listed in the row source.

On the form, the data entry person will select a user. Once that User is selected how do I autopopulate the Department field on the form?

Thanks,

Kevin

[Aric Green]

If you don't have very many users a simple way would be to code the on change or after update (can't rememer which one off the top of my head) something like below. Also need to change the combobox for the department to a textbox. I hope this will help.

if comboboxUser is "Name1" then
textbox = "Department 1"
else if comboboxUser is "Name2"then
textbox = "Department 2"
end if
 

[busterbaxter]

Thanks Aric,

I have too many users to go throw this. Also I allow people to enter new User names in the user field. I'll have to update a file or the database to add the department.

I'm still not clear on how to link the user to his or her department.

Kevin

[OldBirdman]

Table = Assets
Key Department User
1   Accounting John
2   Accounting Sue
3   Sales      Peter
4   Personel   Nancy
5   Accounting Frank
 

I see your table as above. In this case, selecting User from a combo box would find a single Department, and no selection (and no combo box) would be necessary. The result would be a single Department. The code might be:
cboUser.ControlSource = "SELECT Department, User FROM Assets ORDER BY User;" In design view for your form, the Bound Column for cboUser would be 1 and the Row Source Type would be Table/Query. Then txtDisplayDepartment = cboUser would assign the Department to a text box, no operator action needed.

To work the other way, and select the Department, then cboDepartment.ControlSource = "SELECT Department FROM Assets ORDER BY Department;" Once selected (Change event), then cboUser.ControlSource = "SELECT User FROM Assets WHERE Department=""" & cboDepartment & """ ORDER BY User;" Both of these combo boxes have the bound column = 1.

You will have to set Column Count and Column Widths for these combo boxes in design view. In the first example, Column Widths will be 0";2" or whatever you need, but the 0" hides the first column.

Better database design would have 2 tables, Departments and Users. The FROM clause would change in the above examples.

OldBirdman