Updating a query upon which a combo box lookup is based

[FlyingMonkey]

Hi, I'm a newbie here so if this has already been answered, I apologise.

I have a form which has a combo box. This combo box looks up values in a query. When a user picks an option from the lookup list, it inserts the detail into the form and the PK into the underlying table.

All good so far.

The lookup is based on a query on the Employee Table. The employee table has EmployeeID, EmployeeName, StartDate and FinishDate. The query only lists current staff (Finish Date = Null)

My problem is that when an employee leaves and a finish date is added, that Employee's name disappears from all records they were associated with in the main form (A split form).



It seems the info is still in the table but not showing up in the form. I have the table hidden from users. I'm using a split form as the user wants to see certain info for several records in a spreadsheet like form (as they used Excel up to now).

Is there a way of keeping old info in the form which no longer shows up in the drop down box?

[ranman256]

It could could be the underlying combo (or sub form) query that is removing items with end dates. Check the ROWSOURCE sql .

[Missinglinq]

This is normal behavior, and why I never Bind a Combobox to a Field in the Form's underlying Table, but rather assign the value from the Combobox to a Textbox. Simplest approach, at this point, would be to add a Textbox and Bind it to the Field. If you want to get fancy you can place it directly over the Combobox and make either the Combobox or the Textbox visible, based conditionally on the Finish Date field.

Linq ;0)>

[June7]

This is known and expected behavior for combobox in continuous or datasheet view form where combobox is multi-column and displays alias. If the record with the lookup alias is filtered out of the RowSource, then the alias is not available for display in the combobox.

However, this does not prevent records with FinishDate from be included in the form RecordSource, only prevents the lookup alias in the combobox. One way to have the related employee info available is to include the Employees table in the form RecordSource (join type "Include all records from {the primary table of form} and only those records from Employees that match"). Then bind textboxes to the fields from Employees table and set them as Locked Yes and TabStop No.

[FlyingMonkey]

Sorry I haven't had a chance to update this. But I got it working by having the combo box looking up a query with only some records showing. I have used a text box looking up the underlying table to display the record from that. So even if an option is removed from the query (and the combo dropdown box) the old detail still shows, as it just wasn't displaying in the form but wasn't deleted from the table. I then layered the combo box on top of the list box.

Hope that makes sense. Confused myself typing it out.