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

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

Code:

---

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

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

---

what does your table(s) structure look like?

-tg

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

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.