Not sure if this is a query, combo box, SQL or VBA problem

[Navy Ken]

Samual Adams is an Area Personnel Security Representative. This role is stored in the table EmployeeVitalDataT as a foreign key from the AuthorityGroupT table. Samuel Adams has vetted John Hancock and determined he has the proper clearance level and need-to-know to access area A. This is recorded in the TempVisitAccess table as a foreign key from EmployeeVitalDataT. The form TempVisitAccessF has a combo-box that is populated by a query that only returns employees that are Area Personnel Security Representatives to record who authorized the access.

Some time later Samuel Adams is reassigned to the cyber team and his record is changed to record this. The problem is now the TempVisitAccessT table still has Samuel Adams EmployeeVitalDataT foreign key (which is good) but the form shows a blank because the query has filtered him out of the combo box (which is bad). I need the combo box to show the historic data.

How do I limit the combo box choices to only current Area Personnel Security Representatives but still show the historic values until another choice is made?

 

[tvanstiphout]

The classic example is: I have an OrderDetail record with a Price. It should reflect the price at the time of data entry.
The solution is to save the Price as part of the OrderDetail record.

Armed with that understanding, can you apply it to your situation?

 

[Navy Ken]

The classic example is: I have an OrderDetail record with a Price. It should reflect the price at the time of data entry.
The solution is to save the Price as part of the OrderDetail record.

Armed with that understanding, can you apply it to your situation?

Well it is, the historic data is still there. But the query that filters out the non Area Security employees prevents the combo box from displaying it. Just to give you an idea of the volume we are dealing with there about 500 employees and about 30 are Area Security at any given time. They don't change often but they do change.

 

[June7]

Review https://stackoverflow.com/questions...ous-form-the-second-combo-doesnt-show-its-val

 

[Navy Ken]

I appreciate your help, but the thread you linked really doesn't apply there is only the one combo box.

 

[MarkK]

Populate your combo using a different source. Create a distinct query on the IDs you must have appearing in the list. Then join that query to your list of people, including in that result query a flag that indicates if that person is still eligible to be selected. You might prefix non-eligible list members names with "x-," indicating they exist on the list for historical reasons. Then, write code on the combo that prohibits selection of people who are not eligible to be selected.

I have this with ex-employees in some cases, maybe they created a PO, or they were the Project Manager of an old job. They still need to appear in the list, but they may no longer be selected from that list.

 

[Navy Ken]

Populate your combo using a different source. Create a distinct query on the IDs you must have appearing in the list. Then join that query to your list of people, including in that result query a flag that indicates if that person is still eligible to be selected. You might prefix non-eligible list members names with "x-," indicating they exist on the list for historical reasons. Then, write code on the combo that prohibits selection of people who are not eligible to be selected.

I have this with ex-employees in some cases, maybe they created a PO, or they were the Project Manager of an old job. They still need to appear in the list, but they may no longer be selected from that list.

Sorry, you kind of lost me. Some times they are no longer on the list because they have moved on, other times they have been reassigned. I'm not sure what you mean by prefix non-eligible list members names. The table has only the foreign keys. and the query is really only processing foreign keys. Yes, the human readable first and last names fields are in the query but only to populate the combo box. The only manipulation on the first and last names is sort order.

 

[June7]

Doesn't matter if there is only one combobox. The combobox RowSource is dependent on value in another control which could be another combobox or a textbox or a listbox, etc.

 

[Navy Ken]

Doesn't matter if there is only one combobox. The combobox RowSource is dependent on value in another control which could be another combobox or a textbox or a listbox, etc.

I'll have another look. I'm not sure how that helps as I need data from another table but, I'll check it out.

 

[MarkK]

If you have a tPurchaseOrder table containing a field called CreatedByEmployeeID, which is a foreign key to a tEmployee table, then you can create a query, say Query1 ...

SELECT DISTINCT CreatedByEmployeeID FROM tPurchaseOrder

Query1 gives you the list of employees you need to show in your combo, but it is only IDs. So create Query2, with SQL something like...

SELECT
    EmployeeID,
    Firstname & " " & Lastname AS Fullname,
    Nz(LastDayWorked, #12/31/9999#) < Date() As IsCurrent,
    POAuthorizationLevel > 0 As IsAuthorized
FROM tEmployee As t INNER JOIN
    Query1 As q
    ON t.EmployeeID = q.CreatedByEmployeeID;

Now you have a list of IDs and Names of employees to show in your combo drawn from the FK data in the tPurchaseOrder table, so it will be complete. In addition, each row in this query, Query2, shows whether the EE is current, and whether the EE is authorized. Use this query to drive your combo.
Finally, when a user selects a name from this combo, use VBA to check if the selected Employee IsCurrent and IsAuthorized, and if they aren't, cancel the update.

Does that make sense? Anyway, that's what I do where I need to show a complete list of the historical FK data, but where some of the options in that list are no longer valid selections.

hth

 

[Gasman]

I had a similar issue when I volunteered with SSAFA here in the UK.
Caseworkers would leave, but I still needed to see who was the caseworker for old cases.
However when I went to add a new client, I only wanted to see active caseworkers.
So I had a Deactivated date (when they left) which was Null for active caseworkers and used the sql below to see only those.

This did not affect the previous cases as the caseworker ID was stored in the Email record.
I initially had a hardcoded date in the criteria around August 2019, but revisiting this can see that current date would have worked just as well.

SELECT Lookups.ID, Lookups.Data
FROM Lookups
WHERE (((Lookups.DataType)='Email') AND ((Nz([Lookups].[DeActiveDate],Date()+1))>Date()))
ORDER BY Lookups.Data;

 

[Pat Hartman]

I need the combo box to show the historic data.

Then, you can't remove the historic data from the combo's RowSource. Any table used as the RowSource for a combo needs to have an ActiveFlg or perhaps an InactiveDate field. I sort the RowSource so that the inactive values sort to the bottom to keep them out of the way. Then in the BeforeUpdate event of the control, I do not allow the user to select an item which is inactive. That lets the old data be visible but keeps the user from selecting the inactive value for a new record.