Why can't I suppress warnings after Update Query? 3

[TheresAlwaysAWay]

I am running the following code.

Private Sub LeadSource_DblClick(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE CustomerInfo SET CustomerInfo.Cell = [Enter Cell Number Digits Only Here] WHERE (((CustomerInfo.ID)=[Screen].[ActiveForm]![ID]));"
DoCmd.SetWarnings True
End Sub

I want a box to open to enter a cell phone number and then enter that number into the desired field. Seems simple enough, but every time I run it the update data warning runs, even though it is ostensibly suppressed.

Does anyone have any idea why it won't go away?

As always, thanks in advance to all of you.

 

[combo]

Does it work with SetWarnings=True? Try to combine sql string with Screen.ActiveForm![ID] instead of embedding it directly in sql.

combo

 

[dhookom]

I would never use a parameter prompt in a query. It appears you have a form with the ID. I would add a text box for the cell number. Then remove the

Private Sub LeadSource_DblClick(Cancel As Integer)
    Dim strSQL as String
    strSQL = "UPDATE CustomerInfo SET CustomerInfo.Cell = '" & Me.txtCellNumber & "' WHERE ID= " & me.ID
    DoCmd.SetWarnings False
    debug.Print strSQL
    CurrentDb.Execute strSQL , dbFailOnError
    DoCmd.SetWarnings True
End Sub

You might not even need the changes to SetWarnings.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TheresAlwaysAWay]

Combo, I'm sorry but I haven't had a chance to test yet, but I do not understand your comment.

"Try to combine sql string with Screen.ActiveForm![ID] instead of embedding it directly in sql. "

Can you please elaborate?

Thanks.

 

[TheresAlwaysAWay]

dhookum, as I said, I haven't yet had a chance to test, but I would like to understand more fully your reluctance to use a parameter prompt. It is equivalent to having the same value typed directly into the query.

Can you please explain why this is not a good practice?

BTW, I have a colonoscopy scheduled for tomorrow, so I'm going to be away from my computer until midday and recovering from sedation during the afternoon. I may not be able to fully respond to either of you until Saturday or later, but I would like to understand your views on a parameter prompt. It seems a very easy and efficient way to enter the data directly into the query without having to build a form or other mechanism. If there are negatives I would like to know what they are.

Thanks.

 

[dhookom]

faq701-6763


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I am with Duane.
Give the user a text box where you are in full control what user can and cannot type.

You ask user to [tt][Enter Cell Number Digits Only Here] [/tt] and that's fine is they type [tt]2125551234[/tt]
But, user can type whatever they want, like [tt]212'345'3212[/tt] or [tt]1234567890987654321[/tt] or [tt]ABCD[/tt] and your app will crash. :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

I meant building the SQL string from fixed parts and values taken from controls, in the way Duane did.

combo

 

[TheresAlwaysAWay]

Thank you Duane. That answers my question. And it makes sense.

I will definitely change that is if I get an opportunity.

 

[TheresAlwaysAWay]

Thank you Andy. I always appreciate the knowledge and experience that you guys so freely offer. As I said to Duane I will be changing that.

 

[TheresAlwaysAWay]

I see what you’re saying now combo.

 

[TheresAlwaysAWay]

Odd. I thanked you all in a post and let you know that it's working now and I don't see it posted.

I don't know why the message wouldn't suppress, but it's lucky for me because it's a lot better now than it was.

Thanks again to everyone who helped.

 

[TheresAlwaysAWay]

Just out of curiosity, regardless of the use of parameter prompt, etc., does anyone see anything wrong with the original construction that would stop suppression of update warning? It seems pretty straightforward yet it would not suppress.

 

[xweyer]

This is just a guess but it could be that in Duane's suggestion the textbox would logically already have a value assigned but in your original version the query is run and the user supplies the value later aka after the SQL has already executed. If that is the case the error message is being triggered after you have set the warnings to true.

 

[xweyer]

Lol not a good guess though. I built a simple test db. Used your exact code on a button click event, the field got updated and I never got the warning.