Creating a Message box if user doesn't fill in mandatory field

[Triacona]

Dear All,

Any help would really be greatly appreciated
I have the following problem:

I have a form - (MainScreen)

This form has several tabs, each relating to a specific selection of tables.

On one of my tabs - (Residential Premises)
I have a list box which lists reports

"RP Report Via Open Cases";"RP Report on Type for Open Cases";"RP Report Open Cases Via Officer";"RP Report Case Via Officer";"RP Report Via Officer and Action Date";"RP Hazards Sorted via Rating";"RP Hazards Sorted Via Officer"

Then on the right hand side of this list there are fields to be filled in:

Screen Name : Form Name

Start Date : RpStartDate (Date chooser ActiveX)
End Date : RpEndDate (Date chooser ActiveX)
Officer : RpCmbOfficer (Combo box)
Type : RpCmbTyp (Combo Box)

I have made the controls for certain fields disappear when a specific report is selected.

I now want a message box to appear, if they have not entered a mandatory field, i.e. SrStartDate

I have the following code on my generate report button:

Private Sub Rp_Report_Click()
On Error GoTo Err_Rp_Report_Click

    Dim stDocName As String
    'Dim Description As String
    'Description = "Please Enter Dates"
    
        stDocName = ListRP
        Dim ErrMsgED As String
        Dim ErrMsgND As String
        ErrMsgND = "There is no Date for This Report"
        ErrMsgED = "Please Enter Date"
        
        Select Case ListRP.ItemData(ListRP.ListIndex)
        
        Case "RP Report Via Open Cases", "RP Report on Type for Open Cases", "RP Report Open Cases Via Officer"
            If SrStartDate Is Not Null Then
               MsgBox (ErrMsgND)
            End If
        Case "RP Report Case Via Officer", "RP Report Via Officer and Action Date", "RP Hazards Sorted via Rating", "RP Hazards Sorted Via Officer"
        If SrStartDate Is Null Then
               MsgBox (ErrMsgED)
            End If
        End Select
        
        DoCmd.OpenReport stDocName, acPreview
        

Exit_Rp_Report_Click:
    Exit Sub

Err_Rp_Report_Click:
    MsgBox Err.Description
    Resume Exit_Rp_Report_Click
    
End Sub

It gives me an error when I don't enter the date but doesn't give me the message box I want, instead it gives me an error message:

Object required

Where am I going wrong?

Please help, it will be much appreciated.

Kind regards
Triacona

 

[kjv1611]

1. Set the form controls' property for required to Yes/True.
2. Then in your button, then if you have more than one field you want to check, you could just loop through the controls, checking for type of control, whether the control is 'required', and then if it is required, whether it has anything in it.. Or if you know they are all one type, then it'll be a little easier...

Private Sub Button_Click()
  Dim ctl as Control
  For Each ctl in Form.Controls
    If Typeof Control is TextBox Then
      If IsNull(ctl.Text) Then
        MsgBox "Hey, bozo, you didn't fill in the " & ctl.Name & " value!",vbCritical,"Hey, Wait a minute!"
        ctl.SetFocus
        GoTo ExitSub
      End If
     End If
   Next ctl

ExitSub:
On Error Resume Next
  Set ctl = Nothing
  Exit Sub

ErrHandle:
  'Error Handling here
  Resume ExitSub

End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[Triacona]

Thank you very much for your reply and help, Kjv

I still having the same problem...
I have implemented your code as below...but it seems to be ignoring it...
I am relatively new at VB coding, am I not telling it which objects to check?
I.e. RpStartDate and RpEndDate?
I stuck at the mo.. I have tried a few variations and they seem to give me various errors...

Private Sub Rp_Report_Click()
On Error GoTo Err_Rp_Report_Click

    Dim stDocName As String
    Dim text As Control
    Set text = RpStartDate
        
        stDocName = ListRP
        Dim ctl As Control
            
              For Each ctl In Form.Controls 'begin for
            
                If TypeOf Control Is TextBox Then
                
                    If IsNull(ctl.text) Then
                    
                        msgbox "Please fill in the dates " & ctl.NAME & " value!", vbCritical, "Dates not filled in!"
                        
                        ctl.SetFocus
                        
                        GoTo ExitSub
                        
                    End If 'If IsNull(ctl.Text) Then
                    
                End If 'If TypeOf Control Is TextBox Then
                
            Next ctl 'end for   

        DoCmd.OpenReport stDocName, acPreview
        

Exit_Rp_Report_Click:
ExitSub:
On Error Resume Next
  Set ctl = Nothing
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
    
End Sub

I would appreciate any further help
Kind regards
Triacona

 

[dhookom]

In Access, you rarely if ever use the Text property of a control. You would need to place the focus on the control to get the Text property. Instead, use either the Value property or nothing since the Value is the default property of bound controls. I also changed Form.Controls to Me.Controls and removed a line.

Private Sub Rp_Report_Click()
On Error GoTo Err_Rp_Report_Click
    Dim stDocName As String
    Dim text As Control
    Set text = RpStartDate
    stDocName = ListRP
    Dim ctl As Control
    For Each ctl In Me.Controls 'begin for
        If TypeOf Control Is TextBox Then
            If IsNull(ctl.Value) Then
                msgbox "Please fill in the dates " & _ 
                  ctl.NAME & " value!", vbCritical, & _ 
                  "Dates not filled in!"
                ctl.SetFocus
                GoTo ExitSub
            End If 'If IsNull(ctl.Value) Then
        End If 'If TypeOf Control Is TextBox Then
    Next ctl 'end for   
    DoCmd.OpenReport stDocName, acPreview

ExitSub:
On Error Resume Next
  Set ctl = Nothing
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[HarleyQuinn]

You'll almost certainly have to change the typeof control you're checking for. If I remember correctly the boxes you want to check aren't textboxes.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Triacona]

Thanks very much guys, for your replies

Yes Andy the type of control is not a text box...

It is an activeX Calendar Control...
I have looked the the list Access provides.

I could not find any activeX control reference (After the Is statement) Is there an ActiveX control type? Am I on the right approach?

What is the activeX command for calendars?
The activeX control is basically a text box with a button that opens a calendar and inserts the date into the field.
I have modified my code as below.

Private Sub Rp_Report_Click()
On Error GoTo Err_Rp_Report_Click

    Dim stDocName As String
   
    Dim text As Control
    Set text = RpStartDate
    stDocName = ListRP
    Dim ctl As Control
            
       For Each ctl In Me.Controls 'begin for
        
        If TypeOf Control Is ?????? Then
               
            If IsNull(ctl.Value) Then
            'is there something I must put in the Value instead of value?  
                msgbox "Please fill in the dates " & ctl.NAME & " value!", vbCritical, "Dates not filled in!"
                        
                ctl.SetFocus
                        
                GoTo ExitSub
                        
            End If 'If IsNull(ctl.Text) Then
                    
        End If 'If TypeOf Control Is TextBox Then
                
       Next ctl 'end for
            
     DoCmd.OpenReport stDocName, acPreview
        




ExitSub:
On Error Resume Next
  Set ctl = Nothing
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
    
End Sub

Thank you for all the help, any more help will be greatly appreciated

Kind regards
Triacona

 

[dhookom]

I would just type the word "required" into the tag property of all required controls. Then your code would look like:

Private Sub Rp_Report_Click()
On Error GoTo Err_Rp_Report_Click
    Dim stDocName As String
    Dim text As Control
    Set text = RpStartDate
    stDocName = ListRP
    Dim ctl As Control
    For Each ctl In Me.Controls 'begin for
        If Instr(ctl.Tag,"required") > 0 Then
            If IsNull(ctl.Value) Then
                msgbox "Please fill in the dates " & _ 
                  ctl.NAME & " value!", vbCritical, & _ 
                  "Dates not filled in!"
                ctl.SetFocus
                GoTo ExitSub
            End If 'If IsNull(ctl.Value) Then
        End If 
    Next ctl 'end for   
    DoCmd.OpenReport stDocName, acPreview

ExitSub:
On Error Resume Next
  Set ctl = Nothing
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Thank you very much Duane and Andy

I have attempted your code Duane..
Please could I have some more help

Private Sub Rp_Report_Click()

On Error GoTo Err_Rp_Report_Click

    Dim stdocname As String
    Dim text As Control
    Set text = RpStartDate
    stdocname = ListRP
    Dim ctl As Control
    
        For Each ctl In Me.Controls  'begin for
        
            If InStr(ctl.Tag, "required") > 0 Then
            
                If IsNull(ctl.Value) Then
                
                    msgbox "Please fill in the dates" & ctl.NAME & " value!", vbCritical, "Dates not filled in!"
                    ctl.SetFocus
                    GoTo ExitSub
                    
                End If 'If IsNull(ctl.Value) Then
                
             End If
             
             Next ctl 'end for
             DoCmd.OpenReport stdocname, acPreview
            
ExitSub:
On Error Resume Next
  Set ctl = Nothing
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
End Sub

I have a list, an depending on selection the following two
ActiveX controls appear:
RpStartDate
RpEndDate

I am checking to see if the ActiveX controls are null.
These controls are only available when the user selects the report in the list requiring dates.

The user must input these for the report to run.

I have tried your code and it seems to get bypassed and goes directly to the docmd.openreport command.

Which, when the fields are null, even if your code is removed, gives the following error:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may containt to many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I have tagged the 2 ActiveX Controls with required.
I have also tried "required" (inverted commas included)

What am I doing wrong in the code??

Thank for the help already given
Any further help will be greatly appreciated

Kind regards
Triacona

 

[dhookom]

Try some debugging faq705-7148

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,
Thanks for your help
Some more info...

In the properties of the ActiveX control:

In the data tab
OLE Class: DateCalControl.DateWithCalendar
Class: DateCalControl.DateWithCalendar

In the other tab:
Name: RpStartDate
tag: required

I hope this helps.

Kind regards

Triacona

 

[Triacona]

Dear all,

Thanks for all the help so far

I have tried debugging, the problem seems to come from > 0,
id I change to Is null it asks for an object.
If I leave it at > 0 it bypasses the whole for and if statement structure and goes directly to the docmd.openreport command.

I'm really stuck and I don't know where to go from here
Please, any help would really be greatly appreciated.

kind regards

Triacona

 

[Triacona]

Thanks again for all the help
More information...
I have also tried adding in the command docmd.openreport in each of the if statements, the if statements are bypassed and only the cmd is executed.
It is like the objects don't exist...

Please help

kind regards

Triacona

 

[dhookom]

Apparently you have set a break point. You can hover over any object or variable to view its current value.

Issues with activeX controls is one of the reasons I use a form such as Popup Calendar.

There are plenty of ActiveX control calendars, but they have issues with versioning, broken references, and handling Nulls. This is an ordinary Access form you can import into any database.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Thank you Duane

I will attempt to use this form.

Kind regards

Triacona

 

[Triacona]

Dear all,
Thank you for all the help

I have looked at the form and it would require me to change a lot of fundemental properties of my Access DB...

Is there no way of using the ActiveX controls I have?


Please help, any more help will be very much appreciated

kind regards

Triacona

 

[Triacona]

YAY!!!!!!!!!!!!
I fixed it


Thanks for all the help guys!
The code is below:

Private Sub Rp_Report_Click()

On Error GoTo Err_Rp_Report_Click

    Dim stdocname As String
    stdocname = ListRP
    
    
             If RpStartDate.Object = "" Then
             
                DoCmd.RunMacro "MsgBoxNoDate"
                
                Else: DoCmd.OpenReport stdocname, acPreview
                
             End If
         
ExitSub:
On Error Resume Next
  
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
End Sub

Thanks again!
Kind regards

Triacona

 

[Triacona]

Dear all,

Below is a nested loop which because of the case select code beforehand works.
Yay it all works, now depending on my selection it will bring up "Please enter date" msgbox.

Private Sub Rp_Report_Click()

On Error GoTo Err_Rp_Report_Click

    Dim stdocname As String
    stdocname = ListRP
    
        If RpStartDate.Object = "" Then
             
             If RpStartDate.Visible = True Then
             
                DoCmd.RunMacro "MsgBoxNoDate"
              Else: DoCmd.OpenReport stdocname, acViewPreview
             End If
               
         Else: DoCmd.OpenReport stdocname, acPreview
               
        End If
   
ExitSub:
On Error Resume Next
  
  Exit Sub

Err_Rp_Report_Click:
    msgbox Err.Description
    Resume ExitSub
End Sub

Kind regards
Triacona