Error check in queries

[devesa]

Hello,

In one MS Access query I am doing one update operation.



UPDATE myTable SET Phone = "983313" WHERE Id = 3;

Attribute "Name" for Id=3 should be always "Peter", unless an error has ocurred. Then, I would like to check if Name="Peter" and:
- IF Name="Peter" WHERE Id=3 ==> THEN do the update operation.
- ELSE ==> THEN show an alert message "Something went wrong"

How can I do it? THANK YOU!!

[Datagopherdan]

I don't understand what you mean by ID 3 should always be "Peter". Do you have another table where you have a unique id associated to a name or is it a matter where you have something like a Product A is ID 3 and only Peter can buy Product A?

[devesa]

Sorry, I didn't explain it very good.

I have a table myTable with these fields:
- Id (primary key)
- Name (varchar)
- Phone (integer)

Then, in one MS Access query I am doing this update operation:
UPDATE myTable SET Phone = "983313" WHERE Id = 3;

For Id=3 field "Name" should be always "Peter", unless an error has ocurred. Then, I would like to check if Name="Peter" and then:
- IF Name="Peter" WHERE Id=3 ==> THEN do the update operation.
- ELSE ==> THEN show an alert message "Something went wrong"

I could do it this way:
UPDATE myTable SET Phone = "983313" WHERE Id = 3 AND Name="Peter";

But I do not, because I want to SHOW an error message. Not just avoiding the update operation.

THANK YOU

[Datagopherdan]

It sounds like you don't have your database normalized. You should only have one line for "Peter". If you could upload your database, I can take a look.

[devesa]

Thank you very much. Any way the question is independent of my database:

"How can I show an error message (tipical "alert" window) in a MS Access query?"

[maximus]

This is what I have done tried to replicate the conditions. I have a table exp with the Following Field:

EntryID
ID
P_Name
Tele

Now I have used a Form Command Button to open the Recordset Exp. Loop through all the Records and When Condition Name="Peter" and ID=3 the Tele is updated to "983313".

The code is as below:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim strSQL As String
Dim strMessage As String
Dim rs As Object

strSQL = "Select * From exp"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF And rs.BOF Then
MsgBox "No Record"
End If
Do While Not rs.EOF
If rs!ID = 3 And rs!P_Name <> "Peter" Then
strMessage = MsgBox("Check the Entry with the ID=" & " " & rs!EntryID)
Else
rs.Edit
rs!Tele = "983313"
rs.Update
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

This code will Modify the Tele Field where Name="Peter" and ID=3. And when Condition is Not met you will be prompted the EntryID which is a Autonumber Field so that you can check the Entry for errors. You have to not down the Numbers when they are prompted. If you want to break the loop the you can put an Exit Do before else.

attaching a mdb file for you reference.

the startup form Form1 will open click button to run query. see the table exp first and then after running the query to see the effect.

[devesa]

EXCELENT! That is exactly what I was looking for. Thank you very much for your help!!

Regards,

Devesa

[maximus]

mark the thread solved please glad to help

[devesa]

How can I mark the thread as solved? I cannot find the option (first post in this forum)

Thanks!

[Datagopherdan]

Glad that you got it solved but you may be taking the wrong path, which is going to lead to problems down the road if your database is not normalized. Why do you need to have it setup like that?

Maximus,

Nice code. Just a few suggestions, if you don't mind.

If rs!ID = 3 And rs!P_Name <> "Peter" Then
strMessage = MsgBox("Check the Entry with the ID=" & " " & rs!EntryID)
Exit Sub

Should have it exit here, so the correction should be made or better yet. Would recommend having it first look for errors and if any are found then a separate query would be ran that would append them to another table, so they can be viewed and corrected. Once the corrections are made then the function can be ran again, so everything is updated properly.

Again though, it sounds like your database isn't normalized and that's critical because it's going to cause you all kinds of headaches down the road if it's not.

How can I mark the thread as solved? I cannot find the option (first post in this forum)

Thanks!

[maximus]

go to the thread tools at the top and mark the thread solved.

[maximus]

Sir,

I think you didnot go through my post carefully I had suggested an Exit do if the condition is not met.

Well Sir the problem was how to display a prompt to an user while running an update query. I completely agree to the set up that you have mentioned:

"Should have it exit here, so the correction should be made or better yet. Would recommend having it first look for errors and if any are found then a separate query would be ran that would append them to another table, so they can be viewed and corrected. Once the corrections are made then the function can be ran again, so everything is updated properly."

but then again its was not the question. In this sample when ever the condition is not met the user is prompted an EntryNumber which is unique which will help the user to correct the entries later.

It will be great fun running an alternative queries when conditions is not met may be we will get to do it sometimes later

Thanks for the suggestion. Always look forward to them.

[Datagopherdan]

You're right, you do have that in there. I only looked at the code not your posting.

I know the poster asked for a prompt each time a condition isn't met but that can become very tedious if you end up having like 50 errors and you're having to press "Ok" 50 times. It's always a good practice to check for errors first and have them corrected, so a full correct procedure is being ran instead of a procedure that is only grabbing correct information when you actually need all of it. By first identifying the errors and having them weeded out, so you can print them out or even use them in a query to automatically correct the errors, ensures that all the needed data is being accounted for.

I still think that he/she does not have their database normalized and this is just an unnecesary step that will lead to problems in the future. Just my opinion thogh, what do I know.

Sir,

I think you didnot go through my post carefully I had suggested an Exit do if the condition is not met.

Well Sir the problem was how to display a prompt to an user while running an update query. I completely agree to the set up that you have mentioned:

"Should have it exit here, so the correction should be made or better yet. Would recommend having it first look for errors and if any are found then a separate query would be ran that would append them to another table, so they can be viewed and corrected. Once the corrections are made then the function can be ran again, so everything is updated properly."

but then again its was not the question. In this sample when ever the condition is not met the user is prompted an EntryNumber which is unique which will help the user to correct the entries later.

It will be great fun running an alternative queries when conditions is not met may be we will get to do it sometimes later

Thanks for the suggestion. Always look forward to them.

[maximus]

sure sir,

I tend to diagnose each problem as it is, cause its hard to fully understand the true nature of a problem from only a paragraph that you get in a post. explaining a problem is an art in itself. You have a great skill of comprehending problems and a novice like me can learn a lot from you.

[maximus]

If your problem is solved please mark the thread solved. To lear how to do it follow the link given below.

https://www.accessforums.net/forum-s...lved-1828.html