Implementing Many to Many, Subforms, Checkboxes in Access 2007

1
votes

Person Person_Pet Pet

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...).

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.

Eventually I need to make a new form or reuse the data entry form for modificiations, etc.

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.

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) 

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

edit:

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)

  1. make checkboxes... and name them chk1, chk2, chk3... and so on.
  2. make sure they correspond to appropriate fields in my lil pet table.... so dog = chk1, serval = chk2... stuff
  3. do this vba (and call function from Form_Current() and pass it me.id):

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

If Not IsNull(issueID) Then
    query = "SELECT iif(joined.issueid is null, 0, 1) as binval, payer.* " & _
            "FROM payer AS payer LEFT JOIN (select ri.issueid, ri.payerid " & _
                    "from  issue i, payer r, payer_issue ri " & _
                    "where i.id = ri.issueid and r.id = ri.payerid and ri.issueid = " & issueID & _
                    ")  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" & s).Value = rs.Fields("binval")
    rs.MoveNext
Loop

rs.Close
End Function

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...................

Follow up question: are there any serious time constraints issues/consequences/sumthing to running these vba queries each time the record is changed?

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.

2
ms-accessvba
regarding your Follow Up: When you move from one dataset to another there is so much going on behind the scenes that this little vbacode doesn't make any difference. The really time-consuming part would be the SQL - and that scales well (i.e. it doesn't take longer if you have more datasets in pet_person) - and running through the whole petlistbox would only be a problem if there were thousands of datasets in the pet-table which would pose other problems with your design first;) - Johanness
another thought: some people take the time to think about your questions, make - hopefully - valuable contributions and sometimes help you big time for free. You can show them respect in edititng your question wehen you dont get it right the first time - or even better: write it in you local editor and paste it in when you have it... Just a remark - no offense - Johanness

2 Answers

1
votes

Use a subform. Multiple checkboxes border on the insane :)

You need a table:

PersonID
PetID

PetID will be a combobox with a row source that lists possible pets. PersonID will be the link child and master field.

You do not need any code to do this.

1
votes

With VBA you can use the following code. There are two options:

  1. with the 'native' Access Listbox: this shows your selection as highlighted lines in a conventional listbox

    chose multiselect=1 (Single) in the listboxes property/all
    sheet and modify the below code -> uncvomment the commented lines and comment the ones above instead

  2. with the MSForms.Listbox (chose activeX insert in Formcreation)
    here you get the checkboxes you wanted.

    chose Multiselect:multi and ListStyle:option. Here there is no click event, so I chose the Exit event instead. I havent encountered any problems so far, but methodically a click event would be nicer.

And here's the code:

Option Compare Database
Option Explicit

Dim pPetListBox As MSForms.ListBox

Private Sub FillPetListbox() 
  pPetListBox.Clear
  Dim rst As DAO.Recordset
  Set rst = CurrentDb.OpenRecordset("select * from pet order by id")
  While Not rst.EOF
    pPetListBox.AddItem rst!ID
    pPetListBox.List(pPetListBox.ListCount - 1, 1) = rst!petname
    rst.MoveNext
  Wend
  rst.Close
End Sub

Private Sub Form_Current()
  Dim rst As DAO.Recordset
  Dim indx As Long
  Set rst = CurrentDb.OpenRecordset("select * from person_pet where personid=" & Me.ID)
  For indx = 0 To pPetListBox.ListCount - 1
    rst.FindFirst "petID = " & pPetListBox.Column(0, indx)
'    rst.FindFirst "petID = " & petList.ItemData(indx)
    pPetListBox.Selected(indx) = Not rst.NoMatch
  Next
  rst.Close
End Sub

Private Sub UpdateLinkTable()
  Dim rst As DAO.Recordset
  Dim indx As Long
  Set rst = CurrentDb.OpenRecordset("select * from person_pet where personid=" & Me.ID)
  indx = pPetListBox.ListIndex
  rst.FindFirst "petID = " & pPetListBox.List(indx, 0)
  'rst.FindFirst "petID = " & petList.ItemData(indx)
  If (pPetListBox.Selected(indx) And rst.NoMatch) Then
    rst.AddNew
    rst!personID = Me.ID
    rst!petID = pPetListBox.Column(0, indx)
    rst.Update
  Else
    If ((Not pPetListBox.Selected(indx)) And (Not rst.NoMatch)) Then
      rst.Delete
    End If
  End If
  rst.Close
End Sub

Private Sub Form_Load()
  Set pPetListBox = Me.msfPetListBox.Object
  FillPetListbox
End Sub

Private Sub msfPetListBox_Exit(Cancel As Integer)
'Private Sub petList_Click()
  UpdateLinkTable
End Sub

If you use the native access listbox you dont have to load the listbox content programmatically (FillPetListBox). Instead you can just set the rowsource-property to the pet-table. I think it is even possible to bind the MSForms.listbox to that table - but I didn't try it out (yet).