Results 1 to 11 of 11
  1. #1
    Deeber2 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    23

    Use Listbox to Populate Continuous Subform and Edit Values

    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:
    1. Subform datepickers (for Begin, End, Actual Dates) are non-functional
    2. Checkbox for PhaseActive is non-functional
    3. 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
    Last edited by Deeber2; 10-03-2020 at 02:22 PM. Reason: Change title and fat-finger spelling

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Mark

    Any chance you can upload a zipped copy of the database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    Deeber2 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    23
    I can, but it'll take me some time to remove some of the data. There is PII in it and we can't have that floating around. :O

  4. #4
    Deeber2 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    23
    I created another db and quickly added the forms and stripped down tables.
    Attached Files Attached Files

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Are these assumptions right?
    You want to select items from your listbox and filter the subform to just those items
    You want to fill in the dates
    you want to save the data in subform to projectphase table
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  6. #6
    Deeber2 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    23
    Quote Originally Posted by moke123 View Post
    Are these assumptions right?
    You want to select items from your listbox and filter the subform to just those items
    You want to fill in the dates
    you want to save the data in subform to projectphase table
    Yes, with the last requirement to be that the PhaseTypeDesc value can be changed to something different and all without affecting what is in the PhaseType table.

    Lol, you make it sound so easy.

  7. #7
    Deeber2 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    23
    It doesn't appear this is doable (or at least no one has the time to explain it).

    Anyway, I've decided to go a different, though less desirable, route.

    Instead of populating the subform with the listbox data, I decided to load the final table and use the subform to display it. Changes to the data can be made there.

    The reason why this is less desirable is in the event the user decides they don't want one of the selections, they will now have to delete it out of the ProjectPhase table (something I HATE to do as it needlessly wastes space; I know...space is cheap ). I'll just continue with my soft deletes though the table will have a little more data than I was planning on.

    Does anyone see any major problems with this direction?

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Apologies I hadn't much time to try to work out a solution for you. I did run into some wierd problems with the sample you provided and could not figure out the cause.
    I think you would have needed to use temp tables to do what you wanted originally especially if using continuous subforms. It would be complicated.
    I think you're probably better off going in the direction you are now persuing.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've looking at your dB and trying to understand what you are trying to do. I am probably way off base but here is what I tried.

    It seems to me that
    (I renamed some objects )
    1 Project can have many Project phases. And 1 project phase will have 1 Project. (1-to-many relationship)
    For 1 Project Phase, you can select 1 Phase type description........ and you want to be able to change the Phase type description for a Project Phase without changing the Phase type description in "tblPhasetype" table.

    I am not sure what the list box is for.........


    The reason the "Date controls" did not show a datepicker icon is because the column in the query is NOT a date/time type field in a table. I would have code in the form to update the date fields in the Projects table using the dates that are created in the qryProjectPhases query.
    Attached Files Attached Files

  10. #10
    Deeber2 is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    23
    My apologies ssanfu! I reviewed what I was doing, made some changes and went a different direction. I was over engineering (I have a tendency to do that) and had to re-assess my direction.

    I had brain freeze and was set on making two listboxes work together and thus didn't really want to do what I was doing on the form. What I was trying to do was put in a buffer to control any updates on a form (back them out if they cancelled).

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad you are making progress........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-08-2019, 02:01 PM
  2. Replies: 13
    Last Post: 09-10-2015, 03:37 PM
  3. Replies: 1
    Last Post: 07-10-2014, 03:09 PM
  4. Replies: 4
    Last Post: 05-23-2014, 05:43 AM
  5. Replies: 1
    Last Post: 08-07-2013, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums