MsgBox within a MsgBox

[Ian]

Before closing a form I need the user to answer two questions but I I get an
error when I use two Dim Response As String within the same bit of VBA

Do I just need to change the name Response????

'first question
Dim Response As String
ConfirmResponse = MsgBox "Create New Chart?", 4
If ConfirmResponse = 6 Then
DoCmd.OpenQuery UpdateQuery1
'Second Question
Dim Response As String
ConfirmResponse = MsgBox "Update Admit Field?", 4
If ConfirmResponse = 6 Then
DoCmd.OpenQuery UpdateQuery2
Else...
Else....
End If
End If

This VBA is for a button with four possible outcomes (if a field is null
then #1, if it is not null either #2 or #3). The "Update Admit Field"
question applies to them all.

Thanks Ian.

 

[tina]

why are you dimming a variable called Response at all? you're not using it
in the code, at least not in the code you posted. also, i don't see that you
dimmed the variable ConfirmResponse, unless you did it at the module level.
if not, then i'd say you don't have the Option Explicit statement at the top
of your module, and you should. type it directly under Option Compare
Database, as

Option Compare Database
Option Explicit

to set Access to automatically add Option Explicit each time a module is
created, open any module and from the menu select Tools | Options. on the
Editor tab, checkmark the Require Variable Declaration option. that will NOT
add the statement to existing modules; you should do that manually, then
compile your code and declare any variables that the compile highlights.

hth

 

[Albert D. Kallal]

When you create a variable by using "dim some var" you are defining a spot
in
the computers memory to store a value. Thus, you don't (and cannot) define
the same variable more then once in the same code routine. These variables
are
exactly like the idea of a memory button on a desktop calculator.

However, you really don't even need any variable here, I would suggest the
folwling

If MsgBox("Create New Chart?", vbYesNo) = vbYes Then

DoCmd.OpenQuery UpdateQuery1

End If

If MsgBox("Update Admit Field?", vbYesNo) = vbYes Then

DoCmd.OpenQuery UpdateQuery2

End If


As you can see, not only does the above eliminate the variables, it also
"reads" much like what the code is actually doing.

 

[Ian]

Thanks -- that simplifies things considerably -- as I'm sure you can tell I
have no formal training with VBA; just what I've learned form the books.
This code helps a lot.