Access VBA Search Loop

Question

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.

For example:

table "Main"
Field "Mail"
[email protected]
[email protected]
[email protected]
[email protected]

table "Sparr"
Field "sparrord"
hotmail
jonny

table "Testtable"
Field "testMail"
[email protected]
[email protected]

So if I run this VBA code I want [email protected] and [email protected] 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

BIBD BIBD · Accepted Answer · 2015-07-10T19:25:06

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.

Access VBA Search Loop

2 Answers