multiple selection on form (list box)

[Misiek]

Hello,

I am trying to resolve a problem with selecting multiple records.

I have a table called T_user and bound form called F_user.
This form displays all 3 fields from that table.
When I want to add a new user, I enter a name and select a department.

Some of the users work for few departments, so here is the question:

How to select multiple departments for one user?

The department field in F_user is set to LISTBOX, and the multi select option in properties is set to SIMPLE.

I can select multiple departments, but as we know it won't save in the table, it will leave it as NULL value.

I can't use LOOKUP WIZARD as everyone on this forum will tell it it's an evil.

How can I save the record then?

p.s.
I another form I need to select users from specific department, so lets say A.Smith, need to be visible in those few selected ones.

I have seen this:
http://www.baldyweb.com/

form this post
http://www.access-programmers.co.uk/forums/showthread.php?t=269981

but unsure how can I make it work on my form.

Thanks

 

[Uncle Gizmo]

You need to add a subform to your "F_user" that links to a "UserInDepartment" table.

tblUserInDepartment

Fields:-

ID .............. UserID ............ DeptID
1 ................. 100 ................. 11
2 ................. 100 ................. 09
3 ................. 100 ................. 67

ID ------- Some will say you don't need
UserID --- Linked Child field, master will sort this out automatically for you.
DeptID -- Use a Combobox to select from your department table

 

[Misiek]

Right,
How would relationship change after this?

I have table t_user and t_department. They have a one-many relationship.
In my understanding, you suggest to create another table that will hold FK for userID and departmentID.
Will my current table t_user store any value in deptFK field after this?

 

[Uncle Gizmo]

I don't think you should be worrying about relationships at this stage, they only add a level of confusion especially if you don't know how you are going to go about it.

 

[Uncle Gizmo]

>>>Will my current table t_user store any value in deptFK field after this?<<<

Thats a design decision you need to make. I would be inclined to say no, keep the dept info in one place. Having the info in two places just don't make sense.

 

[Misiek]

Thanks for that.

I am a bit lost, so

1. do I leave my current relationship between table user and department as it is?
2. do I make any changes to my current table fields structure?