I have 2 Foreign Key in one table, and how to trap the redanduncy into 2 columns?

[mcapon]

As i said i have 2 Foreign Key In one table, My problem is how can i avoid to save same DATA / Foreign Key in one column. As i know we cant save Same Item Number in One Column...

here is my code for save

On Error GoTo ErrSave
        Dim sqlStr As String
            sqlStr = "Insert Into STRUCTURE(STR_ITEMNO,STR_FLOWER,STR_QTY) values(" & TxtItemNu.Text & "," & TxtFlowerID.Text & "," & CboQuantity.Text & ")"
            Conn.BeginTrans
            Conn.Execute sqlStr
            Conn.CommitTrans
            
            MsgBox "STRUCTURE successfully saved!"
            
            CmdSave.Enabled = False
            CmdDelete.Enabled = False
            
            ClearText
            Unload Me
            Form1.Show
        
ExitErr:
    Exit Sub
ErrSave:
        Conn.RollbackTrans
        MsgBox "Error" & Err.Description, vbCritical
Resume ExitErr

and here is my Whole code...

Dim NewEdit As Boolean

Private Sub CmdAddFlowerID_Click()

    FrameFlowerID.Visible = True
    Dim rs As New ADODB.Recordset
    rs.Open "select ITEM_NO,ITEM_NAME from ITEM ", Conn, adOpenForwardOnly, adLockReadOnly

        If Not rs.EOF Then
            MSHFlowerID.ColWidth(0) = 0
        Set MSHFlowerID.DataSource = rs
        With MSHFlowerID
             .ColWidth(1) = 1000
             .ColAlignmentFixed(1) = 3
             .ColAlignment(1) = 3
    End With
    End If
          
End Sub

Private Sub CmdAddItemnum_Click()
    
    FrameItemNu.Visible = True
    Dim rs As New ADODB.Recordset
    rs.Open "select ITEM_NO,ITEM_NAME from ITEM ", Conn, adOpenForwardOnly, adLockReadOnly

        If Not rs.EOF Then
            MSHItemNu.ColWidth(0) = 0
        Set MSHItemNu.DataSource = rs
        With MSHItemNu
             .ColWidth(1) = 1000
             .ColAlignmentFixed(1) = 3
             .ColAlignment(1) = 3
    End With
    End If
            
End Sub

Private Sub CmdCancel_Click()

    ClearText
    CmdCancel.Enabled = False
    
End Sub

Private Sub CmdDelete_Click()
    
    On Error GoTo ErrDelete
        
        Dim sqlStr As String
            If MsgBox(" You want to DELETE this Record?", vbQuestion + vbYesNo, " Deleted ") = vbYes Then
                sqlStr = " Delete STR_ITEMNO,STR_FLOWER,STR_QTY from STRUCTURE where STR_ITEMNO='" & TxtItemNu.Text & "'"
            
            Else
                Exit Sub
            End If
            Conn.BeginTrans
            Conn.Execute sqlStr
            Conn.CommitTrans
            
            MsgBox "Record Deleted"
            Unload Me
            Form1.Show
            
ExitError:
    Exit Sub
ErrDelete:
    Conn.RollbackTrans
    MsgBox "Error:" & Err.Description, vbCritical
    Resume ExitError
                
End Sub

Private Sub CmdEdit_Click()

    Dim sqlStr As String
    
        sqlStr = "Update STRUCTURE set STR_FLOWER='" & TxtFlowerID.Text & "',STR_QTY='" & CboQuantity.Text & "' where STR_ITEMNO='" & TxtItemNu.Text & "'"
        Conn.BeginTrans
        Conn.Execute sqlStr
        Conn.CommitTrans
        
        MsgBox "Record is successfully updated"
        
        CmdEdit.Enabled = True
        CmdSave.Enabled = True
        CmdDelete.Enabled = True
            
        Unload Me
        Form1.Show
            
End Sub

Private Sub CmdExit_Click()

    End
    
End Sub

Private Sub CmdNew_Click()

    CmdAddItemnum.Enabled = True
    CmdAddFlowerID.Enabled = True
    CboQuantity.Enabled = True
        
    CmdCancel.Enabled = True
    CmdSave.Enabled = True
    
End Sub

Private Sub CmdSave_Click()
            
     On Error GoTo ErrSave
        Dim sqlStr As String
            sqlStr = "Insert Into STRUCTURE(STR_ITEMNO,STR_FLOWER,STR_QTY) values(" & TxtItemNu.Text & "," & TxtFlowerID.Text & "," & CboQuantity.Text & ")"
            Conn.BeginTrans
            Conn.Execute sqlStr
            Conn.CommitTrans
            
            MsgBox "STRUCTURE successfully saved!"
            
            CmdSave.Enabled = False
            CmdDelete.Enabled = False
            
            ClearText
            Unload Me
            Form1.Show
        
ExitErr:
    Exit Sub
ErrSave:
        Conn.RollbackTrans
        MsgBox "Error" & Err.Description, vbCritical
Resume ExitErr
                  
End Sub

Private Sub Form_Load()

    '----------For the COMBO BOX----------
    
         Dim Counter As Integer
         For Counter = 0 To 50
    
              CboQuantity.AddItem Counter
                  Next Counter

                'Now set the number that shows when the form is loaded to the
                'Fist number in the ComboBox
                CboQuantity.ListIndex = 0
                
    '------------------------------------
    
    db2connect
    MSHItemStructure.Enabled = True
    PopulateFlexGrid

    CmdSave.Enabled = False
    CmdDelete.Enabled = False
    CmdEdit.Enabled = False
    CmdCancel.Enabled = False
    
    TxtItemNu.Enabled = False
    TxtFlowerID.Enabled = False
    CboQuantity.Enabled = False
    
    FrameItemNu.Visible = False
    FrameFlowerID.Visible = False
        
    CmdAddItemnum.Enabled = False
    CmdAddFlowerID.Enabled = False
        
    '------------ Functions -----------------
            
            ClearText
            DefaultButtons
    '----------------------------------------
    
End Sub

Private Sub MSHFlowerID_Click()

    With MSHFlowerID
        TxtFlowerID = .TextMatrix(.MouseRow, 1)
                End With
            
            FrameFlowerID.Visible = False
                    
End Sub

Private Sub MSHItemNu_Click()

    With MSHItemNu
        TxtItemNu = .TextMatrix(.MouseRow, 1)
                End With
            
            FrameItemNu.Visible = False
           
End Sub
Function ClearText()
    
    TxtItemNu.Text = ""
    TxtFlowerID.Text = ""
    CboQuantity.Text = ""
    
End Function
Function ControlText(Flg)
    
    TxtItemNu.Enabled = Flg
    TxtFlowerID.Enabled = Flg
    CboQuantity.Enabled = Flg
    
End Function
Function DefaultButtons()
    
    CmdNew.Enabled = True
    CmdSave.Enabled = False
    CmdEdit.Enabled = False
    CmdDelete.Enabled = False
      
End Function
Function DefaultMode()
    
    ControlText
    
    CmdNew.Enabled = True
    CmdExit.Enabled = True
    
End Function


Private Sub MSHItemStructure_Click()

    TxtItemNu.Text = MSHItemStructure.TextMatrix(MSHItemStructure.RowSel, 0)
    TxtFlowerID.Text = MSHItemStructure.TextMatrix(MSHItemStructure.RowSel, 1)
    CboQuantity.Text = MSHItemStructure.TextMatrix(MSHItemStructure.RowSel, 2)
            
        CmdDelete.Enabled = True
        CmdEdit.Enabled = True
        CmdExit.Enabled = True
        CmdCancel.Enabled = True
        CmdEdit.Enabled = True
                        
        TxtItemNu.Enabled = False
        TxtFlowerID.Enabled = False
        
        CmdAddItemnum.Enabled = True
        CmdAddFlowerID.Enabled = True
        CboQuantity.Enabled = True
        
        
End Sub
Function PopulateFlexGrid()
    
    Dim rs As New ADODB.Recordset
    Dim sqlStr As String
        sqlStr = "Select STR_ITEMNO,STR_FLOWER,STR_QTY from STRUCTURE "
        rs.Open sqlStr, Conn, adOpenForwardOnly, adLockReadOnly
            
            If Not rs.EOF Then
                MSHItemStructure.FixedCols = 0
                Set MSHItemStructure.Recordset = rs
                With MSHItemStructure
                    FormatString = "ITEM NUMBER|FLOWER ID|QUANTITY"
                
                    .ColWidth(0) = 1700
                    .ColWidth(1) = 1700
                    .ColWidth(2) = 1550
                
                    
                      .AllowUserResizing = flexResizeColumns
                      .SelectionMode = flexSelectionByRow
                      
             End With
                MSHItemStructure.Enabled = True
             Else
                 
                MSHItemStructure.FixedCols = 0
                With MSHItemStructure
                    FormatString = "ITEM NUMBER|FLOWER ID|QUANTITY"
                
                    .ColWidth(0) = 1000
                    .ColWidth(1) = 1000
                    .ColWidth(2) = 1000
                
                
'               MSHItemStructure.TextMatrix(1, 0) = ""
'               MSHItemStructure.TextMatrix(1, 1) = ""
'               MSHItemStructure.TextMatrix(1, 2) = ""
                
                End With
                MSHItemStructure.Enabled = False
            End If
            
End Function

[techgnome]

what does your table(s) structure look like?

-tg

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[jasonwucinski]

are you talking about having a conflict with primary keys if thats the case, then yes. using a composite of two foreign keys may lead to a non unique primary key. instead, you could use a surrogate key for the primary key (Ie, a GUID or autonumber) and then you would be free to store the two foreign keys without fear of saving more then one set.