Table relationships, and Set Checkboxes based on different table

[asp3232]

Hi all,

I'm currently in process of setting up an issue tracking DB which has a main table, a table of issues (approx. 6-10 items), and a table of causes (approx 10=20 items).

The Issues table has 2 fields: the issue description and an Issue Identifier (I1, I2...)

The causes table has a text field for the cause description, and boolean fields for each issue (fields called I1, I2...) Then for each cause I set the value of I1-Ix for each issue as to whether that cause applies to that issue.

There is no relationship currently specified between the issue table and cause table. (I *know* there's a better way to do this, but at least this works for now -- any pointers would be great!)

My main input form is set up with a series of combo boxes in Issue/Cause pairs. The full list of issues is in the first box, and I set the range of the second based on the issue selected using the AfterUpdate event with code like this:

strsql = "Select [causes] from [causes List] where " & Me.Issue1.Column(1)
    
Me.Cause1.RowSource = strsql
Me.Cause1.Requery

A bit of a kludge, but it does work (again, I'd love some ideas on how to make this better).

I'd also like to know what the best way to set up the relationships between the issues and causes would be (including the best way to set the "flags" for which causes apply to each issue -- right now I just set them in datasheet mode, which is a bit awkward).

Thanks for any help you can provide!

asp

 

[LittleSmudge]

Firstly delete the fields I1, I2, .. .. Ix in your causes table because you're right - there is a better way of doing it.

Create another table called tblCauseIssue like this:-

tblCauseIssue]
CauseRef Foreign Key to tblCause
IssueRef Foreign Key to tblIssue


where CauseId is the primary key of tblCause and CauseRef is of the same data type as CauseId
and IssueId is the primary key of tblIssue and IssueRef is of the same data type as IssueId

( Make both of these fields together a combined Primary Key for the table )
Then, for each valid Cause and Issue combination populate the tblCauseIssue with the primary key from each table.

This is the standard construct of creating a Many-to-Many relationship between tblCause and tblIssue.
tblCauseIssue is often refered to as a "Linking table"

The result of doing it this way is that when someone comes along and 'invents' a new Issue - you simply add more records to tblCauseIssue. Doing it your way mean changing the design of tblCause and ALL of the forms that bind to it.


The code for setting up the second combo then becomes something like :-

strsql = "SELECT tblCause!CauseId, tblCause!Description " _
       & "FROM tblCause INNER JOIN tblCauseIssue " _
       & "ON tblCause!CauseId = tblCauseIssue!CauseRef " _
       & "WHERE IssueRef = " & cboIssue
    
cboCause.RowSource = strsql
cboCause.Requery

Have the combo box cboIssue.RowSource = "SELECT IssueId, IssueDescription FROM tblIssue"
ColumnCount = 2
BoundColumn = 1


Finally as for setting up the Cause - Issue linking then a simple Form that has two combo boxes on it.
Bind the Form to tblCauseIssue and bind the first combo to CauseRef and the second combo to IssueRef

Open the form in Add mode ( New Record )
Select Casue from first box, select Issue from second box and then move on to the next new record.

If you've made both fields into a combined Primary Key as I suggested before you will never have any problems caused by a user entering the same combination data twice.


'ope-that-'elps.

G LS

 

[asp3232]

Thanks for the reply! I've got a simple test working with this.

A follow-up, though -- rather than 2 combo boxes for the issue/cause setup, I'd like to have a combobox for the issue, and a listbox (or a series of checkboxes, but a listbox seems simpler to implement) where I could select an issue and then just select its associated casues from the multi-select listbox.

What would be the best way to implement this? The first combo seems easy, and creating the multi-select listbox seems easy, too. Where I'm not sure is 1) pre-selecting causes that already map to the selected issue, and 2) how to check which items are selected to update the issue/cause table.

Thanks again!

asp

 

[asp3232]

Ok, I was able to set up the listbox and even get the selections right by creating a dummy listbox for the causes that map to a particular issue.

But I'm still not sure how to get the data back into the linking table. I'm planning to have a command button to run the code to do it, but don't know what to do -- should I delete all records matching the existing issue, and the append new records for all selected causes (and how would I do that if that is the right solution?)

Any help would be appreciated!

Thanks!

asp

PS -- For anyone who is interested, the code to select (highlight) the existing causes looks like this:

Private Sub cbIssue_AfterUpdate()
    Dim strsql As String
    Dim i As Integer
    
    strsql = "Select [causes list].causenum from CQ1 where [Issue List].IssueNum = " & Me.cbIssue.Column(1)
    
    Me.SelCauseList.RowSource = strsql
    Me.SelCauseList.Requery
    
    'clear existing selections
     For i = 0 To (Me.CauseList.ListCount - 1)
        Me.CauseList.Selected(i) = False
    Next i

    'now get each item in the SelCauseList box and try to highlight it in the CauseList Box
    
    For i = 0 To (Me.SelCauseList.ListCount - 1)
        Me.CauseList.Selected((Me.SelCauseList.ItemData(i) - 1)) = True
    Next i
End Sub

 

[LittleSmudge]

Firstly, appologies for the typo that made so much of a mess of my first post !

In a similar way to the code in you previous post initiate a For loop on the CauseList.Selected

Then inside the loop open a recordset on the tblCauseIssue WHERE Issue matches the combo box and Cause matches the CauseList.Selected

If rst.EOF Then '( No match found )
rst.AddNew '( create new one )
Else
' Skip as record already exists
End If



'ope-that-'elps.

G LS