Multiple Records from Query

[robben]

Hi,

I have a query where it will return multiple records. What I want to do is loop through all the records received from the query and delete them from the specific table. I know it is a simple question, but I'm not sure about VB. I know how to deal when a query receives just one record but not multiple records.

How would you implement this?

Thanks in advance for your help!

 

[Uncle Gizmo]

I'm not quite sure I understand your question, however if you have a "select query" that has returned a set of records, then you can normally turn this into a "delete query" and it will delete those records.

If you are going to offer this functionality to the database user, then I Normally avoid allowing the database user to delete records, an alternative is to have a boolean field that you mark as true when a record is deleted, then use this field to exclude the records from any datasets that the user can manipulate.

You can now provide the user with an "undelete function" and you can add maintenance routines for supervisors to "purge" the database when necessary of these deleted records.

 

[robben]

Thanks for your quick reply, sorry I didn't explain it too well.

Lets say I have a order table with items for that order for example, if I know the order number is 1, I do a select all items with order number 1 and the query should return all items for that order. I therefore want to be able to delete all items in this order. My question is how can I do this in access/VBA when a query returns multiple records?

Thanks

 

[Uncle Gizmo]

Have you already made this query in the query designer Grid?

 

[robben]

No, I have coded the query manually i.e. i've done the select query but what do I put the results into some array (not sure of VBA I'm afraid) and how do I loop through the results.
Thanks

 

[Uncle Gizmo]

>>>No, I have coded the query manually<<<

I'm not quite sure what you mean by this statement, could you please explain in more detail, and if you have written an SQL statement for your query please post that.

 

[robben]

I.e. finding all items with order 1 (see below -warning just pseudocode):

strSQL = "SELECT Item.Item_ID ” & _
"FROM ITEMs ” & _
"WHERE Order.Order_ID='" & 11 "’ ”;

// call query

// loop through and delete records

Questions what should strSQL be array, or something else in VBA don't know?

Next question how do I loop through the results i.e. item 1, item 5, item 7 and delete them?

Thanks

 

[Uncle Gizmo]

I still can't fathom out your question, I don't understand why you have to post "pseudo code" when you've got the real code? >>>No, I have coded the query manually<<<

So if you would be good enough to post the "real code" and the whole function it is in would be good! Then I may be able to offer you a suggestion, otherwise I feel that I may answer the wrong question, which would be a waste of my time and yours.

 

[robben]

Ok, I'm think I may need to use RecordSet, however if you look at my code how do I extract the Order_ID after calling the following code:

Set rst = db.OpenRecordset(SQL)

Please see the full code below:

Dim db As Database
Dim SQL As String
Dim rs As Recordset
Set db = CurrentDb()

SQL = "SELECT Change_ID FROM Changes_Table WHERE Document_ID = CStr('" & Me.Document_ID & "')"
Set rs = db.OpenRecordset(SQL)

' Delete all changes from Version_Log_Table
Do Until rs.EOF
DoCmd.RunSQL ("Delete FROM Version_Log_Table WHERE Change_ID = ('" & rs!Change_ID & "') ")
rs.MoveNext
Loop