Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Unanswered: Check Boxes and Join Tables

    I'm trying to program a movie database. I want to have three tables: title, formats, and the join table, Title-Format. Each title and format has a UniqueID so the Title-Format table would have a concatenated key that looks something like:

    Usual Suspects - DVD
    Usual Suspects - BRD


    The problem I'm having is, I want the data entry form to have checkboxes for each format. Enter the title once and check off each of the formats that it's owned in. How can I use a check box to fill-in the two fields on the Title-Format Table?

    Also, say I sell a version and uncheck that format for that movie. How do I get the checkbox to remove the entire record from the join table?

    Thanks for helping me figure out forms!

  2. #2
    Join Date
    Oct 2011
    Posts
    21
    Not sure if I totally understand your question. But lets see if I do understand. In a form you have a check box that you would like to check and when you do it needs to fill out two fields in the table. Am I correct?

  3. #3
    Join Date
    Oct 2011
    Posts
    16
    Yes, the join table has two fields, both foreign keys; the primary keys of Title and Formats.

  4. #4
    Join Date
    Oct 2011
    Posts
    21
    Hopefully this is what you are after.

    There are two things you can do. if you want the checkbox to show checked after reloading the then have at least one of the fields attached to one of your fields, or create a new one just for the purpose of showing a checked box.

    Here is the following information that will make it so you can put a value into two different fields. You will have to do a little bit of vba writing.

    This might not be the best way, but this is how I did it. If you want it to still show up as checked. Then you will bind the actual check box (1) with its name as (Chk1) to one of your fields. That takes care of filling one of the fields, but to do the other. Create another check box (2), with its name (Chk2). Bind this check box (2) to the other field you wish to have changed. Then on the properties of check box (2) and put visible as "no". Then, you are going to go to the first check box and on properties, you want to go to "on Click" click in the field and then you will see a "..." click on it. Then you want to click on code builder. This will take you into vba. Then insert the following code:

    Private Sub Chk1_Click()
    On Error Goto Err_Chk1_Click

    me.Chk2=me.Chk1

    Exit_Chk1_Click:
    Exit Sub

    Err_Chk1_Click:
    MsgBox Err.Description
    Resume Exit_Chk1_Click

    End Sub


    Then close the vba. Save the form. And give it a try. If there is a problem let me know. I didn't test this, but it should work. You could also put this code under the after update too.

    Please let me know if this worked.

    Thanks!!
    Darg

  5. #5
    Join Date
    Oct 2011
    Posts
    16
    Hmm. This is close but, the fields are being filled-in with 0 and -1 (Checked and unchecked) rather than the primary keys of the Title and Format.

    Thanks for this help, though!

  6. #6
    Join Date
    Oct 2011
    Posts
    21
    Then all you need to do is is make a text box, with the primary key linked with it and it should do the same thing.

  7. #7
    Join Date
    Oct 2011
    Posts
    16
    Okay, almost there now. Thank you for reminding me about having invisible fields on the form.

    I've got the checkbox and the two invisible text boxes like you recommended. For the after check event, I've got the following code:

    If Check23 = 0 Then
    Me.FrmtID = Null
    Else
    Me.FrmtID = 1
    Me.TtlID = TitleID
    End If


    However, if the box is unchecked I want it to delete the record from the Title-Format Table altogether. Can you help me with the VBA to do that?

  8. #8
    Join Date
    Oct 2011
    Posts
    21
    Hmm. Here is the code to delete the specific record that is selected.
    Private Sub Delete_Click()

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    End Sub

    Hmm to delete the entire record you might have to do something kind of messy. You might have to create an additional form that is invisible. Then have the particular record that you want deleted selected. Is the primary key the same for both tables for the particular record set?

  9. #9
    Join Date
    Oct 2011
    Posts
    21
    Sorry the code above does delete the whole record set, to delete the other linked table you might have to do something else. Is the primary key the same in both tables?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to delete a record from a bound form, here's a solution:
    Code:
    Sub DeleteRow(ByVal RowID As Long)
    
        Dim rst As DAO.Recordset
        
        Set rst = Me.RecordsetClone
        rst.FindFirst "Row_ID=" & RowID
        If rst.NoMatch = False Then
            Me.Bookmark = rst.Bookmark
            rst.Close
            Me.Recordset.Delete
            Me.Refresh
        End If
        Set rst = Nothing
            
    End Sub
    Where Row_ID is the name of the Identity column (usually the primary key) for the rowset. If Row_ID is not numeric, use:
    Code:
    Sub DeleteRow(ByVal RowID As String)
      . . . 
        rst.FindFirst "Row_ID='" & RowID & "'"
      . . .        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •