Loop through data entry subform

[GAMB1T]

Hey so my grand idea is to have a subform where the user can past in multiple account numbers and then the code will remove rows with this account number from the table.

I am having trouble setting the AccountsSubform recordset equal to what is entered into the form. This is what I have currently:

**all other declarations
Dim AccountsSubform As RecordSet

Me.SubscriptionRemovesSubform.Form.Refresh
Set AccountsSubform = Me.SubscriptionRemovesSubform.Form.RecordsetClone

With AccountsSubform
Do While Not AccountsSubform.EOF

userID = [Forms]![MainForm]![txtUserID]
AccountNumber = Me.Account_Number

problemNumber = [Forms]![RemovesForm]![problemNumber]
If (problemNumber = "") Then
problemNumber = "N/A"
End If


Set AccountsTable = CurrentDb.OpenRecordset("Accounts")

Do Until AccountsTable.EOF
If (AccountNumber = AccountsTable![AccountNumber]) Then
If (userID = AccountsTable![userID]) Then
GoTo Continue ***SQL CODE TO DELETE
End If
End If
AccountsTable.MoveNext
Loop

AccountsSubform.MoveNext

Loop
End With

Any help with this would be awesome!

 

[MajP]

Normally this is done with a query not a loop. Maybe something like:
"delete accountsTable.* from accountsTable where UserID = Forms!MainForm.UserID and accountNumber in (select problemNumber from yourSubFormTable)

 

[GAMB1T]

OK sweet. So I wrote a basic Select query to retrieve the accounts entered in the subform, but only the one that is selected(has the arrow pointing to it) shows up in my results. Am I missing something?

SELECT *
FROM accountsTable
WHERE accountsTable.UserID=Forms!MainForm!UserID And accountsTable.AccountNumber=Forms!RemovesForm!RemovesSubform!Account_Number;


I was also thinking of using a make table query to read the subform entries into and then joining it with the table I want to delete accounts from...

 

[MajP]

Can you provide the sql for the subform query? Also the field that links the subform to the main form? I think you are referencing the wrong fields in your where statement.