Strange Problem

[music_al]

This is a bit difficult to explain, but I'll try...

I have 2 tables, lutbl_Role and tbl_Staff. The Staff table has a field "Role" which references the Role table at form level.

I have a 3rd table, tbl_Project (presented on a form; frm_Project) which has a sub form; frm_Resources which updates a junction table; jntbl_Project_Staff, which is a datasheet in frm_Project.

When I select a Role type in the datasheet, a query is run which refreshes the Row Source for the next field which is a combo box. So, I select "Project Manager" the next field only lists Project Managers from the Staff table which is what I expect to happen. BUT, When I go to the next row (record) of the datasheet, the Staff name disappears but if I click back to the previous record, it appears again.

What is going on and how do I fix this ??

 

[John Big Booty]

I think you problem lies in the fact that a form in Data sheet view is essentially a special case of a continuous form. Now when you change focus from one record to another the dependency between, all of, the two combo boxes on your form, changes to the dependency between the two combo boxes of the record that currently hold focus, and this is reflected across all the entirety of the form.

 

[music_al]

Thanks Big John

Is there any way to fix this ?

 

[John Big Booty]

What I have done in the past to get around this sort of problem is to have a continuous form that is populated by a query. This form is only used for display purposes and any additions or editing is done via a pop up form (bound to the underlying table/s), which refreshes the underlying query when it is closed.