Add code to the Access generated warning message

[FNA access]

In some places I have left the warning messages enabled for the users. If they click cancel they get an error.

How do you add code to this event. I have traced through the program, but I never see the code that actually fires this warning message.

The error is 2501, runSQL action was canceled.

Thanks in advance for the help

[NeoPa]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.

[NeoPa]

I don't know the answer to this but I may well know a man who does ;)
I'll PM him the link if you can wait for his response.

[ADezii]

In some places I have left the warning messages enabled for the users. If they click cancel they get an error.

How do you add code to this event. I have traced through the program, but I never see the code that actually fires this warning message.

The error is 2501, runSQL action was canceled.

Thanks in advance for the help

If I interpret your question correctly, you are referring to the display of System Messages and the subsequent '2501' Error that Access generates when the User decides not to run an Action Query. If this is the case, then the SetWarnings Method of the DoCmd Object is what you are looking for. A simple code block will illustrate this point:
[CODE=vb]Dim MySQL As String
MySQL = "DELETE tblDelete.* FROM tblDelete;"

'Turn OFF the display of the Deletion Prompt. You will receive no warning and
'will not be able to Cancel the Delete Operation once it has been committed.
DoCmd.SetWarnin gs False
'Can be ANY type of Action Query
DoCmd.RunSQL MySQL
DoCmd.SetWarnin gs True 'must manually Reset to be turned ON again[/CODE]

Is this what you are looking for?

[NeoPa]

I thought the OP was asking about customising error messages, but your interpretation could as easily be correct. The question is not clear and it's hard to guess what he's actually asking :(

[ADezii]

I thought the OP was asking about customising error messages, but your interpretation could as easily be correct. The question is not clear and it's hard to guess what he's actually asking :(

I was kind of hazy myself, especially because I knew that you were aware of SetWarnings. How about we just sit back, relax, and wait for a response from the OP?

[NeoPa]

I'm good with that :)

[FNA access]

sorry it has been so long, life has a way of happening....

I will try to be more clear.

For the database settings I have checked both boxes for delete warnings and add warnings.

In most cases I have managed the warnings by turning them off and on with no issues.

When a user wants to create a new object I display the nessecary text boxes to collect all the data to enter into the table.

ie new employee (firstname, last name, type)

The user then clicks a button, if one field is missing I send a msgbox to them telling them to fill all fields. If all fields are filled Access tells them they are about to append a row. If they click no I get the error 2501. If they click yes the data is inserted into the table.

I do want the access message to show.

What I am wondering is how to code the no button click on the Access generated msgbox.

I know I could just disable all warning messages in the database and then create my own and code those button clicks with a Select case msgbox.

I was hoping to not have to add a bunch of these message boxes. I would rather just code the no button on the Access messagebox.

Hopefully this is more clear

Thanks to all (sorry for posting in wrong place)

[Scott Price]

Are you looking for something along the lines of this?:

[CODE=vb]If MsgBox("[YourMessageText ", vbYesNo + vbDefaultButton 1) = vbNo Then
DoSomething[/CODE]

Regards,
Scott

[FNA access]

That seems like code for a custom message box ( I do have several of those)

Does that work for Access generated warning messages

for example when you add a record with warning messages enabled access generates a warning message box that you are about to append one record.
It displays a yes and a no button. I need to code the no button.

When I trace the code :

sSQL = "INSERT INTO Lookup_Airtanker (AirTankerID, Type, IsValid)"
                sSQL = sSQL & "VALUES ('" & DataEntryTextbox.Value & "', '" & FirstNameTextbox.Value & "', true)"
                DoCmd.RunSQL (sSQL)

the Do.cmd.RunSQL(s SQL) line highlights then brings up a message box. If I click yes it leaves the Docmd line and continues through code

if i click no I get the error 2501 (as mentioned above)

If that is the code to handle the access generated message then where do I put it?

[Scott Price]

About your question, I'm not sure about the access warning/error message boxes, whether you can (nor how you can), refer to them in vb code, aside from maybe exploring the On Error Goto Error_Handler. You would then have to trap the error number, and then write a corresponding If...Then statement for when it corresponds to the 2501 message.

Simpler than the above would be to simply write the
[CODE=vb]If MsgBox(YourOrig inalMessageBox) = vbNo Then
DoCmd.SetWarnin gs = False
'Add custom message box that emulates the Access generated one
DoCmd.SetWarnin gs = True
End If[/CODE]

Don't think I can help you any further than this! Sorry...

Regards,
Scott

[ADezii]

sorry it has been so long, life has a way of happening....

I will try to be more clear.

For the database settings I have checked both boxes for delete warnings and add warnings.

In most cases I have managed the warnings by turning them off and on with no issues.

When a user wants to create a new object I display the nessecary text boxes to collect all the data to enter into the table.

ie new employee (firstname, last name, type)

The user then clicks a button, if one field is missing I send a msgbox to them telling them to fill all fields. If all fields are filled Access tells them they are about to append a row. If they click no I get the error 2501. If they click yes the data is inserted into the table.

I do want the access message to show.

What I am wondering is how to code the no button click on the Access generated msgbox.

I know I could just disable all warning messages in the database and then create my own and code those button clicks with a Select case msgbox.

I was hoping to not have to add a bunch of these message boxes. I would rather just code the no button on the Access messagebox.

Hopefully this is more clear

Thanks to all (sorry for posting in wrong place)

What I am wondering is how to code the no button click on the Access generated msgbox

To the best of my knowledge, this is intrinsic to Access and cannot be modified!

[FNA access]

Thank you that answers my question. I guess it is custom message box coding heaven I'll have to delve into.

Thanks for all that tried to help with this, sorry for not being clear off the top.

It does make me feel a little better that it can't be done, as I couldn't figure it out on my own.

[jfkiser]

Would catching the 2501 error help you any? I.e.:

On Error GoTo Err_Label
'
' all your sub's code goes here
'
Err_Label:

If Err.Number = 2501 Then

'
'The Run SQL action was canceled...
'put your code for the 'no' button here
'

End if
MsgBox Err.Description

End Sub

[Scott Price]

Would catching the 2501 error help you any? I.e.:

On Error GoTo Err_Label
'
' all your sub's code goes here
'
Err_Label:

If Err.Number = 2501 Then

'
'The Run SQL action was canceled...
'put your code for the 'no' button here
'

End if
MsgBox Err.Description

End Sub

It probably would indirectly accomplish what you want, although might take some tweaking to get it just right. Have you tried it out yet?

Regards,
Scott