Comparing Recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables on where tickets are entered in to a log and one where we
import processed tickets. I need to compare these tables weekly and if the
ticket numbers match insert the processed date from the imported table into
the log table so i can the run a report on ticket that have not been
processed. The procedure I have runs with no error messages but it does not
update the table.

Public Sub CheckProcessed()
On Error GoTo Check_Err

Dim cnn As ADODB.Connection
Dim rstLog As ADODB.Recordset
Dim rstProc As ADODB.Recordset

Set cnn = CurrentProject.Connection

Set rstLog = New ADODB.Recordset
Set rstProc = New ADODB.Recordset

rstLog.Open "tblPaperRefunds", cnn, adOpenDynamic, adLockOptimistic
rstProc.Open "tblPPRREfundsProc", cnn, adOpenDynamic, adLockOptimistic

Do While Not rstLog.EOF
Do While Not rstProc.EOF
If rstLog("TicketNumber") = rstProc("TicketNumber") Then
rstLog("DateProcessed") = rstProc("DateProcessed")
rstLog.Update
End If
rstProc.MoveNext
Loop
rstLog.MoveNext
Loop

Set rstLog = Nothing
Set rstProc = Nothing
Set cnn = Nothing

Check_Exit:
Exit Sub

Check_Err:
MsgBox (Err.Description)
Resume Check_Exit

End Sub


I tried replacing the " rstLog("DateProcessed") = rstProc("DateProcessed")
rstLog.Update" lined with "debug.print rstLog("Ticketnumber")" as a test and
i found that it does not return any matching data.

Any help with this would be greatly appreciated.
 
You said that your debug statement indicated "that it does not return
any matching data." Are you saying that the IF statement never made a match
or it made a match and the assignment statement for the "DateProcessed" had
no data to move? I'm unfamiliar with the syntax >rstLog("DateProcessed")<
and am wondering if it is any different from using the >rstLog!DateProcessed<
syntax? Stick a debug line immediately after >Do While Not rstProc.EOF< and
display the ticket number to make sure you're reading something. I noticed
that you didn't start off with a >rstProc.Movefirst< command. I find that if
I don't pre-position the recordset, I get goofy results (or majorly
significant blowouts). Finally, I noticed that you cleared the ADODB
variables (rstProc=nothing) but did not execute a close command
rstProc.Close< beforehand.
Click to expand...

Steve in Ohio
 
It did not match the ticket numbers and I figured out why thanks to you. I
used to debug.print like you advised and found that it was only comparing
against the first record in rstlog. The reason is that once rstproc ran the
first time it didn;t reset so there was nothing to compare to the other
ticket numbers in rstLog to. I had to put an rstProc.MoveFirst in before mys
rstlog.Movenext statement so it would reset before rstLog moved to the next
ticket. I also added the movefirst at the beginning and the close statements
at the end pe your advice and it seems to be running well.

As far as I know rstlog("TicketNumber") is just another way to write
rst!TicketNumber. I tried running it with both syntaxes and it preformed the
same.

I just have one other question since this is running ok. Records will be
added to the log table on a dailty basis and we will be importing the
processed data weekly. So the amount of record will be contantly growing.
Since the processed logs are done on a weekly basis is there way to do say a
paramaterized recordset so that it won't go and compare every record in the
tables each time I run this and will only compare for the week(s) requested?

Thanks again
 
Back
Top