acces
07-10-2015, 11:20 AM
I am currently making an search loop in VBA for my database. In this database i Have 2 Tabels, one with the customers records called "customersT" and another one with search words called "swordsT". The idea is to use a filter to filter out the customers that match any of the serachwords in the "swordsT" table. Any customer that does not match any of the search words gets added to another table called "filteredCustomerT".
For example:
table "customersT"
Field "mailFi"
mikeatcoolmail
johnathotmail
daveatmail
jonnyatmailx
table "swordsT"
Field "wordFi"
hotmail
jonny
table "filteredCustomerT"
Field "filteredmailFi"
mikeatcoolmail
daveatmail
So if i run this VBA code i want johnathotmail and jonnyatmailx to be filtered out. The "customerT" table contains 200k records and the "swordsT" table contains 2k search words. I have made a VBA code that should loop through the "customerT" table. For every record in the "customerT" table a have another nested loop that loops through the "swordsT" table so see if there is any match. If there is not a match the VBA code copies the entry to anther table called "filteredCustomerT". 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 customerMail As Recordset
Dim SearchwordWord As Recordset
Dim mailFilteredCustomerT As Recordset
Dim customerTemp As String
Dim srcwTemp As String
Set customerMail = CurrentDb.OpenRecordset("customerT")
Set SearchwordWord = CurrentDb.OpenRecordset("swordsT")
Set mailFilteredCustomerT = CurrentDb.OpenRecordset(""filteredCustomerT"")
Do Until customerMail.EOF
Do Until SearchwordWord.EOF
customerTemp = customerMail![mailFi]
srcwTemp = SearchwordWord![wordFi]
If (InStr(customerTemp, srcwTemp) = 0) Then
mailFilteredCustomerT.AddNew
mailFilteredCustomerT![filteredmailFi] = mainTemp
mailFilteredCustomerT.Update
End If
SearchwordWord.MoveNext
Loop
customerMail.MoveNext
Loop
End Sub
The result i get is that just the first mail in the "customerT" table gets copied in 2000 different entries in the "filteredCustomerT" table. I would be very grateful if someone could spot some faults in the code that may cause it not to work.
Have a nice day!
BR
acces
For example:
table "customersT"
Field "mailFi"
mikeatcoolmail
johnathotmail
daveatmail
jonnyatmailx
table "swordsT"
Field "wordFi"
hotmail
jonny
table "filteredCustomerT"
Field "filteredmailFi"
mikeatcoolmail
daveatmail
So if i run this VBA code i want johnathotmail and jonnyatmailx to be filtered out. The "customerT" table contains 200k records and the "swordsT" table contains 2k search words. I have made a VBA code that should loop through the "customerT" table. For every record in the "customerT" table a have another nested loop that loops through the "swordsT" table so see if there is any match. If there is not a match the VBA code copies the entry to anther table called "filteredCustomerT". 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 customerMail As Recordset
Dim SearchwordWord As Recordset
Dim mailFilteredCustomerT As Recordset
Dim customerTemp As String
Dim srcwTemp As String
Set customerMail = CurrentDb.OpenRecordset("customerT")
Set SearchwordWord = CurrentDb.OpenRecordset("swordsT")
Set mailFilteredCustomerT = CurrentDb.OpenRecordset(""filteredCustomerT"")
Do Until customerMail.EOF
Do Until SearchwordWord.EOF
customerTemp = customerMail![mailFi]
srcwTemp = SearchwordWord![wordFi]
If (InStr(customerTemp, srcwTemp) = 0) Then
mailFilteredCustomerT.AddNew
mailFilteredCustomerT![filteredmailFi] = mainTemp
mailFilteredCustomerT.Update
End If
SearchwordWord.MoveNext
Loop
customerMail.MoveNext
Loop
End Sub
The result i get is that just the first mail in the "customerT" table gets copied in 2000 different entries in the "filteredCustomerT" table. I would be very grateful if someone could spot some faults in the code that may cause it not to work.
Have a nice day!
BR
acces