Could we please have assistance in changing the data in a field.
The code that I have entered does not work.
A = Change Data in Field Investigate, from Yes to No
B = Change Data in Field Investigate, from Watch to No
I looked at your code but I'm not going to execute someone else's code on my computer. (No disrespect intended.) You need to learn how to debug this kind of problem for yourself. Learn to use the debug features and learn how to examine variables at run-time to determine their content. You'll help yourself much better in the long run. I'm not going to leave you totally with no advice, though.
For this problem: You should set a breakpoint in the code at the entry point to the button-click event routine so that when you push the button on your form, you can single-step and examine the contents of that SQL string that you build. I suspect your "IN" clause in the SQL string behind the Change Yes button doesn't work correctly and thus your WHERE clause fails to select anything.
It would be nice if you would tell us a bit more than "does not work." Does it give an error? (If so, what error?) Does it make changes to the wrong records? "Does not work" is possibly one of the least useful descriptions you can give us because it gives us nothing to work with. We really do want to help folks, but you need to meet us halfway and give us a decent description of the behavior that you think is wrong.
I just looked at your first button that should change the "yes" to "no". It appears that the subquery is not populating a valid table to select from due to there not being a record source for your example form. The query depends on there being an sql string in the record source of the form and there is none.
Here is the sql string that is being generated from your code. Notice the subquery is not selecting from any table or query.
Code:
UPDATE Investments01_tbl WHERE Investmentl_ID in (SELECT Investmentl_ID from ());
This is ringing a bell. This is a very unusual error. There was an earlier thread where I went to great pains to explain what was wrong with the query and how to fix it but the information required was never forthcoming.
Me.RecordSource = the recordsource for the form named Analytics_F which is blank since the form isn't bound to any data, nor should it be. That means that the query raises an error. To make debugging easier, it is best to build SQL strings INTO a string and then execute the string. That will give you a way of seeing the string that you are sending to the query engine. This is the modified code to enable that.
Code:
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
Dim strSQL As String
strSQL = "update Investments01_tbl" & _
"set investigate = 'No' " & _
"where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
CurrentDb.Execute strSQL
Me.Requery
End Sub
Mike posted the string that is being sent so you can see the missing RecordSource Name.
WHAT table or query do you want to run the update query against? I see no other table that includes the Investmentl_ID and you have no RI defined so I can't back into a non-identical name by finding a relationship.
Also, this query does not call for a sub query so it would be more efficient to use a join.
So, WHAT TABLE are you actually trying to update with the data from Investments01_tbl?
And last but not least, using special characters or embedded spaces in your object names makes them "offensive" to VBA and therefore you always have to encase them in square brackets [], Plus, there are places where VBA will actually change the offensive characters to underscores so that the code will work since these offensive names cannot be used in VBA.
I looked at your code but I'm not going to execute someone else's code on my computer. (No disrespect intended.) You need to learn how to debug this kind of problem for yourself. Learn to use the debug features and learn how to examine variables at run-time to determine their content. You'll help yourself much better in the long run. I'm not going to leave you totally with no advice, though.
For this problem: You should set a breakpoint in the code at the entry point to the button-click event routine so that when you push the button on your form, you can single-step and examine the contents of that SQL string that you build. I suspect your "IN" clause in the SQL string behind the Change Yes button doesn't work correctly and thus your WHERE clause fails to select anything.
It would be nice if you would tell us a bit more than "does not work." Does it give an error? (If so, what error?) Does it make changes to the wrong records? "Does not work" is possibly one of the least useful descriptions you can give us because it gives us nothing to work with. We really do want to help folks, but you need to meet us halfway and give us a decent description of the behavior that you think is wrong.
Thank you The_Doc_Man for your note, the code below is what we have tried.
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, ";", "") & "));"
Me.Requery
The error received is;
Run-time error ’3144’
Syntax error in UPDATE statement
Thank you The_Doc_Man for your note, the code below is what we have tried.
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, ";", "") & "));"
Me.Requery
The error received is;
Run-time error ’3144’
Syntax error in UPDATE statement
One of the all-time great trouble-shooting methods ever devised is to create a SQL String from the concatenated bits and pieces.
Like this:
Dim strSQL As String
...
strSQL =
"update Investments01_tbl" & _
"set investigate = 'No' " & _
"where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
Now, you can use Debug.Print to send that string to the immediate window:
Debug.Print strSQL
Now, you have a SQL string which can be tested in the query designer. That often returns more meaningful, useful error messages, making it easier for you to trouble-shoot the syntax yourself.
I just looked at your first button that should change the "yes" to "no". It appears that the subquery is not populating a valid table to select from due to there not being a record source for your example form. The query depends on there being an sql string in the record source of the form and there is none.
Here is the sql string that is being generated from your code. Notice the subquery is not selecting from any table or query.
Code:
UPDATE Investments01_tbl WHERE Investmentl_ID in (SELECT Investmentl_ID from ());
This is ringing a bell. This is a very unusual error. There was an earlier thread where I went to great pains to explain what was wrong with the query and how to fix it but the information required was never forthcoming.
Me.RecordSource = the recordsource for the form named Analytics_F which is blank since the form isn't bound to any data, nor should it be. That means that the query raises an error. To make debugging easier, it is best to build SQL strings INTO a string and then execute the string. That will give you a way of seeing the string that you are sending to the query engine. This is the modified code to enable that.
Code:
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
Dim strSQL As String
strSQL = "update Investments01_tbl" & _
"set investigate = 'No' " & _
"where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
CurrentDb.Execute strSQL
Me.Requery
End Sub
Mike posted the string that is being sent so you can see the missing RecordSource Name.
WHAT table or query do you want to run the update query against? I see no other table that includes the Investmentl_ID and you have no RI defined so I can't back into a non-identical name by finding a relationship.
Also, this query does not call for a sub query so it would be more efficient to use a join.
So, WHAT TABLE are you actually trying to update with the data from Investments01_tbl?
And last but not least, using special characters or embedded spaces in your object names makes them "offensive" to VBA and therefore you always have to encase them in square brackets [], Plus, there are places where VBA will actually change the offensive characters to underscores so that the code will work since these offensive names cannot be used in VBA.
Thank you Pat Hartman for your suggestions. I have attached the table “Investments01_tbl” and used your debugging suggestion but did not change the query and a new error occurs Compile Error “Ambiguous name detected: Clear_Investigate_Yes_Click”
Nicole
One of the all-time great trouble-shooting methods ever devised is to create a SQL String from the concatenated bits and pieces.
Like this:
Dim strSQL As String
...
strSQL =
"update Investments01_tbl" & _
"set investigate = 'No' " & _
"where Investmentl_ID in (SELECT Investmentl_ID from (" & Replace$(Me.RecordSource, ";", "") & "));"
Now, you can use Debug.Print to send that string to the immediate window:
Debug.Print strSQL
Now, you have a SQL string which can be tested in the query designer. That often returns more meaningful, useful error messages, making it easier for you to trouble-shoot the syntax yourself.
If what you want to do is to update the values in ONE specific table, then you would use a query that did that thing. Your query does something entirely different.
This is what the query you are now requesting would look like.
Code:
UPDATE Investments01_tbl SET Investments01_tbl.Investigate = "No"
WHERE Investments01_tbl.Investigate = "Yes";
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
Look at my answers in 10 and 12. If the table is not variable, you don't need the subquery and the variable reference and so the simplified versions should finally solve your problem.
Thank you The_Doc_Man for your note, the code below is what we have tried.
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, ";", "") & "));"
Me.Requery
The error received is;
Run-time error ’3144’
Syntax error in UPDATE statement
Direct suggestion: Ditch the subquery. You are pulling something from Me.Recordsource and concatenating it into the SQL via a subquery. DON'T GO AFTER DATA INDIRECTLY. If the Investment ID is a control on the form, concatenate THAT directly into something like
or whatever control it is. Oddly enough, if your Investmentl_ID actually IS a field in your recordsource, it is available with Me.fieldname syntax anyway. Turns out that bound forms have the fields of the recordsource visible in event code.
All you are doing with that overly complex IN ( SELECT sub-query is confusing the issue. Assuming that in this is in a form (as seems likely based on the name of the entry point for the Click event), that is normally a single record at a time, so you are attempting to generate a sub-query to pull in the required ID number - but the single-record nature of the form's operation means that the Investmentl_ID is single-valued. So other than trying to look "cute" or something, there is pure overkill in your approach. If the ID you want is on the form, use a Me.xxxx style of reference to pick up that ID directly. It will encompass the SAME EXACT NUMBER OF RECORDS that you would have gotten in what you tried (if it had worked at all). And what you used was returning a blank anyway, which is the origin of your error 3144 - because that blank leaves the subquery with a blank domain name for the FROM clause. A definite no-no.
I'm trying to not be harsh but you seem to be flailing around, getting nowhere fast. The link that Pat provided shows that you have been facing this problem for a whole year, even using the same field name and same IN clause. It makes us wonder if you have been paying attention to our advice. Like I said, I don't want to be harsh, but this seems like an exercise in futility.
