Combo Box Dilema

[FranD]

I have a combo box on a form which allows users to select only those Divisions ([Div_ID]) which have a current status of "Active." To do this, I've set the combo box's underlying query criteria for the [Inactive] field to "No".

Here's the problme. After a Division status is changed from Active to Inactive, the Division name is deleted from the combo box in the records affected by the status change. Although the value still exists in the table, the users will need to occasionally edit records - and they only have access to the form.

How can I keep the Division name in those records, even after the Division's status has been changed????

 

[Travis]

Add an extra where statement to your query:

Where [Inactive] = "No" Or [Div_ID] = Forms![Your Form Name].[DIV_ID Field Name]

Notice I use "Or"

 

[FranD]

Travis, I entered the where statement in the criteria row of my query (under the [InActive] field - but the result is that all the Div_ID's are now deleted.

Am I putting the statement in the wrong place? I'm pretty new to all this, so I'm sure there's some really simple thing I'm not aware of. Just in case, here's the SQL statement - withouth the "Where" statement.

SELECT DISTINCTROW Dept_Sect_BA.Div_ID, Divisions.Div_Name, Divisions.[Group#], Dept_Sect_BA.InActive
FROM Divisions INNER JOIN Dept_Sect_BA ON Divisions.Div_ID = Dept_Sect_BA.Div_ID
GROUP BY Dept_Sect_BA.Div_ID, Divisions.Div_Name, Divisions.[Group#], Dept_Sect_BA.InActive
HAVING (((Dept_Sect_BA.Div_ID) Not Like "*-*") AND ((Dept_Sect_BA.InActive)=No));

Thanks again for your help!!