YesNo Msg box does not work. HELP!

[namdam13]

Hi Everyone,

I have a YesNo message box that keeps jumping to the "Else" clause.

I have tried all the elements to this and it all seems to work fine but i just cannot get the message box to work at all. I even tried on a different button just to see wether something else was effecting it. I have chacked the code and it looks correct according to my reference material. What am i doing wrong? The code is pasted below:

Private Sub Create_TWC_BT_Click()

If IsNull(Text48) Then

On Error GoTo Err_Create_TWC_BT_Click

Dim stDocName As String

stDocName = "Create_TWC_MCR"
DoCmd.RunMacro stDocName

Exit_Create_TWC_BT_Click:
Exit Sub

Err_Create_TWC_BT_Click:
MsgBox Err.Description
Resume Exit_Create_TWC_BT_Click



Else


Call TWCExistMessage



'leave alone

End If

End Sub

Sub TWCExistMessage()


Dim MbxResponse As Integer

MbxResponse = MsgBox("TWC alread exist do you wish to update it?", vbYesNo + vbQuestion, "Update TWC")

If MxbReponse = vbYes Then

stDocName = "Update_TWC_MCR"
DoCmd.RunMacro stDocName

'This does not seem to action and it skip to the message box below.

Else

MsgBox ("TWC has not been update. Changes have not been saved.")

End If

End Sub

 

[PHV]

Tip: use the Option Explicit instruction.

If M[!]bx[/!]Reponse = vbYes Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

You've got a spelling mistake in your code:

Dim MbxResponse As Integer

MbxResponse = MsgBox("TWC alread exist do you wish to update it?", vbYesNo + vbQuestion, "Update TWC")

If [red]MbxResponse[/red]= vbYes Then

stDocName = "Update_TWC_MCR"
DoCmd.RunMacro stDocName

'This does not seem to action and it skip to the message box below.

Else

MsgBox ("TWC has not been update. Changes have not been saved.")

End If

End Sub

If you use Option Explicit that will cause an error if you try and use an undefined variable (as in your example).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

*Cough* your's fails with Option Explicit on PHV...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[namdam13]

Thanks for comments guys,

My Typing is terrible.


I corrected it and im still having the same problem what you mean by "Option Explict".

I have only been using access for the last 10 days and am totaly self taught, so am a little out of my depth.

I have spent half a day on this now and it is fustrating me as it seems so simple and the code looks good (Now with no spelling mistakes).

If you point me in the right direction that would be great.

 

[Remou]

@HarleyQuinn Dim stDocName

namdam13
Best repost your code showing the corrections.

http://lessthandot.com

 

[namdam13]

My Current codes is as below. The first bits seem fine but i have included it just in case. On the message box If statement it seems to no be returning the vbYes value. As a test i change the vbYes to 6 as i believe that this was the integer it shoudl return but it did not do anything.

The basic idea was that if the record already existed, then i wanted to make sure the user was happy to update the existing record. Yes would run Macro "Update_TWC_MCR", no would stop luanch a message box with a message saying "no changes made".

Please bare in mind that i have been teaching myself over the last 10 to 15 days and this still all new to me.

Your help would be much appreciated as i need to get the database completed by the end of the next week.



Private Sub Create_TWC_BT_Click()

If IsNull(Text48) Then

On Error GoTo Err_Create_TWC_BT_Click

Dim stDocName As String

stDocName = "Create_TWC_MCR"
DoCmd.RunMacro stDocName

Exit_Create_TWC_BT_Click:
Exit Sub

Err_Create_TWC_BT_Click:
MsgBox Err.Description
Resume Exit_Create_TWC_BT_Click



Else


Call TWCExistMessage





End If

End Sub

Sub TWCExistMessage()

Dim MbxResponse As Integer

MbxResponse = MsgBox("TWC already exist do you wish to update it?", vbYesNo + vbQuestion, "Update TWC")

If MbxReponse = vbYes Then

stDocName = "Update_TWC_MCR"
DoCmd.RunMacro stDocName

Else
MsgBox ("TWC has not been update. Changes have not been saved.")

End If
End Sub

 

[Remou]

Generally, error handling should go at the beginning and end of a sub.

You can use tags to make your code more readable in Tek-Tips:
[ignore]

[/ignore]

You were missing an 's' in mbxresponse. This is where Option Explicit comes in handy (it is 'best practice' to use it) - note the position of the statement.

Option Compare Database
Option Explicit

Private Sub Create_TWC_BT_Click()
Dim stDocName As String

On Error GoTo Err_Create_TWC_BT_Click

If IsNull(Text48) Then

    stDocName = "Create_TWC_MCR"
    DoCmd.RunMacro stDocName

Else
    Call TWCExistMessage
End If

Exit_Create_TWC_BT_Click:
    Exit Sub

Err_Create_TWC_BT_Click:
    MsgBox Err.Description
    Resume Exit_Create_TWC_BT_Click
    
End Sub

Sub TWCExistMessage()
Dim stDocName As String
Dim MbxResponse As Integer

MbxResponse = MsgBox("TWC already exist do you wish to update it?", vbYesNo + vbQuestion, "Update TWC")

If MbxResponse = vbYes Then

    stDocName = "Update_TWC_MCR"
    DoCmd.RunMacro stDocName

Else
    MsgBox ("TWC has not been update. Changes have not been saved.")

End If
End Sub

http://lessthandot.com

 

[HarleyQuinn]

Remou - declared it as a form level variable, declaration is outside the scope of the procedure posted so I left it out (on purpose of course)

namdam13 - You've still got a spelling mistake in the code, have a look at [red]If MbxReponse = vbYes[/red] Then again.

Option Explicit is an instruction you use at the top of your code to tell it that you have to declare a variable and explicitly specify it's type. You would put Option Explicit underneath the line 'Option Compare Database'.

You have to be careful where you do declare your variables so they can be 'sen' by the necessary procedures in your code (although I covered it very convincingly Remou spotted that I'd not declared stDocName in the code I posted, which causes an error when using option explicit, for your code seems you use it in two sub's you'd have to declare it outside of both sub's).

Hope this helps



HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[MajP]

Go to the Visual Basic Environment and select
Tools>Options>Editor
Then check "Require Variable Declaration"

If you do not check this button (and it is stupid that MS ever made this and option) you will run into trouble like this.

Also whenever you are done writing some code select
Debug>Compile

 

[namdam13]

Thank to all of you this has sorted the problem.

Will take on board your coding comments, im trying to follow as many as i can, but its not easy learning as you go.

Anyone any good books, i am Currently using "Access 2003 VBA: With the Smart Method"


Thanks to you all

 

[Remou]

Here is a useful thread with books and other methods of learning VBA:

http://stackoverflow.com/questions/483086/book-recommendation-for-visual-basic

http://lessthandot.com