Force user to enter data in a text box 1

[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:

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.

 

[Remou]

Why use the query? If the query is run from a button, you can validate each of the boxes that should be completed and cancel if the form does not validate.

 

[theSizz]

OK here's what I did.

In the property sheet for the text box I entered Is Not Null in the Validation Rule and in the Validation Text I entered Please Enter A Control Number.
When I tab through the form with nothing entered in the txtCtrl box no Validation text comes up.

Am I missing something?

 

[Remou]

I imagine that this is an unbound form, so a validation rule is not suitable. You will need to use a little code. You could, for example, set the focus to the required control and test for null on exit.

 

[theSizz]

Right it's an unbound form.
OK here's what I wrote:

Private Sub DoSearch_Click()
On Error GoTo Err_DoSearch_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_DoSearch_Click:
    Exit Sub

Err_DoSearch_Click:
    MsgBox Err.Description
    Resume Exit_DoSearch_Click
    
End Sub

This works but I would still like to know why the query doesn't work.
Anyone know ?

Thanks for pointing me in the right direction remou

 

[PHV]

I would still like to know why the query doesn't work
The query works as expected, i.e. all parameters are evaluated on open.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886