title: Implementing Many to Many, Subforms, Checkboxes in Access 2007
body: <p>Person
      Person_Pet
      Pet</p>
  
  <p>I need to make a data entry form.  Let's say we have a table called Person and that has the appropriate fields in it.  On my form I need to allow the user to select from (one or many) checkboxes that represent all the possible pets they could have (fixed list of items: dog, serval, llama, jackalope, emu, dragon, spider...).  </p>
  
  <p>For the form, there needs to be one checkbox to represent each possible choice. If that person does have a pet dog then that checkbox needs to be true and if they don't have a dog it needs to be false (I guess I'm stating the obvious because I got stuck trying to do this as a subform with the "many to many" and displaying "false" valued things.  They can chose more than one.  </p>
  
  <p>Eventually I need to make a new form or reuse the data entry form for modificiations, etc.  </p>
  
  <p>Is this a subform?  I have a Person table, Pet table, Person_Pet (id/joining) table.  I'm trying to go at all this with VBA but I think I've picked the hard way, however, it isn't too late to change directions.  </p>
  
  <p>edit:
  what if i was starting with this?  it would result in something that gave me a list of all possible pets and if the p.personid was null then the checkbox is not checked.  if it is not null then it is checked.  is this possible?  (excuse the formatting, access sql writer doesn't know what a tab is apparently and excuse sytanx errors because i had to do a quick find and replace for table names) </p>
  
  <pre><code>select pet.*, p.personid
  from pet pet 
  left outer join
  (select pi.petID, pi.personid
  from    person,
          pet_person pi,
          pet 
  where   person.id = pi.personID and
          pet.id = pi.petID) as p
  on p.petID = pet.id
  </code></pre>
  
  <p>edit:</p>
  
  <p>okay.  there's a huge answer down there.  i solved it, too.  i haven't looked at your answer but i will in a lil bit.  here's my answer...  (no subform, just all on the main form, "person" form)  </p>
  
  <ol>
  <li>make checkboxes... and name them chk1, chk2, chk3... and so on.  </li>
  <li>make sure they correspond to appropriate fields in my lil pet table.... so dog = chk1, serval = chk2... stuff</li>
  <li>do this vba (and call function from Form_Current() and pass it me.id):</li>
  </ol>
  
  <p>Function update_checkboxes(issueID As Variant)
      Dim query As String
      Dim rs As DAO.Recordset
      Dim db As Database
      Set db = CurrentDb
      Dim a As String</p>
  
  <pre><code>If Not IsNull(issueID) Then
      query = "SELECT iif(joined.issueid is null, 0, 1) as binval, payer.* " &amp; _
              "FROM payer AS payer LEFT JOIN (select ri.issueid, ri.payerid " &amp; _
                      "from  issue i, payer r, payer_issue ri " &amp; _
                      "where i.id = ri.issueid and r.id = ri.payerid and ri.issueid = " &amp; issueID &amp; _
                      ")  AS joined ON joined.payerid = payer.id;"
  Else
      query = "select 0 as binval, payer.* from payer"
  End If
  
  Set rs = db.OpenRecordset(query)
  rs.MoveFirst
  
  Do While Not rs.EOF
      s = rs.Fields("CorrespondingChkboxNumber")
      Me.Controls("chk" &amp; s).Value = rs.Fields("binval")
      rs.MoveNext
  Loop
  
  rs.Close
  End Function
  </code></pre>
  
  <p>Yeah.  that copy paste code format stackoverflow thing is hating on me.  sorry.  UM.  issue = person, payer = pet.  i know the corresponding check box thing isn't idea, i'm going to have to search/check a string field but this shows the concept of what i think i might end up using unless...................</p>
  
  <p><strong>Follow up question:  are there any serious time constraints issues/consequences/sumthing to running these vba queries each time the record is changed?</strong></p>
  
  <p>btw.  fyi.  the desire to show the possible answers with selected answers is because sometimes what things aren't is equally important as what things are.  some areas/counties/municipalities won't allow pitbulls.  the whole state of california apparently outlaws ferrets.  i do have a pitbull.  i do not have a ferret.  we all only wish we had pet dragons to train.  all of these are on the no-no pet list in some areas so.... both these things need to be displayed.  a listbox could do it but i feel it's a little awkward looking.  by visibly listing all my possible pets, i know which ones raise flags. although in the end it only truly matters what i do have....... still.  but obviously i'm not making a cute little access database for people and pets and where they can live.  </p>