Comparing records

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

Guest

I have two tables in my database. One is called NewAppointments and the
other is called newTelesales.

I am trying to read all records in the newAppointments table, and also
simultaneously read all records in the newTelesales table. When a record
from the newTelesales table matches a record from the newAppointments table,
then I want to update a field called notes in the newAppointments table with
the newTelesales Notes field details.

I have written the following code but it keeps going in a loop I presume, as
pc just crashes.

Private Sub Command0_DblClick(Cancel As Integer)
Dim dbNewdb As DAO.Database
Dim rcdTelesales As DAO.Recordset
Dim rcdAppointments As DAO.Recordset
Dim numHold As Integer
Dim strMove As String

Set dbNewdb = CurrentDb
Set rcdTelesales = dbNewdb.OpenRecordset("NewTelesales")
Set rcdAppointments = dbNewdb.OpenRecordset("NewAppointments")

rcdAppointments.MoveFirst
Do Until rcdAppointments.EOF
numHold = rcdAppointments![TeleSalesID]

With rcdTelesales
.MoveFirst

If rcdTelesales.EOF = False And rcdTelesales.BOF = False Then
Do While rcdTelesales.EOF = False
If rcdTelesales![TeleSalesID] = numHold Then
strMove = rcdTelesales![Notes]
.MoveNext
End If
Loop
End If
End With

rcdAppointments.Edit
rcdAppointments![Notes] = strMove
rcdAppointments.Update
rcdAppointments.MoveNext
Loop
End Sub

Any help appreciated

Thanks Ronnie
 
Ronnie said:
I have two tables in my database. One is called NewAppointments and the
other is called newTelesales.

I am trying to read all records in the newAppointments table, and also
simultaneously read all records in the newTelesales table. When a record
from the newTelesales table matches a record from the newAppointments table,
then I want to update a field called notes in the newAppointments table with
the newTelesales Notes field details.

I have written the following code but it keeps going in a loop I presume, as
pc just crashes.

Private Sub Command0_DblClick(Cancel As Integer)
Dim dbNewdb As DAO.Database
Dim rcdTelesales As DAO.Recordset
Dim rcdAppointments As DAO.Recordset
Dim numHold As Integer
Dim strMove As String

Set dbNewdb = CurrentDb
Set rcdTelesales = dbNewdb.OpenRecordset("NewTelesales")
Set rcdAppointments = dbNewdb.OpenRecordset("NewAppointments")

rcdAppointments.MoveFirst
Do Until rcdAppointments.EOF
numHold = rcdAppointments![TeleSalesID]

With rcdTelesales
.MoveFirst

If rcdTelesales.EOF = False And rcdTelesales.BOF = False Then
Do While rcdTelesales.EOF = False
If rcdTelesales![TeleSalesID] = numHold Then
strMove = rcdTelesales![Notes]
.MoveNext
End If
Loop
End If
End With
Click to expand...


You have the MoveNext inside the If in the inner loop and
there is no MoveNext for the outer loop. Either of these
logic errors will cause an infinite loop. Also, you never
close or dereference the objects you used so those
references may tie up resources.

Because of the nested loops this could be an extremely slow
procedure, especially if there a large number of records to
process.

OTOH, since you have a field you can use to identify which
records match up, you could do this with a single query that
Joins the two tables. This way, you would only see records
that you need to work on and the data for both records would
be available. A key to speed in this kind of thing is to
make sure that the key fields in both tables are indexed.
 
Marshall

Thanks for the advice which I followed and it solved the problem. After
you’re comments, a trick I learnt as well was to actually take each line in
the program, and follow it to exactly where it should be in the program, and
the program made more sense to me when I took this approach.

Thanks Ronnie
 
Ronnie said:
Thanks for the advice which I followed and it solved the problem. After
you’re comments, a trick I learnt as well was to actually take each line in
the program, and follow it to exactly where it should be in the program, and
the program made more sense to me when I took this approach.
Click to expand...


Code walkthrough is an excellent practice, especially if you
can get one or two other people to join you while you
explain the purpose of each line.

Good to hear that it's working now.
 
Back
Top