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.

For example:

table "Main"
Field "Mail"
mike@coolmail.com
john@hotmail.com
dave@mail.com
jonny@mailx.com

table "Sparr"
Field "sparrord"
hotmail
jonny

table "Testtable"
Field "testMail"
mike@coolmail.com
dave@mail.com

So if I run this VBA code I want john@hotmail.com and jonny@mailx.com 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

Answers


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!


Need Your Help

Opening a live media stream using C/C++

c++ c windows streaming media

Can anyone tell me how to open a mms:// live media stream using C/C++. I am trying to do this for days. but can not find any book regarding these information.

Appending multiple parameters/arguments to a jsonp callback function

javascript youtube jsonp

How do I specify more arguments to be passed to a jsonp callback function?