none
Access Web App -- Guidance on how to have changes in one table update a different table RRS feed

  • Question

  • I have experience with Excel macros, but am an Access Web App rookie. I apologize in advance if this is the wrong forum.

    I am creating an Access Web App for my wife's non-profit, hosted in her Office365 site. So far, this app allows users to enter the scientific name of a plant along with related information (common name, water use, etc.). I have one table, Plant List, that stores all this. It is implemented and works great.

    Now, I would like to allow users to specify in which County the plant can be found. When a plant record is opened, I would like to show all the counties that have already been entered for that plant, and allow users to add a new county or remove counties where the plant is not found. Eventually, we would like to do a query like, "Which plants grow in County A or County B?" or "...County A and County B".

    To do this, I created one table for the list of counties, with just two columns (County ID and County Name), and entered all eight of the local county names into it. I created another table called "Plant Inventory", with three columns: ID, Plant Name, and County Name.

    The outline of what I intended was this:

    1. In the view for Plant List, create a list box or something similar.

    2. In the On Current event for that view, get the name of the current plant, and get the list of all counties from Plant Inventory that match that plant name. Load all of these into the list box or something similar.

    3. Allow the user to change the selection in the list box while editing the record.

    4. When the record is saved, write all the changes back to the Plant Inventory table. e.g. if the user deselected a county, then the record for {Plant Name, County Name} should be removed.

    My problem is that I can't figure out a good way to do this: I can't find a control that seems appropriate for showing the list of counties in the view that allows the user to change selection, and then I'm not sure the best way to update the Plant Inventory table. I can think of ways to do this in Excel VBA, but those methods don't work with Access Web Apps.

    Can anybody give me tips on how to approach this?

    Friday, December 11, 2015 10:35 PM

All replies

  • We are doing the research about your problem. There might be some delay about the response. Appreciate your patience.
    Tuesday, December 15, 2015 9:11 AM