Database control 1

[zailie]

I have a data table in ms access and I would like to store the first value in column PartNo and the proceed to search column NoSale to see if the same number is in that column. The matching numbers will not be in the same row. After a match is found I would like to then delete both rows in which the number occurs. After that is complete I would like to move to the next number in column PartNo and repeat the process. If any one can help it would greatly be appreciated. Here is what I have so far. Please advise!

Dim cnConn As New ADODB.Connection
Set cnConn = New ADODB.Connection

With cnConn
.Provider = "microsoft.jet.oledb.4.0; " & _
"persist security info=false; "
.ConnectionString = "data source= " & _
App.Path & "\diesel1.mdb"
.Open
End With

Dim rsSel As ADODB.Recordset
Set rsSel = New ADODB.Recordset

With rsSel
.ActiveConnection = cnConn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Source = "select * from FIP WHERE "
.Open "FIP", cnConn, adOpenKeyset, adLockOptimistic, adCmdTable



Dim StrTemp
Dim strPart
rsSel.MoveFirst


Do While Not rsSel.EOF
strPart = !PartNo
Do While Not rsSel.EOF
If !NoSale = strPart Then
rsSel.Delete (adAffectGroup)
End If
rsSel.MoveNext
Loop
rsSel

Loop

End With
End Sub

 

[ITdev]

Are you testing if column PartNo has the same value as in NoSale for the same row? If you are then try this on your loop.

Do While Not rsSel.EOF
strPart = !PartNo
strTemp = !NoSale
if strTemp = strPart then
rsSel.Delete (adAffectGroup)
End If
rsSel.MoveNext
Loop

Or do you mean, for each PartNo you want to know if there is a match anywhere in Nosale. If there is a match, you want to delete both row? IF this is a case, you'll need another recordset. I changed it slightly.

Dim cnConn As New ADODB.Connection
Set cnConn = New ADODB.Connection

With cnConn
.Provider = "microsoft.jet.oledb.4.0; " & _
"persist security info=false; "
.ConnectionString = "data source= " & _
App.Path & "\diesel1.mdb"
.Open
End With

Dim rsPart As ADODB.Recordset
Set rsPart = New ADODB.Recordset
Dim rsSale As ADODB.Recordset
Set rsSale = New ADODB.Recordset

With rsPart
.ActiveConnection = cnConn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Source = "select * from FIP "
.Open "FIP", cnConn, adOpenKeyset, adLockOptimistic, adCmdTable
end with

With rsSale
.ActiveConnection = cnConn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Source = "select * from FIP "
.Open "FIP", cnConn, adOpenKeyset, adLockOptimistic, adCmdTable
End With

Dim strPart


Do While Not rsPart.EOF
strPart = rsPart!PartNo
rsSale.MoveFirst
Do While Not rsSale.EOF
If rsSale!NoSale = strPart Then
'delete both rows
rsSale.Delete (adAffectGroup)
rsPart.Delete
rsSale.MoveNext
Exit Loop
End If
rsSale.MoveNext
Loop
rsPart.MoveNext

Loop


End Sub



Hope this helps.

 

[zailie]

Hi I tried the code and I keep getting and error message that. "Row cannot be located for updatind. Some values may have been changed since it was last read."
I'm not sure exactly what this means. Anyway the code does not delete both rows that contain the matching record.