UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error Converting Append Query To Vba, Access 2013    
 
   
ScottyBee
post Sep 17 2019, 04:28 PM
Post#1



Posts: 50
Joined: 6-June 05
From: Portland Oregon


Hello,

I have a query with the following code that runs against the value on a subform. The code is:

CODE
INSERT INTO RatingsLog ( RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy )
SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy
FROM Ratings
WHERE (((Ratings.RatingID)=[Forms]![EER Form]![frmCat01_OEM_Spares_P1].[form]![RatingID]));


This code runs just fine but I need to convert it to code that runs in a VBA update event. The modified code is:

CODE
Sub AppendRatingsData()
    Dim db As DAO.Database
    Set db = CurrentDb
    
    DoCmd.RunSQL "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy)" _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy" _
        & "FROM Ratings" _
        & "WHERE (((Ratings.RatingID)=  [Forms]![EER Form]![frmCat01_OEM_Spares_P1].[form]![RatingID]));"

    'Cleanup after ourselves
    Set db = Nothing
End Sub


When I run the code, I get error "Run time error 3075 error in missing operator in......)

Any ideas on what I am missing? Once I get this to work, I have several other subforms that will use this same code and I will add a parameter to this Sub to pass in the subform name---Thanks!

Go to the top of the page
 
tina t
post Sep 17 2019, 04:45 PM
Post#2



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


try the following, as

CODE
Sub AppendRatingsData()
'    Dim db As DAO.Database
'    Set db = CurrentDb
    
    DoCmd.RunSQL "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) " _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy " _
        & "FROM Ratings " _
        & "WHERE (((Ratings.RatingID)=  " & [Forms]![EER Form]![frmCat01_OEM_Spares_P1].[form]![RatingID]));

    'Cleanup after ourselves
'    Set db = Nothing
End Sub

the above syntax assumes that RatingID is a Number data type. also, i put a space at the end of each line, before the closing double quote. and i commented out the Dim and Set statements, since they are not being used.

it's a good idea to assign the SQL statement to a string variable before running it, so you can print the statement to the Immediate window and examine it for flaws; that would have helped you find the missing spaces in the text.

hth
tina
This post has been edited by tina t: Sep 17 2019, 04:46 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ScottyBee
post Sep 17 2019, 05:11 PM
Post#3



Posts: 50
Joined: 6-June 05
From: Portland Oregon


Hello Tina, I copied and pasted your code and received a "compile error--syntax error". Below is the code that I pared down and incorporated your suggestion on assigning the SQL statement to s string variable. The strSQL text is displaying with a red text color in my debugging window.

CODE
Sub AppendRatingsData()
    Dim strSQL As String
    
     strSQL = "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) " _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy " _
        & "FROM Ratings " _
        & "WHERE (((Ratings.RatingID)=  " & [Forms]![EER Form]![frmCat01_OEM_Spares_P1].[form]![RatingID]));

DoCmd.RunSQL strSQL
End Sub
Go to the top of the page
 
MadPiet
post Sep 17 2019, 05:24 PM
Post#4



Posts: 3,334
Joined: 27-February 09



replace this:

DoCmd.RunSQL strSQL

with

Debug.Print strSQL

then copy that to a new query editor window and run it. What happens?
Go to the top of the page
 
ScottyBee
post Sep 17 2019, 05:30 PM
Post#5



Posts: 50
Joined: 6-June 05
From: Portland Oregon


Got it to work by stripping out the parenthesis. Here is the code:

CODE
Sub AppendRatingsData()
    Dim strSQL As String
    
     strSQL = "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) " _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy " _
        & "FROM Ratings " _
        & "WHERE Ratings.RatingID =  " & [Forms]![EER Form]![frmCat01_OEM_Spares_P1].[Form]![RatingID]

DoCmd.RunSQL strSQL
End Sub



Thanks Tina!
Go to the top of the page
 
ScottyBee
post Sep 17 2019, 05:34 PM
Post#6



Posts: 50
Joined: 6-June 05
From: Portland Oregon


Hello MadPiet, just saw your post after my last submission: I see this in the immediate window:

CODE
debug.Print strSQL
INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy FROM Ratings WHERE Ratings.RatingID =  405


Thanks for your reply as I learned another debugging technique.
Go to the top of the page
 
ScottyBee
post Sep 17 2019, 06:23 PM
Post#7



Posts: 50
Joined: 6-June 05
From: Portland Oregon


By the way, how do I mark threads as "Solved" in this forum? I searched around and don't see that option anywhere. Thanks
Go to the top of the page
 
tina t
post Sep 17 2019, 07:07 PM
Post#8



Posts: 6,125
Joined: 11-November 10
From: SoCal, USA


QUOTE
Got it to work by stripping out the parenthesis.

good job figuring that out, and sorry i forgot to ditch them when i edited the string, my bad! anyway, as you say, it's a good learning experience, as you learned a few things! :)

there's not "Solved" mark in the UA forums; your last post tells the tale. and Piet and i were glad to help. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 07:47 AM