please help

[RichardFong]

I am REALLY sorry for posting this here, rather than another area, but i am against a deadline and I am desperate for assistance.

I thought i had found the answer to a problem posted about creating a message box where the user has an option to continue, for example 'Are you sure you want to quit' and the user could selct ok or cancel. I was unable to get this to work at all.

I also have a second problem in my booking system, becuase i have a table names 'date_tbl' containing a 'date_id' field, 'date' field, and an 'unavailable' field. The unavailable field is simply a yes/no checkbox, so certain dates can be selected as unavailable. I have created a form where the user can create a new booking, and the form shows the unavailable tickbox so the user can see whether it is avaiable or not. I wanted an error message to appear when the user clicked the command button to save the booking though.

The only information i could find on this topic on the forum is shown below, but i was unsure how apply it to my database.

Basically in the CONDITION of a macro, have it use Dlookup and pull the Qty On Hand left on the item, and if it is less than 5 do a msgbox command.

Forms!Form1![ItemNo] contains the item number for this example

Dlookup("OHQty","ItemTable","ItemNumber = " & Forms!Form1![ItemNo]) < 5

Would be in the Condition, the statment would be MsgBox

Something like this any way.

Thanks a lot for the help
Richard Fong

 

[Mark Wild]

Richard,

try

strMsg = MsgBox("Are you sure you want continue?. ", vbYesNo)

If strMsg = vbNo Then
Exit Sub

End If

 

[RichardFong]

thanks for replying Mark. the code works, but i am unsure how to make a macro run after the yes is clicked. the code so far is:

Private Sub ExitSystemCommandButton_Click()
strMsg = MsgBox("Are you sure you want continue?. ", vbYesNo)

If strMsg = vbNo Then
Exit Sub

End If
End Sub


Im guessing i add a line that says 'If strMsg = vbYes Then', but how do i add the bit which makes a macro run if yes is clicked?

 

[Mile-O]

Private Sub ExitSystemCommandButton_Click() 

   If MsgBox("Are you sure you want continue?. ", vbQuestion + vbYesNo, "Continue?") = vbYes Then
       DoCmd.RunMacro "Your MacroName"
   End If

End Sub

 

[RichardFong]

Ive just done it, i used:

Private Sub ExitSystemCommandButton_Click()
strMsg = MsgBox("Are you sure you want continue?. ", vbYesNo)

If strMsg = vbYes Then DoCmd.RunMacro "exitsystem_macro"
If strMsg = vbNo Then
Exit Sub

End If
End Sub

thanks a lot for the help, do u know how to solve the first problem about the unavailable dates?

I have a table named 'date_tbl' containing a 'date_id' field, 'date' field, and an 'unavailable' field. The unavailable field is simply a yes/no checkbox, so certain dates can be selected as unavailable. I have created a form where the user can create a new booking, and the form shows the unavailable tickbox so the user can see whether it is avaiable or not. I wanted an error message to appear when the user clicked the command button to save the booking, the user cannot save it. So if the unavalaible box is not ticked, the saverecord_macro needs to run and if the unavalable box is ticked, the error should be shown.

thanks again

 

[Mile-O]

Tidy that code as the response to a Yes/No message box can only be one of two options there's no need to test for both if you are only performing an operation that relies on one option rather than both.

Also, you say you have that working but you use a variable called strMsg which isn't dimensioned which leads me to believe you don't have Option Explicit set at the top of your module - and believe me, it's a better practice to use this method as it will highlight errors regarding references to variables, controls, and other objects.

However, as you are only testing for a one-off response to the message box there is no need to store its result in a variable therefore you can use the If MsgBox line.

Essentially, all that code you have written can be simplified to:

Private Sub ExitSystemCommandButton_Click() 

    If MsgBox("Are you sure you want continue?. ", vbYesNo)  = vbYes Then DoCmd.RunMacro "exitsystem_macro" 

End Sub

 

[RichardFong]

excellent, even better, thanks Mile-O-Phile. do you know how to solve the second problem mentioned above? thanks a lot. sorry to be so demanding, just got to get this system finished very quickly

 

[Mile-O]

I don't know what you've called your controls so I'll assume:

Command button = cmdButton
CheckBox = chkCheck

Private Sub cmdButton_Click()

    If Me.chkCheck = True Then
        MsgBox "That date is not available.", vbInformation, "Unable to save record"
    Else
        DoCmd.RunMacro "saverecord_macro"
    End If

End Sub

 

[RichardFong]

Mile-O-Phile you are a God, thank you very very very very much, I can stop stressing now. I apologise for posting twice. Thanks again, excellent.

 

[Mile-O]

No probs!