RE: Comparing Recordsets

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Stephen Knapp (StephenKnapp_at_discussions.microsoft.com)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 09:53:07 -0800

     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.

Steve in Ohio

"SZito" wrote:

> 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.
>

Quantcast