I have a combo box that was used to select a procedure code. Initially the lookup table to the Procedure code only had a procedure code and a procedure description. I needed to add a start date / end date to the look up table to give them the ability to change the description to the procedure code based on the date the procedure was performed or just put an ending date so that procedure code can't be selected any more. So I need the combo box to filter and only show procedures that fall within the date range of the procedure date performed.
For example Procedure code B1.1 with a description of Bone Marrow Transplant would have a start date of 01/01/2021 and end date of 04/30/2021 and a new record would be added as Procedure code B1.1 with a description of Bone Marrow Transplant - Adult with a start date of 05/01/2021 and a blank end date to signify it is the current active description.
When they enter information for a record they would enter a Procedure date on the form of when the procedure was performed. Based on that procedure date the combo box would filter the list of records to only show the procedure codes/procedure descriptions where the Procedure date falls within the Start/end date of the lookup table.
I think I need to build a string to assign to the RowSource of the combo box but I'm not quite sure how to build the string to include the date filter and which event to place it on in order for it to work. I started with just trying to assign it without the filter:
Me.cmbProcedure.RowSource = "SELECT [tblLUProcedure].[Procedure], [tblLUProcedure].[ProcDesc] FROM tblLUProcedure;"
Or can I create a query that I reference the procedure date from the form so it can filter the records?