×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

Why can't I suppress warnings after Update Query?

Why can't I suppress warnings after Update Query?

(OP)
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.

RE: Why can't I suppress warnings after Update Query?

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

combo

RE: Why can't I suppress warnings after Update Query?

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

CODE --> vba

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

RE: Why can't I suppress warnings after Update Query?

(OP)
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.

RE: Why can't I suppress warnings after Update Query?

(OP)
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.

RE: Why can't I suppress warnings after Update Query?

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 [Enter Cell Number Digits Only Here] and that's fine is they type 2125551234
But, user can type whatever they want, like 212'345'3212 or 1234567890987654321 or ABCD and your app will crash. sad

---- Andy

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

RE: Why can't I suppress warnings after Update Query?

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

combo

RE: Why can't I suppress warnings after Update Query?

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

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

RE: Why can't I suppress warnings after Update Query?

(OP)
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.

RE: Why can't I suppress warnings after Update Query?

(OP)
I see what you’re saying now combo.

RE: Why can't I suppress warnings after Update Query?

(OP)
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.

RE: Why can't I suppress warnings after Update Query?

(OP)
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.

RE: Why can't I suppress warnings after Update Query?

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.

RE: Why can't I suppress warnings after Update Query?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close