Trying to Change Yes to No (1 Viewer)

[access2010]

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 has been updated?

Your suggestions will be appreciated.

Crystal

 

[plog]

Something's wrong with your SQL but you are only looking at VBA. You need to dig into the SQL to find out what it says and why is it wrong.

You do that by not directly running your SQL with a CurrentDb.Execute but putting your SQL in a string and then when things go sideways looking at exactly what is in that SQL string. So do that, assign that SQL string to an actual variable and then either Debug.Print that string or MsgBox it out so you can see with your own eyes what exactly is in that SQL string you think you are building.

 

[June7]

What does "not co-operate" mean - error message, wrong result, nothing happens?
What steps do we take to replicate issue?

 

[access2010]

What does "not co-operate" mean - error message, wrong result, nothing happens?
What steps do we take to replicate issue?

Thank you, June 7 for your reply and question.

On the forms, "Yes_1_Investments_StockBrowse_F_High" and "Yes_2_Investments_StockBrowse_F_Update", when you click the "Clear_Yes" buttons, the "YES" in the Table field "Investigate" becomes "NO"

While, I have tried using the same code on the form "MainMenu" and when the button "Clear_Yes_3" is clicked we get an error message.

Could you please offer us a suggestion on how to correct this error?

Nicole

 

[Pat Hartman]

It's pretty easy to figure out the problem when you use good debugging techniques. When you are building strings in VBA, especially SQL strings. Plog's suggestion is what I always use, so:

Private Sub Clear_Yes_1_Click()
' File Name ===> Clear_Yes_1
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

Now, when you print the string to the debug window, this is what you get. The first line is the one from Main which doesn't work and the second is the one from another form which does work.
update Investments01_tbl set investigate = 'No' where Investmentl_ID in (SELECT Investmentl_ID from ());
update Investments01_tbl set investigate = 'No' where Investmentl_ID in (SELECT Investmentl_ID from (InvestmentsGrouping_Q));

So, seeing that, all I did was look at the record source in the Main form and there is none - so that is the answer.

 

[access2010]

It's pretty easy to figure out the problem when you use good debugging techniques. When you are building strings in VBA, especially SQL strings. Plog's suggestion is what I always use, so:

Private Sub Clear_Yes_1_Click()
' File Name ===> Clear_Yes_1
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

Now, when you print the string to the debug window, this is what you get. The first line is the one from Main which doesn't work and the second is the one from another form which does work.
update Investments01_tbl set investigate = 'No' where Investmentl_ID in (SELECT Investmentl_ID from ());
update Investments01_tbl set investigate = 'No' where Investmentl_ID in (SELECT Investmentl_ID from (InvestmentsGrouping_Q));

So, seeing that, all I did was look at the record source in the Main form and there is none - so that is the answer.

2023_07_Jul08
Thank you Pat Hartman for your note.
Can you please look at the main menu form and advise me why; the function at “Clear_Yes_3” still does not change the “Yes” to “No” in the table and an error message occurs after changing the code to your suggestion?

The other 2 Yes to No’s work.

Thank you.
Nicole

 

[Pat Hartman]

Please reread my explanation and think about what it says. Don't forget to read the last sentence where I tell you what the problem is. Do you see how the two SQL statements are different? Do you see that one is missing something? WHAT is it missing? It is missing a recordsource! WHY? Because the RecordSource for the main form is BLANK. THEREFORE, you need to add a table or a query as the recordsource.

I'm having DeJa Vu. I'm sure we've had this conversation before.

 

[access2010]

2023_Jul13, we thank you for your suggested code.
The three volunteers in our office I think have followed your instructions but with no results.
Please look at the attached database and tell us what we have done wrong?

Yes_1 and Yes_2 works as originally sent in, BUT we can not get, Yes_3 on the main menu to work.
We would appreciate your comments as to what we are doing wrong.

Thank you.
Nicole

 

[June7]

You have an explanation. Yes_1 and Yes_2 forms each have RecordSource. Main_Menu does not. The code references form's RecordSource property but, again, Main_Menu does not have data - it is UNBOUND.

 

[access2010]

Thank you June7, plog and Pat Hartman for trying to help us, but we have had no success in this project and will just give up.

Nicole

 

[Uncle Gizmo]

Thank you June7, plog and Pat Hartman for trying to help us, but we have had no success in this project and will just give up.

Nicole

Or you could employ one of the excellent developers on this website to fix your problem for you. I'm reasonably sure Pat Hartman would help you out, I'm not sure if June7 is up for doing little jobs. Edit:- or plog....

 

[June7]

What exactly do you not understand about the explanation? Since MainMenu does not have data and code requires form's dataset in the nested subquery, what would you want to happen, considering this lack of dataset? Do you want to set ALL records to No?

 

[The_Doc_Man]

@access2010 - It appears that the explanation is not getting through. I'll try to say it a different way.

When you have a form intended to affect data in a recordset, this works only if the form is bound to the recordset. To tell if you have a bound form, you open the form in design mode and look at the Data properties, one of which will be "Record Source" and it should contain something. It should not be blank, but it is. When you execute the code that contains this fragment (see post #5 by Pat Hartman in this thread),

Replace$(Me.RecordSource, ";", "")

for the main form, Me.RecordSource is blank - so you get a blank return value. This is your problem. Getting a blank (or zero-length string) back from the Me.RecordSource reference means the main form is NOT BOUND to a record. It does not have data associated with it.

Your problem occurs when the query tries to gather data from something that is not defined, so it fails. THAT is why you have the failure for the main. But if you checked for the two sub-forms, they have non-blank .RecordSource properties, so the same "Replace" that fails for the Main works for the two subs.

 

[Pat Hartman]

Thank you June7, plog and Pat Hartman for trying to help us, but we have had no success in this project and will just give up.

I would have created the recordsource for you but I have no idea what it should be. All I can tell you is that it is missing and that is why the third form does not work.

Don't give up because you don't understand. Do you not understand that the RecordSource for the main form is blank and that is what is causing the problem? You seem to have made a new form because you want to show some different set of records, You will need to create a query that selects the data that you want to see on the third form and place either the string or the querydef name into the RecordSource property of the main form.