Not changing the values during UPDATE

[chris9]

When I use this code I do not get an error, but it doesn't appear that the MakeName is the tnm table. I really need to get the MakeName somewhere into the tnm table either in it's own column or replace the MakeAbbr if the tnm.MakeAbbr = Makes.MakeAbbr.

Hopefully I've this is clear to understand and I greatly appreciate anyhelp.

Dim strSQL As String
Dim tnm As String


'Get the table name
tnm = [Forms]![Form1]!Text0.Value

strSQL = "UPDATE " & tnm & " AS A INNER JOIN Makes AS B ON A.MakeAbbr=B.MakeAbbr SET A.MakeAbbr = B.MakeName"

DoCmd.RunSQL strSQL

 

[tlbroadbent]

Are you updating a SQL Server or an Access database? The query synatax looks like Jet SQL (Access) syntax. Try the following if you are using SQL Server for the datbase. If you are using Access, then I recommend posting questions in an Access forum.

'Use the text property instead of the value property
tnm = [Forms]![Form1]!Text0.Text

strSQL = "UPDATE A SET MakeAbbr = B.MakeName FROM " & tnm & " AS A INNER JOIN Makes AS B ON A.MakeAbbr = B.MakeAbbr"

DoCmd.RunSQL strSQL If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm