message box won't work in On_Click event of form control...

[dgtampa]

Hello,



Some info: I am using AC2010. Since I am not proficient in VBA, I tend to use the MacroBuilder.

I have an unbound form named frmStart which calls forms or reports (via event-based macros) conditionally through the user's selection(s) from 2 controls: a combobox named cboTaskChoose (which has a value list of 6 different choices) and a textbox called cboDatePick (I know...it's a "textbox" which has "cbo" as a prefix in the name...just stay with me). After the user selects/inputs values in the two controls, they click on a button control called btnOpenSchedule which runs the macro to call the corresponding form or report.

If the user does not enter a value/make a selection in cboTaskChoose I would like to call a message box to direct them to make a selection. I have tried to make this work with several different macro configurations, to no avail; if selections are made in the form controls and btnOpenSchedule is clicked, the form calls the appropriate form or report. However, nothing happens if I leave everything blank and click on btnOpenSchedule (nothing, meaning no message box, no error message; nothing). Currently, I have created the following macro in the On_Click event of btnOpenSchedule:

If:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/...InterfaceMacro For="btnOpenSchedule" Event="OnClick"><Statements><ConditionalBlock><If><Condition>IsEmpty([cboTaskChoose])</Condition><Statements><Action Name="RunMacro"><Argument Name="MacroName">mcroSelectTask</Argument></Action><Action Name="GoToControl"><Argument Name="ControlName">cboTaskChoose</Argument></Action></Statements></If></ConditionalBlock></Statements></UserInterfaceMacro></UserInterfaceMacros>

Else:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/...InterfaceMacro For="btnOpenSchedule" Event="OnClick"><Statements><ConditionalBlock><If><Condition></Condition><Statements><Action Name="RunMacro"><Argument Name="MacroName">FormORReport</Argument></Action></Statements></If></ConditionalBlock></Statements></UserInterfaceMacro></UserInterfaceMacros>

The "If" part of my On_Click macro contains a call for the message box macro called mcroSelectTask, as follows:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/...InterfaceMacro MinimumClientDesignVersion="14.0.0000.0000"><Statements><Action Name="MessageBox"><Argument Name="Message">Please choose a task...</Argument><Argument Name="Title">I'm not a mind-reader!</Argument></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

Here is a picture of the form (in Design View), in case it's needed (the form is very simple).



I'm sure I'm missing something obvious. Please let me know if you need more info in order to help me. Any/all help is appreciated.

[pbaldy]

I don't use macros, but I think you'd need a condition. The VBA would look a little like the test here:

http://www.baldyweb.com/BeforeUpdate.htm

[dgtampa]

Thank you for your prompt reply Paul! I was able to try out a couple of things yesterday, based on your suggestion. I tried putting my macro (AC2010's macro builder seems to me to work a lot like VBA -in terms of its structure) into the Before Update event of the form as well as in the Before Update event of both controls (cboTaskChoose and btnOpenSchedule), but I have the same result as before.
Paul, you also said that you think a condition would be needed; the macro I made uses the function "IsEmpty" to evaluate whether or not the user has made a selection from the control cboTaskChoose. Is that what you meant by a condition?
Since you said that you don't use macros, I have converted examples of my macros, converted to Visual Basic (thought it would help):

Option Compare Database
'------------------------------------------------------------
' btnOpenSchedule
'
'------------------------------------------------------------
Function btnOpenSchedule()
    If (IsEmpty(Forms!frmStart!cboTaskChoose)) Then
        Beep
        MsgBox "You need to choose a task...", vbInformation, "I'm not a mind reader!"
        DoCmd.CancelEvent
        DoCmd.GoToControl "Forms!frmStart!cboTaskChoose"
    Else
        DoCmd.RunMacro "FormORReport", , ""
    End If
End Function

Option Compare Database
'------------------------------------------------------------
' FormORReport
'
'------------------------------------------------------------
Function FormORReport()
    If (Forms!frmStart!cboTaskChoose = "Make A Schedule") Then
        DoCmd.OpenForm "frmSchedule", acNormal, "", "", , acNormal
    ElseIf (Forms!frmStart!cboTaskChoose = "Level Review Report") Then
        DoCmd.OpenReport "rptLevel_Reviews", acViewReport, "", "", acNormal
    ElseIf (Forms!frmStart!cboTaskChoose = "Update Levels") Then
        DoCmd.OpenForm "frmLevel_Review", acNormal, "", "", acEdit, acNormal
    ElseIf (Forms!frmStart!cboTaskChoose = "Add or Delete Beneficiaries") Then
        DoCmd.OpenForm "frmNew_Beneficiaries", acNormal, "", "", acEdit, acNormal
    ElseIf (Forms!frmStart!cboTaskChoose = "View/Print Caseload") Then
        DoCmd.OpenReport "rptCaseload", acViewReport, "", "", acNormal
    ElseIf (Forms!frmStart!cboTaskChoose = "View/Print Groups") Then
        DoCmd.OpenReport "rptBeneficiary_Group_Lists", acViewReport, "", "", acNormal
    End If
End Function

I hope this helps you help me! :-)

[pbaldy]

You wouldn't use IsEmpty, you'd use a test like I showed. It tests for Null and a zero length string. Here's more info:

http://www.baldyweb.com/NullEmptyEtc.htm

I personally would only use macros if I was creating a web database, as they can't contain VBA.

[dgtampa]

Hello again,

Thank you for helping me to learn about this (or at least, try to help me ).

I have tried to use this code in the Before Update property of both my form and the control itself, based on the test code you pointed me to:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.cboTaskChoose & vbNullString) = 0 Then
  MsgBox "You need to choose a task"
  Cancel = True
  Me.cboTaskChoose.SetFocus
End If
End Sub

I still did not get a message box after leaving the field blank and clicking on the button control btnOpenSchedule. I know I'm missing something!

I may have taken you too literally in using/modifying the example code you linked me to, but I don't know enough yet to really ad-lib in code. Or, perhaps I'm putting it in the wrong place? Thank you again for your patience and helping me learn...

[John_G]

Hi -
I think you have the Null test in the wrong event. Try putting your no-selection check in the on_click event of the command button. If no selection has been made, give the error message, and use Exit Sub to exit the procedure.


Private Sub btnOpenSchedule_Click
If Len(Me.cboTaskChoose & vbNullString) = 0 Then
MsgBox "You need to choose a task"
Me.cboTaskChoose.SetFocus
exit sub
End If
'
' Selection is OK - continue
'
End Sub


I cannot be sure, but I suspect that in an unbound form, the before update event will never occur, because there is no record to update.

John

[pbaldy]

I missed the fact that it was an unbound form. As John mentioned, behind your save button would be the appropriate place.

[dgtampa]

Hello there folks!

I have been indisposed for the past couple of days and have not been able to keep working on my database. However, I thank you all for your suggestions thus far.

Paul and John; in my first post I stated that I had tried putting a macro behind the button control in my form; seeing that John said he prefers VBA to macros, I have just tried placing the following code in the On Click property of the button control btnStartSchedule in the form:

Private Sub btnOpenSchedule_Click()
If (IsEmpty(Forms!frmStart!cboTaskChoose)) Then
        Beep
        MsgBox "You need to choose a task...", vbInformation, "I'm not a mind reader!"
        DoCmd.CancelEvent
        DoCmd.GoToControl "Forms!frmStart!cboTaskChoose"
    Else
        DoCmd.RunMacro "FormORReport", , ""
    End If
End Sub

However, I still get no response from the database if I don't put in a value. I have uploaded a "redacted" version of the database where the names of people have been changed "to protect the innocent" if it helps at all...

I would also like the textbox control cboDatePick to be validated in the same way; I thought it would be easier than the other, but I can get neither one nor the other to work now...

TestSeniority and Review.zip

[pbaldy]

As mentioned earlier:

If Len(Forms!frmStart!cboTaskChoose & vbNullString) = 0 Then

[John_G]

Hi -

The IsEmpty() function is not the right one to use - is is always False for a form control - use IsNull() instead.
You don't need docmd.CancelEvent - it does nothing because the On_Click event does not have a Cancel argument. Use Exit Sub to leave the procedure if necessary.

Also, I notice that your sub is called btnOpenSchedule_Click, but you state that it is for btnStartSchedule; that could have a lot to do with your problem!

John

[dgtampa]

Thank you Paul and John for your help. Paul...Yes, I am a little hard-headed.

Your suggestions/clarifications got the message box working, but now I get a macro error:
"Run-time error '2109':
There is no field named 'Forms!frmStart!cboTaskChoose' in the current record."

Wouldn't that have been cancelled based on the VBA coding? I tried also adding Exit Sub before the End Sub, but the error still pops up. How might I cancel that? Would I use

DoCmd.ClearMacroError?

By the way; Paul, why do you call yourself the "wino moderator?" To me that seems like a contradiction in terms... (hmm...thinking about drinking wine now...)

[pbaldy]

Try this (I assume this is the line throwing the error):

DoCmd.GoToControl Me.cboTaskChoose

I'm a moderator, I'm a wino; "wino moderator" seemed like a logical name!

[dgtampa]

Try this (I assume this is the line throwing the error):

DoCmd.GoToControl Me.cboTaskChoose

I'm a moderator, I'm a wino; "wino moderator" seemed like a logical name!

Paul, you were correct in your assumption that it was the GoToControl command that was throwing the error.

Your suggestion took care of the last error, but now I have a new error:

Run-time error '2498':

An expression you entered is the wrong data type for one of the arguments.]

The debugger highlights the new line of code (DoCmd.GoToControl Me.cboTaskChoose)

I did some research on this error, but I don't understand a lot of what is being suggested as a solution (forced conversion in code, etc.). Don't know why there would be a mismatch in data types now when all we changed was a command...

[pbaldy]

Try this instead:

Me.cboTaskChoose.SetFocus

[John_G]

The control name has to be a string containing the control name:

DoCmd.GoToControl "cboTaskChoose"

John