Syntax Error in changing a field’s contents (1 Viewer)

[access2010]

We have been trying to correct the code in this Access 2003 form without success and continue to have a syntax error.

Any assistance will be appreciated.

Thank you
Marylyn

 

[bob fitz]

Try:

Private Sub Clear_Investigate_Yes_Click()
' Fuction Name ===> In the field Investigate, Change "Yes" => "No"
' Table Name ==> update Investments01_tbl
' Field Name ==> investigate
' Change Data in Field Investigate, from Yes to No
''CurrentDb.Execute "update Investments01_tbl" & _
''  "set investigate = 'No' " & _
''  "where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
CurrentDb.Execute "UPDATE Investments01_tbl SET Investments01_tbl.Investigate = 'No' " & _
    "WHERE (((Investments01_tbl.Investigate)='Yes'));"

Me.Requery
End Sub

 

[The_Doc_Man]

Your problem is in this SQL string, which is in the Click_Event code for the example button in your sample database.

CurrentDb.Execute "update Investments01_tbl" & _
  "set investigate = 'No' " & _
  "where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"

When I attempt to examine Me.RecordSource, it is empty. Your syntax error is that your SELECT sub-query HAS no recordsource. Tracing that back, the form Analytics_F has an EMPTY .RecordSource property.

Marylyn, it seems to me that you have had this problem before. For instance,

Change field name from Yes to No

Could I please receive assistance on how to change the field contents of the field = Investigate = in our Access 2003 Db, if it contains the word =Yes= to = No = by clicking the button = Clear Investigate = The Form = Investments_StockBrowse_F_As_Report = WORKS The Form = MainMenu = is not...

www.access-programmers.co.uk

You were previously advised of this same exact problem but don't seem to want to fix it. Well, if you don't do something to fix it, you aren't going to make any progress. It seems like you don't understand that to use Me.RecordSource in any context, the form in question has to be bound to something - and in your example database it is not. That property is EMPTY.

The specific syntax error in this case is that the sub-query after performing the concatenation noted reads (SELECT Investment_ID from ())
and that just is NOT going to work. You are, in effect, asking Access to select data from nowhere. Access doesn't pluck numbers from out of thin air unless your table is NAMED [Thin Air].

 

[bob fitz]

Cross-posted: https://www.accessforums.net/showthread.php?t=89178

Please read: https://excelguru.ca/a-message-to-forum-cross-posters/

 

[Gasman]

All of which could be identified if you put it all into a string variable and just Debug.Print the variable before even trying to use it?

I always put spaces at the front of the next string,(saves having to scroll all the way to the right to see if one is there? ), but do not use continuation much, preferring concatenation

strSQL =  "update Investments01_tbl" & _
  " set investigate = 'No' " & _
  " where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
Debug.Print strSQL
'CurrentDB.Execute  strSQL

 

[The_Doc_Man]

I thought I had seen this before. I am speaking now as a moderator. This problem keeps coming back to haunt us. Here is an older iteration.

Trying to Change Yes to No

We are having an error on only 1 of 3 Access 2003 forms in changing the Yes field to No. Form Yes_1 and form Yes_2 works. The form Main menu does not co-operate. Could I please be advised as what I have done wrong? How would I add the message, “Yes has been removed” as a note after the query...

www.access-programmers.co.uk

Either you are not paying attention or another situation is going on here. Is this account being shared by a group? I have seen your replies that suggest there is a group - or else you started using a royal "We" ... but also, back in February, you signed your posts with FOUR different names: Crystal, Marylyn, Nicole, and Paul.

It seems that this is at least the third time (in this forum) that you have been told about this exact same problem: You use an unbound form's non-existent .RecordSource - which of course triggers errors because it is blank but is used where blanks aren't allowed - and it seems that you don't learn from these mistakes. If the REAL problem is that this is a shared account then you need to let those who are sharing it know about the answers that other sharers have gotten. You are not using this forum wisely as a resource and should realize that we keep records of prior posts. When someone acts in a way to waste resources, we aren't happy because WE, the answering members, ARE the resources.

You have posted this identical problem or a closely related problem on:
Dec 9, 2023
Jul 6, 2023
Nov 30, 2022

Per Bob Fitz, you have also cross-posted this problem, so that makes at least four posts.

I have seen in some of the threads that you claim to have limited programming experience and are working for a poor NGO. You seriously need to find a way to upgrade your programming skills somewhat and perhaps not only help your employer but help yourself, too. But when we solve a problem several times and it keeps coming back to haunt us, our patience will start to wear thin. We actually would prefer for that to not happen, but ignoring or carelessly forgetting or mislaying our answers will lead to people putting you on an IGNORE list. Try to at least meet us halfway and pay closer attention to questions for which you have already gotten answers. Probably wouldn't hurt to learn to use our search feature and the "Similar Threads" section - which happens to contain a link to a previous iteration of this same problem!

I am taking no punitive action here, just posting a warning and comment. But remember that you are asking us to volunteer our time to help you fix problems. At least show us the common courtesy to act like you paid attention to what we told you.

 

[access2010]

Try:

Private Sub Clear_Investigate_Yes_Click()
' Fuction Name ===> In the field Investigate, Change "Yes" => "No"
' Table Name ==> update Investments01_tbl
' Field Name ==> investigate
' Change Data in Field Investigate, from Yes to No
''CurrentDb.Execute "update Investments01_tbl" & _
''  "set investigate = 'No' " & _
''  "where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
CurrentDb.Execute "UPDATE Investments01_tbl SET Investments01_tbl.Investigate = 'No' " & _
    "WHERE (((Investments01_tbl.Investigate)='Yes'));"

Me.Requery
End Sub

Thank you Bob for your suggestion, we have tried to use your suggested code in our Access 2003 database without success. The error message that we get is //
Run-time error’3144’:
Syntax error in UPDATE statement.
= = = =
I am trying to; In the Field Investigate, change from Yes, => No
or In the Field Investigate, change from Watch => No
The field may contain the following values, NO, Stop, Watch or Yes

With our OLD forms, the click code that we used previously worked was;
Private Sub Clear_Investigate_Yes_Click()
' File Name ===> Clear_Investigate_Yes
CurrentDb.Execute "update Investments01_tbl " & _
"set investigate = 'No' " & _
"where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
Me.Requery
End Sub

Thank you, Marylyn

 

[access2010]

Your problem is in this SQL string, which is in the Click_Event code for the example button in your sample database.

CurrentDb.Execute "update Investments01_tbl" & _
  "set investigate = 'No' " & _
  "where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"

When I attempt to examine Me.RecordSource, it is empty. Your syntax error is that your SELECT sub-query HAS no recordsource. Tracing that back, the form Analytics_F has an EMPTY .RecordSource property.

Marylyn, it seems to me that you have had this problem before. For instance,

Change field name from Yes to No

Could I please receive assistance on how to change the field contents of the field = Investigate = in our Access 2003 Db, if it contains the word =Yes= to = No = by clicking the button = Clear Investigate = The Form = Investments_StockBrowse_F_As_Report = WORKS The Form = MainMenu = is not...

www.access-programmers.co.uk

You were previously advised of this same exact problem but don't seem to want to fix it. Well, if you don't do something to fix it, you aren't going to make any progress. It seems like you don't understand that to use Me.RecordSource in any context, the form in question has to be bound to something - and in your example database it is not. That property is EMPTY.

The specific syntax error in this case is that the sub-query after performing the concatenation noted reads (SELECT Investment_ID from ())
and that just is NOT going to work. You are, in effect, asking Access to select data from nowhere. Access doesn't pluck numbers from out of thin air unless your table is NAMED [Thin Air].

= = =
Thank you The_Doc_Man for your comments. We are volunteers at a charity helping to run their office. The code that we tried to transfer to a new form does NOT work, while it does work on their OLD forms.

So I will use the old forms.
Thank you, Marylyn

 

[access2010]

Cross-posted: https://www.accessforums.net/showthread.php?t=89178

Please read: https://excelguru.ca/a-message-to-forum-cross-posters/

Thanks Bob for your note about the cross posting, which will not happen again. I / we are under a pressure at the charity as all of their reports have to be completed by December 29th and little things like changing the field name from Yes to No are slowing us down.
Regards, Marylyn

 

[cheekybuddha]

@access2010 / Marylyn - there is no problem with cross-posting.

It just helps (a lot) if you take the extra minutes to explain you have done so and add the links to the cross-posts.

Actually, it is likely to save you more time than those extra minutes in the long run, since folk have the opportunity to build on the posts in the other threads rather than risk repeating the same stuff.

Have a great Christmas/holidays!

 

[access2010]

All of which could be identified if you put it all into a string variable and just Debug.Print the variable before even trying to use it?

I always put spaces at the front of the next string,(saves having to scroll all the way to the right to see if one is there? ), but do not use continuation much, preferring concatenation

strSQL =  "update Investments01_tbl" & _
  " set investigate = 'No' " & _
  " where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
Debug.Print strSQL
'CurrentDB.Execute  strSQL

Thank you Gasman for your suggestion, BUT for some reason they do not work on the new Access 2003 form we tried to create. We will go back to using an OLD form that works. Your suggestions are appreciated.
Marylyn

 

[access2010]

@access2010 / Marylyn - there is no problem with cross-posting.

It just helps (a lot) if you take the extra minutes to explain you have done so and add the links to the cross-posts.

Actually, it is likely to save you more time than those extra minutes in the long run, since folk have the opportunity to build on the posts in the other threads rather than risk repeating the same stuff.

Have a great Christmas/holidays!

Thank you cheekybuddha for your note, which I appreciate. I was trying to find a solution to the reports the Charity needed completed by Dec 29th.
Enjoy the holidays.
Marylyn

 

[The_Doc_Man]

= = =
Thank you The_Doc_Man for your comments. We are volunteers at a charity helping to run their office. The code that we tried to transfer to a new form does NOT work, while it does work on their OLD forms.

So I will use the old forms.
Thank you, Marylyn

This is again a case of not wisely using the forum as a resource. There is nothing wrong with the idea of using a "new form" or "old form" as long as you realize WHY one doesn't work.

We are just asking you to pay attention. Your answer that I quoted above says that you did not. All you need to do is recognize that if you are going to USE a construct such as Me.RecordSource, the form needs to HAVE a .RecordSource. If you would realize that and just CHECK whether the form has a .RecordSource, you wouldn't run into the problem.

Blindly copying code from one form to another is a sign of not paying attention to the answer provided for the problem you just posted. If the code failed on form X because it didn't have a recordsource then don't expect form Y to work without a recordsource either. If you copy the code but don't check for the condition that caused the failure, you haven't learned anything useful. Computers are actually quite predictable. If code fails because something is missing on form X, it will probably fail if that something is missing on form Y.

LEARN from this forum. PLEASE. It is why we are here. Not to hold your hand at every little step of every little problem, but to show you the way through the minefield of putting together a working form so that you can learn. Each of us on the forum who answer your problems and others are, in our own way, teachers. We WANT to help you. But try to learn from what we post.

 

[Pat Hartman]

Any assistance will be appreciated.

You cannot seem to help yourself no matter how many times we tell you exactly what the problem is. We can't fix it for you since we do not have enough information. You have never answered any question that would lead us to be able to solve the problem. You also don't seem capable of following the simplest of directions or understanding any technical concept that would help you to resolve the problem yourself. The problem is and has ALWAYS been that you are using an unbound form (no recordsource) and using code that references the non-existent recordsource. We have told you that and have asked how to determine the selection criteria by NOT pointing to a non-existent recordsource and have gotten no usable answer.

If similar code is working for you in a different situation, it is because it is being run from a bound form. To prove this to yourself, open the form that works and look at the form's RecordSource property. You will see that it is not empty. It contains either the name of a form or querydef or an actual SQL String. THAT is the difference between the form that works and the one that doesn't work. The form that doesn't work, has no RecordSource and logically, it seems that it shouldn't. THEREFORE, you need some other method of specifying the criteria for the query that is failing.

Many of the people here donate time to charities to help fix problems like this but you've never given anyone any information about your charity or asked for a donation of help. It is 12/26 and the clock has run out. I'm working on a project and cannot spend the time this week to help. Perhaps someone else can.