comparing 2 values from 2 tables

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I am trying to compare 2 values from 2 tables.
If the values differs then a message should be displayed.
below is the routine that I created:

Public Sub comparelines()

Dim rst As Recordset
Dim rst1 As Recordset
Dim dbs As Database
Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("SELECT * FROM tblinvoices
ORDER BY telnr", dbOpenDynaset)
Set rst1 = dbs.OpenRecordset("SELECT * FROM
tbltelephonelines ORDER BY telnr", dbOpenDynaset)

Do
While Not rst1.EOF
If rst!telnr = rst1!telnr Then
rst.MoveNext
rst1.MoveNext
Wend
ElseIf rst!telnr <> rst1!telnr Then
MsgBox (rst!telnr & " " & "cannot be found in the
database")
rst.MoveNext

Loop Until rst.EOF
End if

rst.Close
rst1.Close
db.Close

Set rst1 = Nothing
Set rst = Nothing
Set db = Nothing
End Sub

When compile the routine I get the following error
message:
compile error wend without while.
any help is most appreciated.
 
Let me indent your code for you:
Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("SELECT * FROM tblinvoices ORDER BY telnr", dbOpenDynaset)
Set rst1 = dbs.OpenRecordset("SELECT * FROM tbltelephonelines ORDER BY telnr", dbOpenDynaset)

Do
While Not rst1.EOF
If rst!telnr = rst1!telnr Then
rst.MoveNext
rst1.MoveNext
Wend
ElseIf rst!telnr <> rst1!telnr Then
MsgBox (rst!telnr & " " & "cannot be found in the database")
rst.MoveNext ?!
Loop Until rst.EOF
End if
Click to expand...

You cannot nest tests/loops like this. Draw the structure of the code
for yourself in a diagram or so, and code again.
 
Back
Top