Access VBA Search Loop
I am currently making an search loop in VBA for my database. In this database I Have 2 Tables, one with the customers records called Main and another one with search words called Sparr. The idea is to use a filter to filter out the customers that match any of the search words in the Sparr table. Any customer that does not match any of the search words gets added to another table called filteredCustomerT.
table "Main" Field "Mail" email@example.com firstname.lastname@example.org email@example.com firstname.lastname@example.org table "Sparr" Field "sparrord" hotmail jonny table "Testtable" Field "testMail" email@example.com firstname.lastname@example.org
So if I run this VBA code I want email@example.com and firstname.lastname@example.org to be filtered out. The Main table contains 200k records and the Sparr table contains 2k search words. I have wrote some VBA code that should loop through the Main table. For every record in the Main table a have another nested loop that loops through the Sparr table so see if there is any match. If there is not a match the VBA code copies the entry to anther table called Testtable. I Use the inStr function to do the matching.
Below I have posted the VBA code that does not seem to work. Can anyone help me ant maybe point out a fault in the code. I am very new to VBA programming.
Option Compare Database Option Explicit Sub filter() Dim mainMail As Recordset Dim sparrSokord As Recordset Dim testtableTestmail As Recordset Dim mainTemp As String Dim sparrTemp As String Dim match As Integer Set mainMail = CurrentDb.OpenRecordset("Main") Set sparrSokord = CurrentDb.OpenRecordset("Sparr") Set testtableTestmail = CurrentDb.OpenRecordset("Testtable") Do Until mainMail.EOF mainTemp = mainMail![Mail] match = 0 sparrSokord.MoveFirst Do Until sparrSokord.EOF sparrTemp = sparrSokord![sparrord] If (InStr(mainTemp, sparrTemp) <> 0) Then match = 1 Exit Do End If sparrSokord.MoveNext Loop If (match = 0) Then testtableTestmail.AddNew testtableTestmail![testMail] = mainTemp testtableTestmail.Update End If mainMail.MoveNext Loop End Sub
InStr can operate in unexpected ways is you have nulls/empty strings/etc, involved.
I've noticed that you have nothing resetting the position of SearchwordWord back to the beginning of the record set once you reach the end.
Naturally you would do something like SearchwordWord.MoveFirst before the Do Until SearchwordWord.EOF. It doesn't hurt to do one before the Do Until customerMail.EOF either
I'll also note the Do Until always executes the contents of the loop, and then checks the condition at the end (which could be giving you unexpected results, especially with SearchwordWord being at EOF after the first successful pass of the loop.
You probably want to use a while/wend instead for both do untils (I practically never use them as it is). This is probably the biggest cause of your grief.
The problem is now solved. I just had to close some programs and try again with the updated code above. Worked just fine!