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?
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?
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
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?
If you want to delete a record from a bound form, here's a solution:
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
Set rst = Nothing
Where Row_ID is the name of the Identity column (usually the primary key) for the rowset. If Row_ID is not numeric, use:
Sub DeleteRow(ByVal RowID As String)
. . .
rst.FindFirst "Row_ID='" & RowID & "'"
. . .