Creating relational links between tables

[NancyLoc]

I am trying to re-establish links between two tables.
However, the code underneath seems to cause problems.
Does someone have an idea what would be the problem ?

Thanks

Nancy
---------------------

Private Sub Command1_Click()
Dim dbs As Database
Dim rstspain As Recordset
Dim rstcity As Recordset
Set dbs = CurrentDb()
Set rstspain = dbs.OpenRecordset("Spain", dbOpenDynaset)


Set rstcity = dbs.OpenRecordset("City", dbOpenDynaset)
rstspain.MoveFirst
Do While Not rstspain.EOF
rstcity.MoveFirst
rstcity.FindFirst cityname = rstspain![cityname] ( tried also " [cityname] = " & rstspain![cityname]" (with same result))
If rstcity.NoMatch Then
MsgBox "City not found"
Else
rstspain.Edit
rstspain!city = rstcity!cityID
rstspain.Update
End If

rstspain.MoveNext
Loop
MsgBox "Einde"

End Sub

[ssanfu]

If "rstspain![cityname]" is a text type field, you need to use delimiters. Try:

Private Sub Command1_Click()
   Dim dbs As Database
   Dim rstspain As Recordset
   Dim rstcity As Recordset

   Set dbs = CurrentDb()
   Set rstspain = dbs.OpenRecordset("Spain", dbOpenDynaset)
   Set rstcity = dbs.OpenRecordset("City", dbOpenDynaset)

   rstspain.MoveFirst

   Do While Not rstspain.EOF
      rstcity.MoveFirst
      rstcity.FindFirst "[cityname] = '" & rstspain![cityname] & "'"
      If rstcity.NoMatch Then
         MsgBox "City " & rstspain![cityname] & " not found"
      Else
         rstspain.Edit
         rstspain!city = rstcity!cityID
         rstspain.Update
      End If

      rstspain.MoveNext
   Loop
   MsgBox "Einde"

End Sub

Exploded, the line looks like:

rstcity.FindFirst "[cityname] = ' " & rstspain![cityname] & " ' "

(but don't use with the spaces)

[NancyLoc]

Many thanks for this !
I should have known ... need a refreshment course ggrggr ...
Nancy