0

I have the following VBA script:

Private Sub CmbTermLookup_AfterUpdate()
Dim BusinessTerm As Integer
Dim SqlString As String
If IsNull(Me!CmbTermLookup) Then
  Me!CmbTermLookup = ""
      Else
    BusinessTerm = Me!CmbTermLookup
End If
SqlString = "SELECT TblBusinessTerm.BusinessTermID,       TblBusinessTerm.BusinessTerm, TblField.FieldID, TblField.FieldName," _
          & " TblField.FieldDescr, TblField.TableID" _
          & " FROM TblBusinessTerm INNER JOIN TblField ON    TblBusinessTerm.BusinessTermID = " & BusinessTerm

Me.RecordSource = SqlString
End Sub

I get the error - JOIN EXPRESSION NOT SUPPORTED.

When I run the sqlstring in a query though it works. Any ideas why?

Thank you

Jext
  • 75
  • 1
  • 8
  • Strange...it looks like it should work. Did you print the exact query string from your VBA code and then try to run that? Also, consider using prepared statements. This would eliminate a lot of uncertainty. – Tim Biegeleisen Oct 27 '17 at 05:42
  • https://stackoverflow.com/questions/6572448/ms-access-prepared-statements – Tim Biegeleisen Oct 27 '17 at 05:42
  • How does the query know which records in `TblField` should be joined to each record in `TblBusinessTerm` if the join expression is something like `... ON TblBusinessTerm.BusinessTermID = 10`? – YowE3K Oct 27 '17 at 05:43
  • I use the variable from the CmbTermLookup which is on a form which I move to the variable BUSINESSTERM in order to pass the ID. There is no where clause. when I run the query in query design it works, so didnt think that it needed a where clause – Jext Oct 27 '17 at 05:49
  • Can you post the string you used in a query that works? I'm really surprised that you can join two tables without specifying the relationship between the tables. (But I am more used to just using normal SQL statements - maybe MS Access queries do some sort of guess as to what fields in the two tables are used to join them.) – YowE3K Oct 27 '17 at 05:55
  • SELECT TblBusinessTerm.BusinessTermID, TblBusinessTerm.BusinessTerm, TblField.FieldID, TblField.FieldName, TblField.FieldDescr, TblField.TableID FROM TblBusinessTerm INNER JOIN TblField ON TblBusinessTerm.BusinessTermID = TblField.BusinessTermID; So all I am trying to do differently here is replace the = TblField.BusinessTermID with a variable – Jext Oct 27 '17 at 05:59
  • maybe you should be joining on `INNER JOIN TblField.FieldID ON` – jsotola Oct 27 '17 at 06:01
  • `ON TblBusinessTerm.BusinessTermID = TblField.BusinessTermID` is **very** different to `ON TblBusinessTerm.BusinessTermID = 10` - the method that works tells it which fields to join on (the field `BusinessTermID` in `TblBusinessTerm` needs to be equal to the field `BusinessTermID` in `TblField` for a join to occur) while the method that doesn't work gives no information about which field in `TblField` to use. – YowE3K Oct 27 '17 at 06:05
  • Can I not replace the = Tblfield.businesstermID with a variable ? – Jext Oct 27 '17 at 06:07
  • No - it needs to know which **fields** to join the tables on. Otherwise it doesn't know whether to join the tables by matching the two `BusinessTermID` fields, or perhaps whether to join using `TblBusinessTerm.BusinessTermID = TblField.TableID` for instance. – YowE3K Oct 27 '17 at 06:10

1 Answers1

0

You need to specify a field in each of the two tables to join the tables on, and then you need to specify a WHERE clause if you only want certain records:

SqlString = "SELECT TblBusinessTerm.BusinessTermID, TblBusinessTerm.BusinessTerm, TblField.FieldID, TblField.FieldName," _
          & " TblField.FieldDescr, TblField.TableID" _
          & " FROM TblBusinessTerm INNER JOIN TblField ON TblBusinessTerm.BusinessTermID = TblField.BusinessTermID " _
          & " WHERE TblBusinessTerm.BusinessTermID = " & BusinessTerm
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Can I ask please to look then at this SQL string, where LIKE has been used: SqlString = "SELECT TblBusinessTerm.BusinessTermID, TblBusinessTerm.BusinessTerm, TblField.FieldID, TblField.FieldName," _ & " TblField.FieldDescr, TblField.TableID" _ & " FROM TblBusinessTerm INNER JOIN TblField ON TblBusinessTerm.BusinessTermID = TblField.BusinessTermID " _ & " WHERE TblBusinessTerm.BusinessTermID like '*" & Me!CmdTitleSimilar & "*'" It doesnt return a record set at all – Jext Oct 27 '17 at 07:51
  • `If IsNull(Me!CmdTitleSimilar) Then Me!CmdTitleSimilar = "" Else BusinessTerm = Me!CmdTitleSimilar End If SqlString = "SELECT TblBusinessTerm.BusinessTermID, TblBusinessTerm.BusinessTerm, TblField.FieldID, TblField.FieldName," _ & " TblField.FieldDescr, TblField.TableID" _ & " FROM TblBusinessTerm INNER JOIN TblField ON TblBusinessTerm.BusinessTermID = TblField.BusinessTermID " _ & " WHERE TblBusinessTerm.BusinessTermID like '*" & Me!CmdTitleSimilar & "*'"' ` – Jext Oct 27 '17 at 08:06
  • 1
    Yes - spot on - thank you - changed the business term ID to Business term, and issue resolved. Thank you for all your help – Jext Oct 27 '17 at 08:12
  • 1
    @Jext It's probably a good idea to delete all the "off-topic" comments we have just been posting - they're nothing to do with the actual question – YowE3K Oct 27 '17 at 08:16