Search for two fields match in two recordsets??

[Jen]

Hi All,
I have two recordsets behind a button, one(rsttbl) is for
a table: tblData, which has SubscriberID,ReStateDate, and
FinishedFlag. another(rst) is for a query,which has
SubscriberID, ReStateDate. What I wanted to do is to
search both SubscriberID and ReStateDate in the tblData
table, if there is a match, then update the tblData table
by flipping the FinishedFlag = Yes. My sample data is
like the following in the table, which includes
duplicated SubscriberIDs, but with different RestateDates:
SubscriberID RestateDate FinishedFlag
S00003128 3/23/2004 No
S00007513 5/6/2004 Yes
S00014601 2/3/2004 No
S00014601 5/6/2004 No
S00014601 4/3/2004 No
S77777777 5/6/2004 No
S88888888 5/6/2004 No
But right now I have the following codes, they are not
working correctly, Can anyone help me to take a look at
the codes below and give me some suggestions? Thank you
very much for any help! Jen

Set rsttbl = db.OpenRecordset("tblData",
dbOpenDynaset) 'for tblData
Set rst = db.OpenRecordset("qryExtractData",
dbOpenDynaset) 'for qryExtractData
If rst.EOF And rst.BOF Then
MsgBox "You do not have any data in the
table. " , vbOKOnly, "ABC"
Else
rst.MoveLast
intCount2 = rst.RecordCount
rst.MoveFirst
strSearch = "[SubscriberID]= " & QUOTE & rst!
SubscriberID & QUOTE

rsttbl.MoveFirst
rsttbl.FindFirst strSearch

For intCount1 = 1 To intCount2
If Not rsttbl.NoMatch And intCount1 = 1 Then
strSearch2 = "[RestateDate]= # " & rst!
RestateDate & "#"
rsttbl.FindFirst strSearch2

If Not rsttbl.NoMatch Then
rsttbl.Edit
rsttbl!Completed = True
rsttbl.Update
End If
Else
rsttbl.MoveFirst
rst.MoveNext
strSearch = "[SubscriberID]= " & QUOTE &
rst!SubscriberID & QUOTE
rsttbl.FindNext strSearch
If Not rsttbl.NoMatch And intCount1 > 1 Then
strSearch2 = "[RestateDate]= # " & rst!
RestateDate & "#"
rsttbl.FindNext strSearch2
If Not rsttbl.NoMatch Then
rsttbl.Edit
rsttbl!Completed = True
rsttbl.Update
End If
End If
End If
Next intCount1

End If

 

[Guest]

Jen,

I looked at you code and took the liberty of rewriting it; I think I understand what you are wanting to do. I did have a question - in the description of tblData you have a field named 'FinishedFlag' but in the code you are using !Completed = True....... I used !FinishedFlag = True.

I did the search for both the SubscriberID and the date at the same time, using a Do While loop to move thru the rst recordset. I hope I put enough comments. If not, repost or email me.

I tried to use your names - but is I missed any, you should be able to fix them.

Any lines with '*** at the end are lines I added that won't hurt anything if deleted.
Here is the code - watch for line wrap:

'----- snip ----
Dim db As Database
Dim rst As Recordset
Dim rsttbl As Recordset
Dim strSearch As String
Dim RecModified As Long '***
Dim RecTested As Long '***
Dim msg As String '***

'initalize
Set db = CurrentDb
RecModified = 0 '***
RecTested = 0 '***

'open recordsets
Set rsttbl = db.OpenRecordset("tbldata", dbOpenDynaset)
Set rst = db.OpenRecordset("qryExtractData", dbOpenDynaset)


If rst.BOF And rst.EOF Then
MsgBox "You do not have any data in the table. ", vbOKOnly, "ABC"
Else

rst.MoveFirst
'loop thru rst until reach End_Of_Flie
Do While Not rst.EOF
RecTested = RecTested + 1 ' ***

'create search string
strSearch = "[SubscriberID] = '" & rst!subscriberID & "'"
strSearch = strSearch & " AND [ReStateDate] = #" & rst!ReStateDate & "#"

rsttbl.FindFirst strSearch
With rsttbl
'if found and not finished - update record
If Not .NoMatch And Not !Finishedflag Then
.Edit
!Finishedflag = True
.Update
'inc modified counter
RecModified = RecModified + 1 '***
End If
End With
rst.MoveNext
Loop
End If

msg = "Done. There were " & RecModified '***
msg = msg & " records changed to Completed in tblData out of " '***
msg = msg & RecTested & " records read from the query" '***
MsgBox msg '***
'--- snip ----


HTH

Steve

 

[jen]

Hi Steve,
Thank you very much for your help!!!! Thank you so much
for your time and patience to look at my code and make
changes!! Yes, this is exactly what I would like to do.
Thank you, thank you,
jen

-----Original Message-----
Jen,

I looked at you code and took the liberty of rewriting

it; I think I understand what you are wanting to do. I
did have a question - in the description of tblData you
have a field named 'FinishedFlag' but in the code you are
using !Completed = True....... I used !FinishedFlag =
True.

I did the search for both the SubscriberID and the date

at the same time, using a Do While loop to move thru the
rst recordset. I hope I put enough comments. If not,
repost or email me.

I tried to use your names - but is I missed any, you should be able to fix them.

Any lines with '*** at the end are lines I added that

won't hurt anything if deleted.

Here is the code - watch for line wrap:

'----- snip ----
Dim db As Database
Dim rst As Recordset
Dim rsttbl As Recordset
Dim strSearch As String
Dim RecModified As Long '***
Dim RecTested As Long '***
Dim msg As String '***

'initalize
Set db = CurrentDb
RecModified = 0 '***
RecTested = 0 '***

'open recordsets
Set rsttbl = db.OpenRecordset("tbldata", dbOpenDynaset)
Set rst = db.OpenRecordset("qryExtractData", dbOpenDynaset)


If rst.BOF And rst.EOF Then
MsgBox "You do not have any data in the table. ", vbOKOnly, "ABC"
Else

rst.MoveFirst
'loop thru rst until reach End_Of_Flie
Do While Not rst.EOF
RecTested = RecTested + 1 ' ***

'create search string
strSearch = "[SubscriberID] = '" & rst! subscriberID & "'"
strSearch = strSearch & " AND [ReStateDate] = #" & rst!ReStateDate & "#"

rsttbl.FindFirst strSearch
With rsttbl
'if found and not finished - update record
If Not .NoMatch And Not !Finishedflag Then
.Edit
!Finishedflag = True
.Update
'inc modified counter
RecModified = RecModified + 1 '***
End If
End With
rst.MoveNext
Loop
End If

msg = "Done. There were " & RecModified '***
msg = msg & " records changed to Completed in tblData out of " '***
msg = msg & RecTested & " records read from the query" '***
MsgBox msg
'***
'--- snip ----


HTH

Steve