DoCmd.RunSQL help!

[stephenchan433]

Hi everyone and just wanted to say a big thank you for helping me out... I have the query written successfully, but am struggling to get the information entered successfully in the events/After Update.

Below is my code:
table append from = Item Master
table append to = RM_Pricing
field = RM_Code

When I enter new information to the RM_Code field it gives me "you are about to append 0 rows" but when I click on the query it actually populates the info.

------------------------------------------------------


Private Sub RM_CODE_AfterUpdate()




DoCmd.RunSQL "INSERT INTO tblRM_Pricing ( RM_CODE )" & _
"SELECT [tblItem Master].RM_CODE AS Expr1 " & _
"FROM [tblItem Master] LEFT JOIN tblRM_Pricing ON [tblItem Master].RM_CODE = tblRM_Pricing.RM_CODE " & _
"WHERE (((tblRM_Pricing.RM_CODE) Is Null));"


End Sub

----------------------------------------------------

Big thanks again!

[ItsMe]

It is likely you will get multiple msgs regarding the updates because you have nested subqueries. If you want to validate the number of records that will be affected you can use some other methods to update. Alternatively, you can suppress the messages. Yet, another option would be to use

'As a simple example how to employ
CurrentDB.Execute "Insert your statement", dbFailOnError

[stephenchan433]

doesn't work

It's still not updating my other table [tblRM_Pricing] when i changed the DoCmd.RunSQL to CurrentDB.Execute

[ItsMe]

I am not fully understanding. Sometimes it works and other times it does not? I based my previous response mostly on your following statement. Perhaps you can elaborate and what you are doing when it works and what you are doing when it does not work.

When I enter new information to the RM_Code field it gives me "you are about to append 0 rows" but when I click on the query it actually populates the info.

[stephenchan433]

Hi:

The query works when I manually run it. What I'm trying to do is automate the query in the event properties and have it run automatically when a user enters new data (RM_CODE) field in the "Item_Master" table. When new data is enter it should trigger the query in the event property to create the same record in the RM_Pricing table. Currently, it is not doing it at automatically, but if I manually run the query it works. Thanks

[ItsMe]

What is the name of the query that functions correctly? You can use the name of the query in your VBA code.
currentdb.execute "MyQueryName"

Maybe your VBA code is not executing.
Msgbox "Attempting Now"
currentdb.execute "MyQueryName"

When I stare at your SQL statement. Nothing is jumping out as incorrect. The only thing I can see that I would of typed would be ( RM_CODE ) " & _ instead of ( RM_CODE )" & _

But I do not think that matters and if it did matter, you would get an error.

Go ahead and try running the query object using the name of the query and the message box. Also, post the actual SQL from the object here.

[stephenchan433]

I got it resolved. I used DoCmd.OpenQuery "Query1" did the trick... It was the placement of where I created the event it should be after I closed the application not when I entered the first bit of record.

[ItsMe]

Glad to hear you were able to get it working and thanks for posting how you resolved your issue.