Combo box in dataview problem

[Guest]

Ok I hope I can explain this. I have a table that has 3 fields an ID auto
incrementing field, StaffID field and a RoleID field. In the form/dataview I
display a combo box with all staff names which is bound to the StaffID field
and I have a combo box dynamicly displaying the roles that currently
selected Staff member can perform "the Combo box dosnt display all the
posible roles only that that the staff member can perform". the query behind
the combo box is a stored procedure

ALTER PROCEDURE dbo.GetMyRoles
(@CurrentUserID int)
AS SELECT dbo.Roles.RoleName, dbo.Roles.RoleID
FROM dbo.Roles INNER JOIN
dbo.PeopleToRolesRosetta ON dbo.Roles.RoleID =
dbo.PeopleToRolesRosetta.RoleID
WHERE (dbo.PeopleToRolesRosetta.StaffID = @CurrentUserID)
ORDER BY dbo.Roles.RoleName


This works fine and filters the roles according the staffID and their
assigned roles BUT when you make a selection and then roll over the other
combo box's the fields become blank unless they are one of the option in the
Combo box that was origially opened!!!

I've looked around the internet and it would apear that I need to use
lookup table in the actual table. Ive tried this but I cant get the lookup
query to look a the StaffID filed in the parent table so as to correctly
display the available roles such as

SELECT DISTINCT Roles.RoleID, Roles.RoleName, PeopleToRolesRosetta.StaffID
FROM PeopleToRolesRosetta INNER JOIN
Roles ON PeopleToRolesRosetta.RoleID = Roles.RoleID
WHERE (PeopleToRolesRosetta.StaffID = PeopleToProjectRosetta.StaffID)



PeopleToProjectRosetta.StaffID is the field in the parent or master table
that the lookup needs as as to return the correct roles for the selected
staff member.



Can anyone help me sort this out


regards

Marcel

 

[MacDermott]

The combobox is a property of the form, not of the record. When you change
its RowSource, you are changing it for all records, not just the current
one. So if the data in the combobox doesn't match any of the items in the
RowSource, the combobox *will* show up blank. That's how it's designed to
work.
You may want to rethink your design.

 

[Guest]

sorry macDermont

In access the combo box can be part of the table go into table design and
clicj on the lookup tab.

anyone else can help me here

regards


Marcel

 

[MacDermott]

The principle is the same -
the combobox is defined for the field, not the record.

BTW it is not recommended to have your users working directly in tables.
Forms (which you did mention in your original post) will give you much more
flexibility.
Also few experienced developers use the lookup function directly in the
table. If you search the newsgroups, you'll find lots of posts on reasons.