Writing a Module as a Query

[Hanif Merali]

Hello, I'm having some trouble with some syntax, and was wondering if
someone could help me out, or perhaps even offer me some advice as how to
write a query to do this. I have two tables, one is just one column filled
with text fields. I need to check every row of a particular column in
another table to see if that entire field contains the string from the other
table if it does I want to update another column of the same table to a
certain value. This is what I tried to write, I've never used VBA before
and am trying to learn syntax, any help would be greatly appreciated.

Public Function UpdateComRecords()
Dim keywordList(1 To 1376) As String
Dim tempInt(1 To 1376) As Integer

For i% = 1 To 1376
keywordList(i%) = [Commercial Key Words].[Company Keywords]
Next i%
End Sub

For i% = 1 To 1376
tempInt(i%) = InStr([Customers].[LNAME], keywordList(i%))
If tempInt(i%) <> 0 Then
'UPDATE Customers SET Customers.DNP = "1"
End If
Next i%
End Sub
End Function

Perhaps there is a better way to go about this? Thanks in advance.

Regards,

H.M.

 

[Jeff Boyce]

Hanif

I may not understand what you are trying to do, but what you described
sounds like an update query. If you join the two tables on the field in
common, you should be able to update your other column.

A couple notes:
It isn't necessary (and it is confusing) to have the same data in more
than one place.
Any tiny spelling difference will make the compared fields different.

 

[glue]

I would use recordset objects and do...loops to accomplish this kind o
thing.

something like this, you'll have to change the specific table and fiel
names, but i think my logic is correct for what you are trying to do.

dim r as recordset
dim s as recordset
set r=currentdb.openrecordset("TheOneWithOneColumn",dbopensnapshot)
set s=currentdb.openrecordset("TheOtherOneWeEdit",dbopendynaset)
with r
.movefirst
while not .eof
with s
.findfirst "LName LIKE '*" & r!keywords & "*'"
if not .nomatch then
do until .nomatch
.edit
!DNP = 1
.update
.findnext "LName LIKE '*" & r!keywords & "*'"
loop
end if
end with
.movenext
wend
.close
end with
set r=nothing
set s=nothing

hope this helps,
aaro

 

[glue]

yikes lets try that with formatting

Code
-------------------

Sub bub()
Dim r As Recordset
Dim s As Recordset
Set r = CurrentDb.OpenRecordset("TheOneWithOneColumn", dbOpenSnapshot)
Set s = CurrentDb.OpenRecordset("TheOtherOneWeEdit", dbOpenDynaset)
With r
.MoveFirst
While Not .EOF
With s
.FindFirst "LName LIKE '*" & r!keywords & "*'"
If Not .NoMatch Then
Do Until .NoMatch
.Edit
!DNP = 1
.Update
.FindNext "LName LIKE '*" & r!keywords & "*'"
Loop
End If
End With
.MoveNext
Wend
.Close
End With
Set r = Nothing
Set s = Nothing
End Sub

 

[Jeff Boyce]

Aaron

If set-level operations can accomplish the task, they are much faster than
iterating line-by-line through a recordset. Without more information from
the original poster, I can't tell if an update query (set operation) will
satisfy the need.

JOPO (just one person's opinion)

Jeff Boyce
<Access MVP>