Hi all.
Once upon a time I was a hard core Access VBA coder, but time has passed and I'm now in the Data Warehousing game. Nevertheless, Access coding has always been in my blood so I've taken on a project and trying to dust off those old skills.
On to the problem at hand:
I am using a query bound multiselect listbox (I needed to add some fields) that is based on a table.
Main form = frmPhase
Listbox.Name = lstPhaseType
Query = qryProjectPhase (based off the PhaseType table)
I want to be able to select multiple, non-contiguous, items from the listbox and display them. Currently, I'm using a subform with default view = Continuous Forms for this.
Subform = sfrmProjectPhase
Now the crux of the matter: I need to be able to change the data in the subform and NOT have it affect the underlying table (PhaseType).
How this all works:
Every project has multiple phases (each project can have a different selection of phasetypes). There are 13 standard phasetypes to select from; one or more.
The requirement is to allow the ability to change the PhaseType name (from standard description to something custom), add in the Begin Time, End Time and Actual End Time (thus the query to add the extra fields). They can also decide (typically at a later date) to delete a phase (in the database it will be a soft delete).
Tables
Project = ProjectId (that's all that is pertinent for this part)
PhaseType = PhaseTypeId, PhaseTypeCode, PhaseTypeDesc, PhaseTypeOrder, PhaseTypeActive
ProjectPhase = ProjectId, PhaseTypeId, PhaseTypeAlias, BeginDate, EndDate, ActualDate, PhaseTypeOrder
Queries
qryPhaseType = PhaseTypeId, PhaseTypeDesc, "" AS BeginDate, "" AS EndDate, "" AS ActualDate, 1 AS PhaseActive, PhaseTypeOrder WHERE PhaseTypeActive = True ORDER BY PhaseTypeOrder
Forms
frmPhase = main form
sfrmProjectPhase = subform; continuous forms
The Listbox (lstPhaseType) is on the mainform and populated via RowSource = qryPhaseType
The Subform (sfrmProjectPhase) is on the mainform. I've tried tying it to the Listbox, but the following tends to happen:
- Subform datepickers (for Begin, End, Actual Dates) are non-functional
- Checkbox for PhaseActive is non-functional
- Any changes to the data are reflected in the base table (I understand why, but that can't be).
I hope this is a sufficient description and explanation.
Any help with this would be GREATLY appreciated!
Mark