Query from multiple tables-Error

[kvanch]

Hi!

I have a question about SQL query return data from multiple tables..

Private Sub Command1_Click()
Dim psql
 cn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\Dictionary.mdb"
 cn.Open
  rs.Open "Select Spanish.*, English.* from Spanish, English", cn, adOpenDynamic, adLockOptimistic
If Right$(Text2.Text, 2) = "ed" Then
Text2.Text = Left$(Text2.Text, Len(Text2.Text) - 2)
     psql = "SELECT Spanish.Verbpast FROM English INNER JOIN Spanish ON English.Spsh_ID = Spanish.Spsh_ID WHERE English.Verbpresent= '" & Text2.Text & "'"
 rs.MoveFirst
    rs.Requery psql
If Not rs.EOF Then
    Text3.Text = rs(“Verbpast”)
End If
End If
End Sub

The two tables are linked by the SpshId. . I want to get the past tense of a verb in Text3 if I type the past form of a verb. But I get ‘Type mismatch’ error.

Can anybody help me on it?

[dglienna]

Use SQL MGMT STUDIO to debug query's. It stops at each word that faults, as apposed to the wrong line...

[DataMiser]

Which line gives the error?

There is no reason to select all records from the tables and then requery. You should get rid of the first query and use open on the other one.

As for the type mismatch, that indicates that one of your variable types is incorrect. Is the ID in both tables the same type?

Also note that while ed is common on past tense verbs there are also many that do not end in ed, such as ran, came, went, flew, drove and so on