title: Access Dynamic entry form
body: <p>First off my Access skills are sub-par, but I'm fairly certain that it is the direction I need to go for my project so I'm hoping I can get some feedback to direct my research. I am however well versed at Excel, VBA and Matlab so I'm not completely incapable, I hope.</p>
  
  <p>I need to assemble a system to track emissions (chemicals) from a variety of sources (units). Each unit has its own set of possible emissions and they need to be tracked daily (but also have the capability to be entered for multiple days at the same time).</p>
  
  <p>What I would ideally like to have is an entry form that initially has 2 dropdown boxes, one to select the month/year and the other to select the unit. </p>
  
  <p>Once selected I would like to auto populate a table that has the entire months dates in column 1, and then all of the possible emissions for that unit in the next 2:N columns. Then I would like to query the existing database to populate any data that has already been entered for the date/unit combination.</p>
  
  <p>At this point need to let the users input new data, either for just one day or for numerous days. Then save it to the database.</p>
  
  <p>I have approximately 70 possible emissions and approximately 40 units. I will need to be able to add new units and specify their chemicals as well as add new chemicals to existing units as their duties change. I would also like the ability for multiple users to be entering data simultaneously, which I believe is a hallmark of Access and may not be something I have to "turn on".</p>
  
  <p>I hope that there is a way that Access can help me do this and not necessitate using Excel (which is very clunky in this case).</p>
  
  <p>Thanks in advance,</p>
  
  <p>Ben</p>
  
  <p>The format for the data can simply be:</p>
  
  <pre><code>Date      Unit    Chemical1    Chemical2    Chemical3
  1/1/16    UnitA    0            0.1          0
  1/1/16    UnitB    0            0.3          0.2
  1/1/16    UnitC    0.2          0            0.5
  </code></pre>
  
  <p>I'll need to do more analysis of the data, but given it as shown would be more than sufficient for the remainder of the work.</p>
  
  <p>For the data entry from I would like to have two drop down boxes: (1) Month/Year and (2) Unit Name. And then the form that the users would fill in would list each day of the month in the left-most column and then give all of the chems that unit could emit in the following columns.
  So  if: 
  Unit1 was able to emit ChemA and ChemC
  Unit2 was able to emit ChemB
  Unit3 was able to emit ChemA, ChemB, and ChemC</p>
  
  <p>So the entry form for Jan-2016 for Unit1 would give them the following assuming today is the 3rd and they have entered data for the 1 and 2.</p>
  
  <pre><code>(1)Jan-2016       (2)UnitA
  Date       ChemA       ChemC
  1/1         0.1         0.2
  1/2         0.2         0.1
  1/3
  </code></pre>
  
  <p>Then selecting unit 2 would change the available columns and again show the last 2 days entries and allow for the entry of today</p>
  
  <pre><code>(1)Jan-2016       (2)Unit2
  Date       ChemB       
  1/1         0.1         
  1/2         0.2     
  1/3 
  </code></pre>
  
  <p>Finally Unit3 has all 3 chemicals available and would show the previous data and the blank entries.</p>
  
  <pre><code>(1)Jan-2016       (2)UnitB
  Date       ChemA       ChemB       ChemC
  1/1         0.1         0.1         0.1         
  1/2         0.2         0.2         0.2 
  1/3
  </code></pre>
  
  <p>I don't mind (and would prefer) that all of the dates for a month were populated and not just the next date. So that January would have 31 rows, Feb would have 29 (this year), etc, etc.</p>
  
  <p>01-21-2016
  Wayne, my current tables are:</p>
  
  <p>UnitList</p>
  
  <pre><code>ID    UnitName    UnitOwner    UnitLocation    UnitDesc    AllowedChems
  </code></pre>
  
  <p>The last column AllowedChems, is a multiple selection that is linked to the table ChemicalList which looks like:</p>
  
  <pre><code>ID    ChemicalName    MassUnit    Type
  </code></pre>
  
  <p>Where all of the entries from ChemicalName are possible selection in the AllowedChems field of UnitList</p>
  
  <p>Then I have a table called Tracking with the columns:</p>
  
  <pre><code>ID    EDate    UName    Vent    ThOx    
  </code></pre>
  
  <p>At the end of this list I would like to have a column for each of the chemicals listed in ChemicalList, that way the users can input a number for the mass of that chemical they are emitting and it will be transferred to the correct column. But I'm as yet unable to determine if I'm able to build columns by linking them to the contents of another table. Perhaps there is another way around this?</p>
  
  <p>For example if my chemicals are listed in the ChemicalList table as:</p>
  
  <pre><code>ID    ChemName   
  1     ChemA
  2     ChemB
  3     ChemC
  </code></pre>
  
  <p>Then I want the Tracking table to look like:</p>
  
  <pre><code>ID    EDate    UName    Vent    ThOx    ChemA    ChemB    ChemC
  </code></pre>
  
  <p>And grow each time a new chemical is added.</p>