Error in form code

[devesa]

Hello. In the EVENT CODE of a Access Form I get an error on the line in bold. What I do is to compare two tables (Table1 and Table2) checking if for the same Id, fields name and surname are the same. Otherwise it shows a message. Any ideas? THANKS

Table1: Id, name, surname


Table2: Id, name, surname

 
Option Compare Database
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim strSQL1, strSQL2, strSQL3 As String
Dim strMessage As String
Dim rs1, rs2, rs3 As Object
strSQL1 = "Select * From [Table1]"
strSQL2 = "Select * From [Table2]"
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
Set rs2 = CurrentDb.OpenRecordset(strSQL2)
If rs2.EOF And rs2.BOF Then
strMessage = MsgBox("ERROR: No rows to add")
End If
Do While Not rs2.EOF
strSQL3 = "Select * From [Table1] WHERE Id = rs2!Id"
Set rs3 = CurrentDb.OpenRecordset(strSQL3)
If rs3!name <> rs2!name Or rs3!surname <> rs2!surname Then
strMessage = MsgBox("ERROR: Check ID")
Else
strMessage = MsgBox("Correct!!")
End If
rs2.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
Exit_Command0_Click:
    Exit Sub
Err_Command0_Click:
   MsgBox Err.Description
   Resume Exit_Command0_Click
 
End Sub

And also... how could I include an UPDATE SQL operation just after the strMessage = MsgBox("Correct!!") line? Any example?

[pbaldy]

There's no line in bold, but I suspect the error is because of this line, which should be (presuming ID is numeric):

strSQL3 = "Select * From [Table1] WHERE Id = " & rs2!Id

You could run SQL with

CurrentDb.Execute "UPDATE..."

with the same kind of concatenation as above. A FAQ on creating SQL in VBA:

http://www.baldyweb.com/BuildSQL.htm

[devesa]

Thank you, that was exactly the problem and now is solved. It was really kind and helpful.

Just a further question. My UPDATE operation is very very long, and if I do
CurrentDb.Execute "UPDATE............" I get "line too long" error. What can I do?

Thank you once again!

[pbaldy]

You can use line continuation characters (space-underscore) to break up the line:

Dim strSQL As String
strSQL = "INSERT INTO TableName (FieldList) " _
       & "Values (ValueList)"
  
CurrentDb.Execute strSQL, dbFailOnError

Though if you're updating the data in one of the recordsets it might make more sense to do the update there.