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.

 

[dhookom]

I would use debug.print in the code so you can trouble-shoot. You will find that tbl_Models_Data is not in any "from" clause so it can't be resolved. You could grab the values from tbl_Models_Data first and then use the values "outside" the quotes. What are the primary key fields on the tables?

Public Function UpdateArchiveTbl(lngBEMS_ID As Long, strModel As String)
    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], " & _
        "WHERE BEMS_Id= " & lngBEMS_ID & " AND Model= """ & strModel & """ "
    [COLOR=#CC0000][b]debug.Print strSQL[/b][/color]
    db.Execute strSQL, dbFailOnError 
End Function

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK,

Here is the Immediate - Is this where Debug.print goes to?

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], WHERE BEMS_Id= 1794035 AND Model= "A320"

I did find some brackets that were missing and corrected those. Now I am getting syntax errors with 3144.

Currently not using a primary key in either table.

A persons BEMS (Unique personal ID) can be repeated in both tables because they can have more than one model. That is why I don't use the PK.

 

[puforee]

If it helps, here is the query that I am trying to get into VBA Squ.

UPDATE tbl_Archive SET tbl_Archive.[Last PC] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last PC], tbl_Archive.[Last IP Eval] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last IP Eval], tbl_Archive.[Last RT] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last RT], tbl_Archive.[Last ITC] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last ITC], tbl_Archive.[Last TCE] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last TCE], tbl_Archive.[Last LOBS] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last LOBS], tbl_Archive.[Last QA Eval] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last QA Eval], tbl_Archive.[Last GS 1st] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last GS], tbl_Archive.[Last GS 2nd] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last GS 2nd], tbl_Archive.[Last MOLITSIM] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last MOLIT], tbl_Archive.[Last MOLITAural] = [Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Last MOLITAural], tbl_Archive.Boeing_Compliance_Tng = [Forms]![Instructor Training Setup]![Boeing_Compliance_Tng], tbl_Archive.BCT_Completion_Date = [Forms]![Instructor Training Setup]![BCT_Completion_Date]
WHERE (((tbl_Archive.Year)=[Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![Training_Year]) AND ((tbl_Archive.BEMS_Id)=[Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![BEMS_Id]) AND ((tbl_Archive.Model)=[Forms]![Instructor Training Setup]![Training_Dates subform - Vertical].[Form]![AC]));

 

[Andrzejek]

Try something like this:

    strSQL = "UPDATE tbl_Archive SET " _
        " [Last PC] = [blue]'" & [/blue][tbl_Models_Data]![Last PC][blue] & "'[/blue], " & _
        " [Last IP Eval] = [blue]'" & [/blue][tbl_Models_Data]![Last IP Eval][blue] & "', [/blue]" & _

You will not need ' for Numbers, but you will need # for Dates.
And if you ever can get a String with ' in it, replace single ' with 2 single quotes.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

You can't run an update query like this without:
[ul]
[li]removing the comma to the left of WHERE[/li]
[li]adding primary keys[/li]
[li]adding [tbl_Models_Data] into a FROM statement[/li]
[/ul]

Are you suggesting "the query that I am trying to get into VBA" might update more than one record int tbl_Archive?

You should either use your existing references to the form controls or include 13 more values in the function arguments.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Dhookom,
I removed the Comma before Where statement.
Adding primary keys....question...the Archive table would need 3...BEMS_ID, Model, and Training_Year to make this work. The BEMS_ID is uniqued to an individual. Each individual can have one or more models, and the Training_Year is identifies the year the employee was trained. There will be new entries for each employee for each year.
Training is repeated on a yearly bases. This is ment to store training history for each person over many years (training years)

So, since the Model_Data table and the Archive table both have these values should I make all three primary keys in their respective tables? Should I create a Relationship between them?

Where would I put the from statement. I know in SQL I can have a select, from, where, order sequence. But this SQL does not use this exact sequence.

Would you be so kind as to take a snipet of my code and insert the From for me. I will learn from this, I promise.

Query more that one record....no. I will be on an individual persons screen and will update the Completion date for one of the training items. The update will cause the Archive to be updated for that person, model, and training year. I would also want this to work next year..2015 when the very first training is completed. At that time the Archive table will not have the unique combinatio of person, model, training year. So, will the update code work or will I have to do an append for the first one of the year?

I am passing the BEMS and Model from the form. I tried passing the Training Year but ran into errors.

I appreciate your patience with me on this. You gave me one good SQL code a week or so ago. That was my first. Evidently I can not just plagerize that...I am doing something differnent. So, I need your help again.

 

[puforee]

Andrzejek
All most all of these are dates. I tried replacing both 's with #. It did not like it.

       [COLOR=#CC0000] " [Last PC] = #" & [tbl_Models_Data]![Last PC] &"#," &_" &
        "WHERE BEMS_Id= " & lngBEMS_ID & " AND Model= """ & strModel & """ "[/color]

Also, what would the end of the last statement look like just before the WHERE. Does the last comma
have to be removed.

 

[dhookom]

Apparently there is a need to create a new record (append query) if one doesn't already exist in the archive table. FMS has examples of update queries with joins at Update with Values from Fields in Another Table. If you use a join with all records from tbl_Models_Data, it can be used to either update or append.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Andy's solution won't work without adding some reference to tbl_Models_Data. There is no way the code as it exists will understand tbl_Models_Data.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

puforee,
In my (simple) mind, I would start from the beginning. When I need an Update SQL, (1) I create one with hard-coded data, (2) run the hard-coded code and (3) make sure I will update only the records I intend to update (correct WHERE part), and then (4) replace my hard-coded values with variables / data from other record set.

1.

strSQL = "UPDATE tbl_Archive SET " & _
  " [Last PC] = #[red]1/1/2014[/red]#," & _
  " [Last IP Eval] = #[red]5/5/2014[/red]# " & _
  " WHERE BEMS_Id = [red]1234[/red]" & _
  " AND Model = '[red]XYZ[/red]'"

so when I run it I should get (2):
[tt]
UPDATE tbl_Archive SET
[Last PC] = #1/1/2014#,
[Last IP Eval] = #5/5/2014#
WHERE BEMS_Id = 1234
AND Model = 'XYZ'
[/tt]

And then 3:

if your fields/variables hold:[tt]
[tbl_Models_Data]![Last PC] = 1/1/2014
[tbl_Models_Data]![Last IP Eval] = 5/5/2014
lngBEMS_ID = 1234
strModel = XYZ[/tt]

I replace my hard-coded values with fields/variables:

strSQL = "UPDATE tbl_Archive SET " & _
  " [Last PC] = #[red]" & [tbl_Models_Data]![Last PC] & "[/red]#, " & _
  " [Last IP Eval] = #[red]" & [tbl_Models_Data]![Last IP Eval] & "[/red]# " & _
  " WHERE BEMS_Id = [red]" & lngBEMS_ID[/red]" & _
  " AND Model = '[red]" & strModel & "[/red]'"

I should end up with the same SQL at the end:
[tt]
UPDATE tbl_Archive SET
[Last PC] = #1/1/2014#,
[Last IP Eval] = #5/5/2014#
WHERE BEMS_Id = 1234
AND Model = 'XYZ'
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

....and no, as you can see - there is NO comma before WHERE

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[puforee]

Andy,

Tried Hard code:

Public Function UpdateArchiveTbl(lngBEMS_ID As Long, strModel As String)
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 " & _
" [Last PC] = #1/1/2014#," & _
" [Last IP Eval] = #5/5/2014# " & _
" WHERE BEMS_Id = 1958902 & _
" AND Model = 'A330'"

'Debug.Print strSQL
db.Execute strSQL, dbFailOnError
End Function

As soon as I put in A330 for the model everything turned red. And It highlights the Model statement and says it expedted: end of statement.
Any suggestions


Any Ideas?

 

[dhookom]

pufuree,
Please use TGML Code tags. You are missing a closing quote as noted. Also, the SQL statement still will have no idea where to pull the [tbl_Models_Data] values from unless it is included in a JOIN to tbl_Archive.

Public Function UpdateArchiveTbl(lngBEMS_ID As Long, strModel As String)
  Dim strSQL As String
  Dim db As DAO.Database
  Set db = CurrentDb
  [COLOR=#73D216][COLOR=#4E9A06]' the following assumes BEMS_ID is numeric and Model/AC is text[/color][/color]
  strSQL = "UPDATE tbl_Archive SET " & _
    " [Last PC] = #1/1/2014#," & _
    " [Last IP Eval] = #5/5/2014# " & _
    " WHERE BEMS_Id = 1958902 [COLOR=#A40000][highlight #FCE94F]"[/highlight][/color] & _
    " AND Model = 'A330'"
 
  'Debug.Print strSQL
   db.Execute strSQL, dbFailOnError
End Function

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

" WHERE BEMS_Id = 1958902" & _

 

[puforee]

All. The hard code worked. Now for the hard part...adding the other table.

Thanks to all so far. I will be back...probably tomorrow.

 

[puforee]

OK...I had a little time left today so I tried Andy's full up code.

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" & _
" [Last PC] = #" & [tbl_Models_Data]![Last PC] & "#, " & _
" [Last IP Eval] = #" & [tbl_Models_Data]![Last IP Eval] & "# " & _
" FROM [tbl_Models_Data]" & _
" WHERE BEMS_Id = " & lngBEMS_ID & _
" AND Model = '" & strModel & "'"

'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

I received a Compile Error: External Name not defind and tbl_Models_Data was highlighed blue.

Duane...I have take all your suggestion except putting in the FROM code. I am not sure where to put it. I tried after SET and Before WHERE. No luck.
Guidance please..or is something else wrong

 

[dhookom]

The syntax was found in the link I provided earlier to the FMS web page.

UPDATE tblAddress
INNER JOIN tblZipCodes ON [tblAddress].[ZipCode] = [tblZipCodes].[ZipCode]
SET [tblAddress].[City]  = [tblZipCodes].[City], 
    [tblAddress].[State] = [tblZipCodes].[State]
WHERE ([Country] = 'US')

Your query would Update the archive table joined to the data table. The join fields must be a primary key in at least the data table. If your SET field list includes all field including the primary key fields and the join is a left join it may add new records to tbl_Archive.

tbl_Models_Data LEFT JOIN tbl_Archive ON ... field = field list ...

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Duane,

I assumed you wanted me to use the LEFT JOIN instead of the INNER JOIN. So, I followed your instructions but the example and your LEFT JOIN code does not show the complete syntax...so I got: (I get confused as to when to use " and when not to.)

Everythiing is in RED and it does not like the Set statement.

strSQL = "UPDATE tbl_Archive" & _
    "tbl_Models_Data LEFT JOIN tbl_Archive On [tbl_Models_Data].[Model]=[tbl_Archive].[Model]," & _
    "[tbl_Models_Data].[BEMS_Id] = [tbl_Archive].[BEMS_Id],[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] & "# " & _
    " FROM [tbl_Models_Data]" & _
    " WHERE BEMS_Id = " & lngBEMS_ID & _
    " AND Model = '" & strModel & "'"
 
  'Debug.Print strSQL
   db.Execute strSQL, dbFailOnError

And secondly, I took out the FROM statement....that did not work either still all red:

  strSQL = "UPDATE tbl_Archive" & _
    "tbl_Models_Data LEFT JOIN tbl_Archive On [tbl_Models_Data].[Model]=[tbl_Archive].[Model]," & _
    "[tbl_Models_Data].[BEMS_Id] = [tbl_Archive].[BEMS_Id],[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] & "# " & _
    " WHERE BEMS_Id = " & lngBEMS_ID & _
    " AND Model = '" & strModel & "'"
 
  'Debug.Print strSQL
   db.Execute strSQL, dbFailOnError

 

[dhookom]

I should have mentioned/questioned this earlier but why are you keeping an archive table? We typically have a status field in the records that is used to determine if the record is "Active" or "Archive".

Your syntax didn't come to close to the example I posted. Your join fields need " AND " between them and you don't use hardly any quotes in the middle of your lines. Also you were missing some spaces.

strSQL = "UPDATE tbl_Archive " & _
    " RIGHT JOIN tbl_Models_Data On [tbl_Archive].[Model]=[tbl_Models_Data].[Model][highlight #FCE94F] AND [/highlight]" & _
    "   [tbl_Archive].[BEMS_Id] = [tbl_Models_Data].[BEMS_Id][highlight #FCE94F] AND [/highlight]" & _
    "   [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
[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