VB Critique

[Tom]

I am really quite a novice at VB and would like to know if the VB code I
wrote is the most efficient (fastest) way delete multiple records from
within a table.

The background is I have two unassociated tables, when a person highlights
the lines in the Left Table, I run the VB code to delete records in the
Right Table

Form with highlighted lines as delete Table with data needing
to be deleted(orderLines)
----------------------------------- ---------------------------------------
PartNumber PartNumber
Product Product
Etc.. Etc...
_____________________________________________
Option Compare Database
Option Explicit
_____________________________________________
Private Sub Form_Delete(Cancel As Integer)

Dim txtPN As String
Dim txtProduct As String
Dim mydb As Database
Dim mytbl As Recordset

txtPN = PartNumber
txtProduct = Product
Set mydb = CurrentDb()
Set mytbl = mydb.OpenRecordset("orderlines", DB_OPEN_TABLE)

mytbl.MoveFirst
Do Until mytbl.EOF

If mytbl![PN] = txtPN And mytbl![Product] = txtProduct Then
mytbl.Delete
End If
mytbl.MoveNext
Loop

mytbl.Close

End Sub

Thanks
Tom

 

[Dirk Goldgar]

I am really quite a novice at VB and would like to know if the VB code I
wrote is the most efficient (fastest) way delete multiple records from
within a table.

The background is I have two unassociated tables, when a person highlights
the lines in the Left Table, I run the VB code to delete records in the
Right Table

Form with highlighted lines as delete Table with data
needing to be deleted(orderLines)
----------------------------------- ---------------------------------------
PartNumber PartNumber
Product Product
Etc.. Etc...
_____________________________________________
Option Compare Database
Option Explicit
_____________________________________________
Private Sub Form_Delete(Cancel As Integer)

Dim txtPN As String
Dim txtProduct As String
Dim mydb As Database
Dim mytbl As Recordset

txtPN = PartNumber
txtProduct = Product
Set mydb = CurrentDb()
Set mytbl = mydb.OpenRecordset("orderlines", DB_OPEN_TABLE)

mytbl.MoveFirst
Do Until mytbl.EOF

If mytbl![PN] = txtPN And mytbl![Product] = txtProduct Then
mytbl.Delete
End If
mytbl.MoveNext
Loop

mytbl.Close

End Sub

Thanks

Tom, I posted a better solution to your original thread this morning.

 

[Software-Matters via AccessMonster.com]

Hi,
A quicker way to do this would be to write a delete query and then call it in
code whenever it's needed.

Regards
JD

--
<a href="
http://www.software-matters.co.uk/bespoke-database-design.html">Bespoke
Access Database Development</a>
<p>Software Matters</br>
Straightforward solutions that work</p>