Change field name from Yes to No (1 Viewer)

[access2010]

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 working.

Could I please be advised as to what I have done wrong?

Thank you for your help.

Nicole

 

[Uncle Gizmo]

See video number 4 here:-

Lock, Unlock Controls - Nifty Access

Lock, Unlock Controls A problem that crops up from time to time is the need to handle a Group of Controls differently from the rest of the Controls on a Form.Usually this is the need to Lock the Controls so that Data cannot be changed, and conversely Unlock them. However it may be the need

www.niftyaccess.com

The video demonstrates how to replace two command buttons with a single command button.

The same idea could be applied in your case.

 

[Pat Hartman]

The main form is not working because you are using a subquery that evaluates to null. Not sure why you are using a subquery at all.

I changed the code to build the string into a variable so I could easily display the variable before executing the query.

Private Sub Clear_Yes_Investigate_Click()
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

I put a stop on the execut line and printed the query.

print strsql
update Investments01_tbl set investigate = 'No' where Investmentl_ID in (SELECT Investmentl_ID from ());

As you can see the from is empty since the form has no recordsource.

The query could be :

update Investments01_tbl set investigate = False where investigate = True

PS - the Yes/No field is numeric internally so I don't use strings to update it.

 

[access2010]

See video number 4 here:-

Lock, Unlock Controls - Nifty Access

Lock, Unlock Controls A problem that crops up from time to time is the need to handle a Group of Controls differently from the rest of the Controls on a Form.Usually this is the need to Lock the Controls so that Data cannot be changed, and conversely Unlock them. However it may be the need

www.niftyaccess.com

The video demonstrates how to replace two command buttons with a single command button.

The same idea could be applied in your case.

Thank you Uncle Gizmo for your link.

My question though is why does my code work on one form, but not the other.

Crystal.

 

[access2010]

The main form is not working because you are using a subquery that evaluates to null. Not sure why you are using a subquery at all.

I changed the code to build the string into a variable so I could easily display the variable before executing the query.

Private Sub Clear_Yes_Investigate_Click()
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

I put a stop on the execut line and printed the query.

print strsql
update Investments01_tbl set investigate = 'No' where Investmentl_ID in (SELECT Investmentl_ID from ());

As you can see the from is empty since the form has no recordsource.

The query could be :

update Investments01_tbl set investigate = False where investigate = True

PS - the Yes/No field is numeric internally so I don't use strings to update it.

Thank you, Pat Hartman for your suggestions.

When I use your code in our Access 2003 form, we receive the following error.

"Compile error
Ambiguous name detected. Clear_Yes_Investigate_Click"

Could you please let me know what I have done wrong.

Thank you
Crystal

 

[Uncle Gizmo]

My question though is why does my code work on one form, but not the other.

That was not clear....

 

[access2010]

[B]Uncle Gizmo[/B], On our form = Investments_StockBrowse_F_As_Report =, clearing yes works​

On form = MainMenu, = clearing “Yes” with the changed code causes an error.

Could you please let us know what we have done wrong?

Crystal

 

[Pat Hartman]

When I use your code in our Access 2003 form, we receive the following error.

The code is yours, not mine. It is right out of one of the forms. The point was to show you how to add a variable to make it easy to print it to the debug window so you can see the sql and perhaps determine what the problem is.

Apparently you missed my actual suggestion for a solution - which is - change the query!!!!!

update Investments01_tbl set investigate = False where investigate = True

 

[Uncle Gizmo]

On our form = Investments_StockBrowse_F_As_Report =, clearing yes works​

On form = MainMenu, = clearing “Yes” with the changed code causes an error.

Could you please let us know what we have done wrong?

Still not clear ...

 

[Pat Hartman]

Did you understand my original response???? You ADDED the code I corrected and so duplicated the procedure and that is causing a new error. So I removed the original and just left my version. Which of course still doesn't work and I didn't tell you it would. The change I made to the code made it easy for me to find the error BUT you could still see it if you actually read the message. it says

Syntax error in query expression in
Investment_ID in (Select Invenstment_ID From ())

This is the part of the sql that contains the error. Can you see it yet?

The problem is that the From() does not contain a valid table or query name. WHY? Look back to post #3 for the exact answer. How to fix it? Again - look back to post #3 for the suggested answer. I can only suggest because I don't know exactly what you are trying to do. So the suggested solution - again - is to change the query to:

update Investments01_tbl set investigate = False where investigate = True

There is no need for the sub query that i can determine. The main form is not bound so there is NO RecordSource query you can use in the From() clause. THEREFORE, you CANNOT fix the code. PERIOD. Unless you bind the form. So, do you want to bind the form? or do you just want to reset all the investigate flags to false?

 

[access2010]

Thank you, Pat Hartman for trying to help us, but we can not get the code to work, so we will just live with this problem.

Nicole

 

[Pat Hartman]

Are you sure you want to give up? I told you what the problem was. Look at post #10 again. I would have fixed the form but I haven't been able to get you to tell me WHAT the recordsource for the query should be. Did you try the stripped down query? Does it run? Does it produce what you expect?

update Investments01_tbl set investigate = False where investigate = True

 

[access2010]

Thank you for your suggestions, Pat Hartman.

We have kept on trying to get our button to work and still without succes.

Could you please look at the attached error message and advise us what we have done wrong?

Thank you.
Nicole

 

[access2010]

Did you understand my original response???? You ADDED the code I corrected and so duplicated the procedure and that is causing a new error. So I removed the original and just left my version. Which of course still doesn't work and I didn't tell you it would. The change I made to the code made it easy for me to find the error BUT you could still see it if you actually read the message. it says

Syntax error in query expression in
Investment_ID in (Select Invenstment_ID From ())

This is the part of the sql that contains the error. Can you see it yet?

The problem is that the From() does not contain a valid table or query name. WHY? Look back to post #3 for the exact answer. How to fix it? Again - look back to post #3 for the suggested answer. I can only suggest because I don't know exactly what you are trying to do. So the suggested solution - again - is to change the query to:

update Investments01_tbl set investigate = False where investigate = True

There is no need for the sub query that i can determine. The main form is not bound so there is NO RecordSource query you can use in the From() clause. THEREFORE, you CANNOT fix the code. PERIOD. Unless you bind the form. So, do you want to bind the form? or do you just want to reset all the investigate flags to false?

 

[access2010]

Did you understand my original response???? You ADDED the code I corrected and so duplicated the procedure and that is causing a new error. So I removed the original and just left my version. Which of course still doesn't work and I didn't tell you it would. The change I made to the code made it easy for me to find the error BUT you could still see it if you actually read the message. it says

Syntax error in query expression in
Investment_ID in (Select Invenstment_ID From ())

This is the part of the sql that contains the error. Can you see it yet?

The problem is that the From() does not contain a valid table or query name. WHY? Look back to post #3 for the exact answer. How to fix it? Again - look back to post #3 for the suggested answer. I can only suggest because I don't know exactly what you are trying to do. So the suggested solution - again - is to change the query to:

update Investments01_tbl set investigate = False where investigate = True

There is no need for the sub query that i can determine. The main form is not bound so there is NO RecordSource query you can use in the From() clause. THEREFORE, you CANNOT fix the code. PERIOD. Unless you bind the form. So, do you want to bind the form? or do you just want to reset all the investigate flags to false?

Hello Pat.
Yes, we want to reset all the investigate flags to false,
Crystal

 

[Pat Hartman]

You have EXACTLY the same error I pointed out originally. LOOK at the error message showing you the part of the generated SQL statement that contains the error!!!

Investment_ID In (SELECT Investment_ID from ())

There is NO table name, query name, OR Select statement!!!! The parentheses are EMPTY and I also told you WHY. Your code is trying to fill the variable inside the parentheses with the RecordSource for the form but the RecordSource for the form is EMPTY because the form is not bound. Therefore the SQL is incomplete. I have no idea what records you want to select using this form. I offered a potential solution that selects all records. If you don't want to select all records, WHAT do you want to select??

 

[access2010]

See video number 4 here:-

Lock, Unlock Controls - Nifty Access

Lock, Unlock Controls A problem that crops up from time to time is the need to handle a Group of Controls differently from the rest of the Controls on a Form.Usually this is the need to Lock the Controls so that Data cannot be changed, and conversely Unlock them. However it may be the need

www.niftyaccess.com

The video demonstrates how to replace two command buttons with a single command button.

The same idea could be applied in your case.

Uncle Gizmo thank you for the Link. Nicole