Force user to enter data in a text box

[theSizz]

I have the following 3 access objects
frmSignOff , rptSignOff, and qrySignOff.

The form FrmSignOff has 2 unbound text boxes and 1 unbound combo box that the user enters search criteria in to populate the query qrySignOff.

I want to force the user to enter a control number in the text box txtCtrl. I thought I could accomplish this by putting this line in the query:

IIf(IsNull([Forms]![frmSignOff]![txtCtrl]),[Please Enter A Control Number],[Forms]![frmSignOff]![txtCtrl])

Here is the complete SQL statement in the qrySignOff

SELECT tblEmployees.FirstName, tblEmployees.LastName, tblEmployees.Dept, tblEmployees.Unit, tblEmployees.Active, tblMemos.CtrlNo, tblMemos.Re
FROM tblEmployees, tblMemos
WHERE (((tblEmployees.Dept)=IIf(IsNull([Forms]![frmSignOff]![cboDept1]),[tblEmployees].[Dept],[Forms]![frmSignOff]![cboDept1])) AND ((tblEmployees.Unit)=IIf(IsNull([Forms]![frmSignOff]![txtLoc]),[tblEmployees].[Unit],[Forms]![frmSignOff]![txtLoc])) AND ((tblEmployees.Active)="yes") AND ((tblMemos.CtrlNo)=IIf(IsNull([Forms]![frmSignOff]![txtCtrl]),[Please Enter Control Number],[Forms]![frmSignOff]![txtCtrl])));

What happens when the report rptSignOff is run and the query executes, a parameter dialog box comes up telling the user ” Please Enter A Control Number ” even if the user enters a value into the unbound text box (txtCtrl) .

In other words it fires whether there is a value in the text box or not.
Obviously there is something wrong with this line:

IIf(IsNull([Forms]![frmSignOff]![txtCtrl]),[Please Enter A Control Number],[Forms]![frmSignOff]![txtCtrl])

Can somebody offer some help ?
Thanks in advance.

 

[boblarson]

You don't put it in the query, you can put it in the form's Before Update event. So, something like this:

If MsgBox("You didn't enter a Control Number.  If you want to continue" & vbCrLf & _
  "entering a record, you must enter a control number." & vbCrLf & _
  "Do you wish to continue with this record?", vbYesNo, "Data Entry Error") = vbYes Then
   ' Cancels the form's update and sets the focus back to the control number text box
   Cancel = True
   Me.txtControl.SetFocus
Else 
   ' Cancels the update and removes the current started record
   Cancel = True
   Me.Undo
End If

 

[theSizz]

Thanks Bob.
This is what I ended up writing.

Private Sub cmdDoSearch_Click()
On Error GoTo Err_cmdDoSearch_Click

    Dim stDocName As String
    
    Select Case Me.PickReport
           
        Case 1
            stDocName = "rptSignOff"
        Case 2
            stDocName = "rptDeptList"
                    
    End Select
    
        If IsNull(Me.txtCtrl.Value) Then
            MsgBox "Please Enter Control Number", vbExclamation + vbOKOnly, "No Control Number Entered"
            txtCtrl.SetFocus
        Else
   
            DoCmd.OpenReport _
            ReportName:=stDocName, _
             View:=Me.OutputMode
        End If
    
Exit_cmdDoSearch_Click:
    Exit Sub

Err_cmdDoSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdDoSearch_Click
    
End Sub

The above code produces the desired results.
Thanks for pointing me in the right direction.