Help with Opening Query

[vernthemerciless]

Hi

I'm a newbie to VBA and i'm trying to get a msg box to pop up when you open a form in my database asking if you want to generate this weeks work, which is held in a query. The msgbox pops up ok but when you click enter it just does nothing. Can someone help me please? Here is the code...

Private Sub Form_Open(Cancel As Integer)
MsgBox "Is it Monday today? If so do you need to get this week's list of follow-up work?", vbYesNo, "Good Morning Paul and Clem"
If Response = vbYes Then
DoCmd.OpenQuery "THIS_WEEK"
Else
End Sub
End If
End Sub

Thanks

V

 

[georgedwilkinson]

You need to capture the response before you use it:

Private Sub Form_Open(Cancel As Integer)
Dim Response as Integer
Response = MsgBox "Is it Monday today? If so do you need to get this week's list of follow-up work?", vbYesNo, "Good Morning Paul and Clem"
If Response = vbYes Then
DoCmd.OpenQuery "THIS_WEEK"
Else
End Sub
End If
End Sub

 

[georgedwilkinson]

Oh, I just noticed you have 2 End Sub(s). You need to change the first to "Exit Sub":

Private Sub Form_Open(Cancel As Integer)
Dim Response as Integer
Response = MsgBox "Is it Monday today? If so do you need to get this week's list of follow-up work?", vbYesNo, "Good Morning Paul and Clem"
If Response = vbYes Then
DoCmd.OpenQuery "THIS_WEEK"
Else
Exit Sub
End If
End Sub

But you don't really need the else at all (unless you plan on enhancing the sub).

 

[vernthemerciless]

Thanks George

I tried this but it's still running the macro when i click "no" as well as when i click "yes". How do i stop this? Do i need to add another if for when the response is "no"?

 

[gemma-the-husky]

just an aside

if you only need to do this on a monday just check whether the date IS a monday.
the date function returns todays date - the weekday function returns a value of 1 to 7 to indicate which day it is, but you can use constants vbmonday, vbtuesday etc, rather than trying to workout which actual value is being returned in the range of 1 to 7

so

if weekday(date) = vbmonday then
ask your question

--------
the other thing you could also do is set some sort of flag to indicate the the weeks data HAS been collected, so again you wouldn't need to offer the option unnecessarily, or you could remind users they have already collected the data, so they dont need to do it again

this sort of user-friendly feature is a great help in getting your users acceptance of new systems

 

[georgedwilkinson]

Thanks George

I tried this but it's still running the macro when i click "no" as well as when i click "yes". How do i stop this? Do i need to add another if for when the response is "no"?

Fascinating. Show us the exact code you're using.

Gemma's advice is good, if it works for you. But if you're just trying to figure out how to respond to MsgBox and are having problems, post all of your exact code here with a description of the problem.

 

[gemma-the-husky]

out of interest there appears to be something wrong in your statement

the line

Response = MsgBox "Is it Monday today? If so do you need to get this week's list of follow-up work?", vbYesNo, "Good Morning Paul and Clem"

is showing a syntax error for me, without brackets round the parameters for msgbox.

------
putting brackets in makes it work correctly