Filters in access forms

[OCM]

Hello,
Given my tables screenshots below, I'd like to display two fields in my form where users select from 'active' users from drop down.
1. primary investigator
2. secondary investigator

Currently, the above is stored in a single field: users select from USERID and INVTYPE (primary or secondary). And following is the Row Source:

SELECT tblLOGIN.USERID, tblLOGIN.INVNM FROM tblLOGIN WHERE (((tblLOGIN.STATUS)="A")) ORDER BY tblLOGIN.INVNM;

For historical cases, I still would like my form to display the 'inactive' users who investigated the case.

TIA
Regards,

 

[SoftwareRT]

So, you want to show 'only active users' in the dropdown for 'new selections', but still display inactive users if they were previously selected (for historical data)?

If so, try this Primary Investigator Combo Box Rowsource:

SELECT L.USERID, L.INVNM
FROM tblLOGIN AS L
INNER JOIN tblInvestigators AS I ON L.USERID = I.USERID
WHERE (L.STATUS = 'A' OR L.USERID = Forms!YourFormName!PrimaryInvestigatorCombo)
AND I.INVTYPE = 'P'
ORDER BY L.INVNM;

C.