dhookom....SQL in VB throwing error 128 1

[puforee]

Dhookom,

You recently helped (big time) with some SQL in VBA so I could run a table updated from more than one form. It works great.

So, I plagerized the code to work on another table and I am getting Errors on the db.Execute strSQL, dbFailOnError.

[highlight #FCE94F]Public Function UpdateArchiveTbl(lngBEMS_ID As Long, strModel As String)[/highlight]    Dim strSQL As String
    Dim db As DAO.Database
    Set db = CurrentDb
    ' the following assumes BEMS_ID is numeric and Model/AC is text
    strSQL = "UPDATE tbl_Archive SET tbl_Archive.[Last PC] = [tbl_Models_Data]![Last PC]," & _
        "tbl_Archive.[Last IP Eval] = [tbl_Models_Data]![Last IP Eval], " & _
        "tbl_Archive.[Last RT] = [tbl_Models_Data]![Last RT], " & _
        "tbl_Archive.[Last ITC] = [tbl_Models_Data]![Last ITC], " & _
        "tbl_Archive.[Last TCE] = [tbl_Models_Data]!Last TCE], " & _
        "tbl_Archive.[Last LOBS] = [tbl_Models_Data]!Last LOBS], " & _
        "tbl_Archive.[Last QA Eval] = [tbl_Models_Data]!Last QA Eval], " & _
        "tbl_Archive.[Last GS 1st] = [tbl_Models_Data]!Last GS 1st], " & _
       [highlight #FCE94F] "WHERE BEMS_Id= " & lngBEMS_ID & " AND Model= """ & strModel & """ "[/highlight]    db.Execute strSQL, dbFailOnError

I have googled the 128 error and all I can find is that the SQL is tring to reference a Form. I don't see this.
Her is my call:

    Me.Dirty = False
    Call UpdateArchiveTbl(Me.Bems_ID, Me.AC)
    Me.Refresh

The highlighted code is the same we used in the previous SQL. The rest is simular.

Can you see anything obviously wrong...please.

 

[puforee]

OK...copied above into blank Query and ran the query.

This is what I copied:

strSQL = "UPDATE tbl_Archive " & _
    " RIGHT JOIN tbl_Models_Data On [tbl_Archive].[Model]=[tbl_Models_Data].[Model] AND " & _
    "   [tbl_Archive].[BEMS_Id] = [tbl_Models_Data].[BEMS_Id] AND " & _
    "   [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year] " & _
    " SET [tbl_Archive].[Last PC]         = [tbl_Models_Data]![Last PC] , " & _
    "   [tbl_Archive].[Last IP Eval]      = [tbl_Models_Data]![Last IP Eval], " & _
    "   [tbl_Archive].[Model]             = [tbl_Models_Data].[Model], " & _
    "   [tbl_Archive].[BEMS_Id]           = [tbl_Models_Data].[BEMS_Id], " & _
    "   [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year] " & _
    " WHERE BEMS_Id = " & lngBEMS_ID & _
    "   AND Model = """ & strModel & """"
    Debug.Print strSQL

When I ran it I got:

Invalid SQL statement; Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I am curious...since I am joining the tables on 3 fields...should I pass all three fields to the code when I do the "CALL"? Right now I am only passing two. I am not passing the training_year.

And, to answer your questins:
The archive table keeps the history of an employee's trainiing over the years. This can be used for reporting and the employee can take it with him/her when the leave the organization to go to another organization that requires this training.

The Archive training_year, BEMS_Id, and Model are change to the next year through different code. So, when 2015 arrives the Archive table will be updated for all employees to show these three items. As each training event is completed their respective records in the Archive table will be updated with the completion date for that training.

The employee being active or not active is controled through another method and if inactive will not have a new training_year record in the Archive table. We will still have old history on the employee but recording stops when they become in active.

Hope that helps give you the reason.

 

[dhookom]

You don't paste the code into the SQL view, you paste the results of the Debug.Print. Open the debug/immediate window (press Ctrl+G to view) and you should see the complete statement.

If every piece of information in the archive table is also in the data table then there I see no reason to maintain an archive table. If information is removed from or changed in the data table than I can possibly see a need for an archive table. Either that or change the data table so that information is added rather than changed.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK..I will do it. Only the current year is in the tbl_models_data.

 

[puforee]

Duane. I finally got what you wanted me to do. I copied the code from the imediate screen and pasted into a query in SQL view.

UPDATE tbl_Archive  RIGHT JOIN tbl_Models_Data On [tbl_Archive].[Model]=[tbl_Models_Data].[Model] AND    [tbl_Archive].[BEMS_Id] = [tbl_Models_Data].[BEMS_Id] AND    [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year]  SET [tbl_Archive].[Last PC]         = [tbl_Models_Data]![Last PC] ,    [tbl_Archive].[Last IP Eval]      = [tbl_Models_Data]![Last IP Eval],    [tbl_Archive].[Model]             = [tbl_Models_Data].[Model],    [tbl_Archive].[BEMS_Id]           = [tbl_Models_Data].[BEMS_Id],    [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year]  WHERE BEMS_Id = 1794035   AND Model = "A320"

This returned: "The specified field 'BEMS_Id' could refer to more than one table listed in the From clause of your SQL statement".

And, since I do not see a FROM clause in the code above...does this mean I have to insert one? Please, if I do..where and referencing which table...the tbl_Models_Data?

And I do want to thank you....I am learning...even though it might seem slow.

 

[dhookom]

You don't need the FROM clause for this with Access SQL. You might only need to fix the error to fully qualify the two fields in the WHERE clause with their table name. It might also help to use [highlight #FCE94F].[/highlight] rather than [highlight #FCE94F]![/highlight]

I also added [highlight #FCE94F]& vbCrLf[/highlight] so the results of the debug.print is formatted better and doesn't cause a very long line in your postings.

strSQL = "UPDATE tbl_Archive " & _
    " RIGHT JOIN tbl_Models_Data On [tbl_Archive].[Model]=[tbl_Models_Data].[Model] AND " & _
    "   [tbl_Archive].[BEMS_Id] = [tbl_Models_Data].[BEMS_Id] AND " & _
    "   [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year] " & vbCrLf &_
    " SET [tbl_Archive].[Last PC]         = [tbl_Models_Data].[Last PC] , " & _
    "   [tbl_Archive].[Last IP Eval]      = [tbl_Models_Data].[Last IP Eval], " & _
    "   [tbl_Archive].[Model]             = [tbl_Models_Data].[Model], " & vbCrLf & _
    "   [tbl_Archive].[BEMS_Id]           = [tbl_Models_Data].[BEMS_Id], " & _
    "   [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year] " & vbCrLf & _
    " WHERE [tbl_Models_Data].BEMS_Id = " & lngBEMS_ID & _
    "   AND [tbl_Models_Data].Model = """ & strModel & """"
Debug.Print strSQL
[COLOR=#4E9A06]' copy from the debug window and paste into the SQL view of a blank query.[/color]

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Duane,

I pasted in your code to the module and everything turned red.

So, I put the previous version back in and just changed the WHERE statement.

I left out the vbCrLf statements.

I ran this code and it worked WOW, you did it.

Now I can start building the rest of the SQL for the other values.

Fantastic job...hope my slowness was not too painful for you.

 

[dhookom]

puforee,
You need to be able to identify and fix simple mistakes like missing a space to the left of the underscore at the end of this line:

    "   [tbl_Models_Data].[Training_Year] = [tbl_Archive].[Training_Year] " & vbCrLf &_

Have you determined if new year records will be automatically appended to the archive table?

Duane
Hook'D on Access
MS Access MVP