Flexgrid to Table - Compare contents 1

[LiLgUrL]

Here's my code...

Set rs = New ADODB.Recordset
With rs
.Open "Select * from tblPartNumber", conn, adOpenForwardOnly, adLockPessimistic

For i = 1 To flgPartNum.Rows - 2
If !PartNumber = flgPartNum.TextMatrix(i, 0) Then

!PartNumber = flgPartNum.TextMatrix(i, 0)
!GenDesc = flgPartNum.TextMatrix(i, 2)
!PartDesc = flgPartNum.TextMatrix(i, 3)
End If

If !PartNumber <> flgPartNum.TextMatrix(i, 0) Then
rs.AddNew
!PartNumber = flgPartNum.TextMatrix(i, 0)
!GenDesc = flgPartNum.TextMatrix(i, 2)
!PartDesc = flgPartNum.TextMatrix(i, 3)
End If
Next
rs.Update
rs.MoveNext
End With


I want import the contents of the Flexgrid to my table tblPartNumber ... but with some consideration... if from the flexgrid the partnumber is already existing(in tblPartNumber) then it will edit the record and if not existing will add new record... i think there's a problem with my condition... please advice

 

[garths2]

LiLgUrL,

Here's the way I would do it:

' start code
Set rs = New ADODB.Recordset

With rs
.Open "Select * from tblPartNumber", conn, adOpenDynamic, adLockPessimistic

For i = 1 To flgPartNum.Rows - 2
rs.Find "PartNumber = '" & flgPartNum.TextMatrix(i, 0) & "'"
If rs.EOF Then
rs.AddNew
End If

!PartNumber = flgPartNum.TextMatrix(i, 0)
!GenDesc = flgPartNum.TextMatrix(i, 2)
!PartDesc = flgPartNum.TextMatrix(i, 3)

rs.Update

Next
rs.Close

End With

' end code

version of ADO I used is 2.7.
-GS

 

[LiLgUrL]

hi garths2 thanks for the prompt reply i tried your code... but just like mine when the PartNumber is existing I receive an error "The changes you requested to the table were not successful because they would create duplicate vaules in the index..." i can't figure where the error lies... because at any angle i think the logic is correct. Please help... i am running out of time with this... been doing this for a long time now

 

[LiLgUrL]

oh i think i got it...

' start code
Set rs = New ADODB.Recordset

With rs
    .Open "Select * from tblPartNumber", conn, adOpenDynamic, adLockPessimistic

For i = 1 To flgPartNum.Rows - 2
    rs.Find "PartNumber = '" & flgPartNum.TextMatrix(i, 0) & "'"
    If rs.EOF Then
        rs.AddNew
        !PartNumber = flgPartNum.TextMatrix(i, 0)
    End If

    !GenDesc = flgPartNum.TextMatrix(i, 2)
    !PartDesc = flgPartNum.TextMatrix(i, 3)

    rs.Update

Next
rs.Close

End With

' end code

thanks garths2! Mmmmmwha!

 

[garths2]

LiLgUrL,

Good catch! I didn't even notice you were trying to update the primary key. When I set up an example I didn't use a primary key so I didn't get an error. Glad you found it.

-GS