MSGBox: Check empty date field, checkbox control

[Dököll]

Me again!

I have been battling this one for months. Wrote below to aid in getting certain subforms to hide once checkbox named Active is clicked. I am hoping to make the code work for Date fields that may be empty. I think i am going about it the wrong way:

Private Sub Active_BeforeUpdate(Cancel As Integer)
 
'On current event of main form
'Check if checkbox is selected
'then show or hide subform
    If Me.Active = True Then
        Me.InfoOnly.Visible = True
    Else
        Me.InfoOnly.Visible = False
    End If
End Sub
 
 

I tried:

Private Sub Active_BeforeUpdate(Cancel As Integer)
 
'On current event of main form
'Check if checkbox is selected and contact date field is empty
'then pop up an error message
 
    If Me.Active = True And Me.ContactDate Is Null Then
        MsgBox "Please add a contact date to continue..."    
    Else
        ...
    End If
End Sub
 
 

It is not working. In fact, now and then I get an error that something or other is missing, not always, but I do get that. For now, I get nada, code is mute, no errors though, just does not do anything;-)

Can you help? Thanks!

Dököll

[puppydogbuddy]

Me again!

I have been battling this one for months. Wrote below to aid in getting certain subforms to hide once checkbox named Active is clicked. I am hoping to make the code work for Date fields that may be empty. I think i am going about it the wrong way:

Private Sub Active_BeforeUpdate(Cancel As Integer)
 
'On current event of main form
'Check if checkbox is selected
'then show or hide subform
    If Me.Active = True Then
        Me.InfoOnly.Visible = True
    Else
        Me.InfoOnly.Visible = False
    End If
End Sub
 
 

I tried:

Private Sub Active_BeforeUpdate(Cancel As Integer)
 
'On current event of main form
'Check if checkbox is selected and contact date field is empty
'then pop up an error message
 
    If Me.Active = True And Me.ContactDate Is Null Then
        MsgBox "Please add a contact date to continue..."    
    Else
        ...
    End If
End Sub
 
 

It is not working. In fact, now and then I get an error that something or other is missing, not always, but I do get that. For now, I get nada, code is mute, no errors though, just does not do anything;-)

Can you help? Thanks!

Dököll

Dokoll,
Try placing your code in the <<AfterUpdate event of your checkbox>>. You want to hide your subform and check for nulls <<after>>> the checkbox has been checked. If you use the BeforeUpdate code you are presently using, your checkbox will not have been checked, so naturally nothing happens.

[Dököll]

Hello puppydogbuddy!

Thanks for your reply. My subform example works great. What I needed was to use the same code to search for instances where ContactDate field is empty, among others, if user checks the checkbox named Active. Also tried:

 
If Me.Active.Value = True And Me.ContactDate.Value = True Then
  la la la
 
Else
 
 MsGBox "Hummm!"
 
End If
 
 

Did not get what I expected there. This was a test with one date field, but there are others included...If we can find a solution, that'd be awesome. Can use it elsewhere in the system. But I did come up with another option to aid the user.

Attempted an update query to find instances where Active = False And ContactDate = True (among other date fields) and system updates for the user. I kind of like this one since it minimizes work load for our users (especially if they forget to do it everytime).

Module code:

 
Option Compare Database
 
Sub RunMultiSQL()
 
  DoCmd.SetWarnings False
 
  DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null")
  DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE OpenDate Is Not Null")
  DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE CloseDate Is Not Null")
 
  DoCmd.SetWarnings True
 
End Sub
 
 

This works great but the only problem is if either of the date fields are found to have data, Active is True. I don't want that. So I tried:

 
Option Compare Database
 
Sub RunMultiSQL()
 
  DoCmd.SetWarnings False
 
  DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null And OpenDate Is Not Null And CloseDate Is Not Null ")
 
  DoCmd.SetWarnings True
 
End Sub
 
 

While I have no errors, Active does not record True where dates are available.

Any ideas, and thanks for your input!

[puppydogbuddy]

Hello puppydogbuddy!

Thanks for your reply. My subform example works great. What I needed was to use the same code to search for instances where ContactDate field is empty, among others, if user checks the checkbox named Active. Also tried:

 
If Me.Active.Value = True And Me.ContactDate.Value = True Then
  la la la
 
Else
 
 MsGBox "Hummm!"
 
End If
 
 

Did not get what I expected there. This was a test with one date field, but there are others included...If we can find a solution, that'd be awesome. Can use it elsewhere in the system. But I did come up with another option to aid the user.

Attempted an update query to find instances where Active = False And ContactDate = True (among other date fields) and system updates for the user. I kind of like this one since it minimizes work load for our users (especially if they forget to do it everytime).

Module code:

 
Option Compare Database
 
Sub RunMultiSQL()
 
  DoCmd.SetWarnings False
 
  DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null")
  DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE OpenDate Is Not Null")
  DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE CloseDate Is Not Null")
 
  DoCmd.SetWarnings True
 
End Sub
 
 

This works great but the only problem is if either of the date fields are found to have data, Active is True. I don't want that. So I tried:

 
Option Compare Database
 
Sub RunMultiSQL()
 
  DoCmd.SetWarnings False
 
  DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null And OpenDate Is Not Null And CloseDate Is Not Null ")
 
  DoCmd.SetWarnings True
 
End Sub
 
 

While I have no errors, Active does not record True where dates are available.

Any ideas, and thanks for your input!

Hi Dököll,

Change this line:
If Me.Active.Value = True And Me.ContactDate.Value = True Then

To This:
If Me.Active.Value = True And IsNull(Me.ContactDate) Then

[Dököll]

Dyn-O-Mite!

Works like a charm:-)

Super thanks, puppydogbuddy!

So simple...

Have a wonderful week-end, thanks!!!

[puppydogbuddy]

Dyn-O-Mite!

Works like a charm:-)

Super thanks, puppydogbuddy!

So simple...

Have a wonderful week-end, thanks!!!

Dököll,

You are most welcome. Glad I could help, and thanks for the feedback.

[Dököll]

Dököll,

You are most welcome. Glad I could help, and thanks for the feedback.

Quite welcome...

I tweaked a bit, you got my creatinve juices flowing. A desperate attempt to keep clean data for future users. Still need work, but no known bugs:

 
 A checkbox named Phoned...
 
Private Sub Phoned_AfterUpdate()
If IsNull(Me.ContactDate) And Me.Phoned = True Then 'Let's do some checking
MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _
               "Data Central"
Me.Phoned = False 'report false because user must add contact info...
ElseIf Me.Phoned.Value = True And Me.ContactPerson <> Null Then 'Let user in if there isn't any issues...
    Exit Sub 'Get out since there are no issue
        Else 'If there are issues, let user know...
         If MsgBox("Please add name and phone number for incoming/outgoing phone contacts." & _
                    vbCrLf & vbCrLf & "Do you have a phone number to add for this entry?", _
                    vbYesNo, "Data Central Reminder") = vbYes Then
    Exit Sub
                    Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error
        Else 'If user does not have a number, provide an option....
         If MsgBox("You are required to add a precise comment for this entry." & _
                    vbCrLf & vbCrLf & "Please note dummy phone number for this entry.", _
                    vbYesNo, "Data Central Reminder") = vbYes Then
         Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error
         Me.ContactPerson.Value = "Data Central: 000-000-0000" 'Add a dummy phone number
         'Note you are referencing  .Value and not .Text
         Else
    Exit Sub 'Get out since you've completed your mission...
         End If
         End If
         End If
 
 
End Sub
 
 

another one named Emailed...

 
Private Sub Emailed_AfterUpdate() 'Emailed is the name of our Checkbox
If IsNull(Me.ContactDate) And Me.Emailed = True Then 'Let's do some checking
MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _
               "Data Central"
Me.Email = False 'report false because user must add contact info (Email is the name our Textbox)...
ElseIf Me.Emailed.Value = True And Me.ContactPerson <> Null Then 'Let user in if there isn't any issues...
    Exit Sub 'Get out since there are no issue
        Else 'If there are issues, let user know...
         If MsgBox("Please add email address for incoming/outgoing email contacts." & _
                    vbCrLf & vbCrLf & "Do you have an email address to add for this entry?", _
                    vbYesNo, "Data Central Reminder") = vbYes Then
    Exit Sub
                    Me.Email.SetFocus 'Set focus to this textbox to avoid an error
        Else 'If user does not have a number, provide an option....
         If MsgBox("You are required to add a precise comment for this entry." & _
                    vbCrLf & vbCrLf & "Please note dummy email address for this entry.", _
                    vbYesNo, "Data Central Reminder") = vbYes Then
         Me.Email.SetFocus 'Set focus to this textbox to avoid an error
         Me.Email.Value = "Data Central: [e-mail]" 'Note you are referencing  .Value and not .Text
         'If .Text you will get an error
         Else
    Exit Sub 'Get out since you've completed your mission...
         End If
         End If
         End If
 
End Sub
 
 

Thanks again!

Dököll

[puppydogbuddy]

Dököll,
Wow, you have been busy! Thanks for providing your "final" code.

[Dököll]

Dököll,
Wow, you have been busy! Thanks for providing your "final" code.

Sure, who knows someone may be experiencing the same issue. I did more to it because during testing, I found out I could further tighen it...this is the "final". Anything more I think should probably add as an Article. I'll tell you why in a minute:

 
 
Private Sub Phoned_AfterUpdate() 
If IsNull(Me.ContactDate) And Me.Phoned = True Then 'Let's do some checking 
MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _ 
               "Data Central Reminder " 
Me.Phoned = False 'report false because user must add contact info... 
ElseIf Me.Phoned.Value = True And Me.ContactPerson <> Null Then 'Let user in if there isn't any issue... 
    Exit Sub 'Get out since there are no issue 
        Else 'If there are issues, let user know... 
         If MsgBox("Please add name and phone number for incoming/outgoing phone contacts." & _ 
                    vbCrLf & vbCrLf & "Do you have a phone number to add for this entry?", _ 
                    vbYesNo, "Data Central Reminder") = vbYes Then 
         'Me.Phoned.Value = False 'report Phoned checkbox false 
         If IsNull(Me.ContactPerson) Then 
         Me.Phoned.Value = False 
         ElseIf Me.ContactPerson <> Null Then 
         Me.Phoned.Value = True 
         'Me.Phoned.Locked = True 
                  End If 
    Exit Sub 
                    Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error 
        Else 'If user does not have a number, provide an option.... 
         If MsgBox("You are required to add a precise comment for this entry." & _ 
                    vbCrLf & vbCrLf & "Please note dummy phone number for this entry", _ 
                    vbOKCancel, "Data Central Reminder") = vbOK Then 
         Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error 
         Me.ContactPerson.Value = "Data Central: 000-000-0000" 'Add a dummy phone number 
         'Note you are referencing  .Value and not .Text 
         Me.Phoned.Value = True 'report Phoned checkbox true 
         Else 
    Exit Sub 'Get out since you've completed your mission... 
         End If 
         End If 
         End If 
End Sub 
 
 
 
 
'We need to make sure user is entering an email address if contact was made 
'If an email address is not available, we can add a dummy email address 
'but user will need to eventually add a precise comment for reason 
'to add a dummy email address as contact info... 
Private Sub Emailed_AfterUpdate() 
If IsNull(Me.ContactDate) And Me.Emailed = True Then 'Let's do some checking 
MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _ 
               "Data Central Reminder " 
Me.Emailed = False 'report false because user must add contact info... 
ElseIf Me.Emailed.Value = True And Me.Email <> Null Then 'Let user in if there isn't any issue... 
    Exit Sub 'Get out since there is no issue 
        Else 'If there are issues, let user know... 
         If MsgBox("Please add an email address for incoming/outgoing phone contacts." & _ 
                    vbCrLf & vbCrLf & "Do you have an email address to add for this entry?", _ 
                    vbYesNo, "Data Central Reminder") = vbYes Then 
         If IsNull(Me.Email) Then ‘This may not be necessary, have not tested it truly
         Me.Emailed.Value = False ‘Dido
         ElseIf Me.Email <> Null Then ‘Dido
         Me.Emailed.Value = True ‘Dido
 
                  End If 
    Exit Sub 
                    Me.Email.SetFocus 'Set focus to this textbox to avoid an error 
        Else 'If user does not have a number, provide an option.... 
         If MsgBox("You are required to add a precise comment for this entry." & _ 
                    vbCrLf & vbCrLf & "Please note dummy email address for this entry?", _ 
                    vbOKCancel, "Data Central Reminder") = vbOK Then 
         Me.Email.SetFocus 'Set focus to this textbox to avoid an error 
         Me.Email.Value = "Data Central: [e-mail] " 'Add a dummy email address 
         'Note you are referencing  .Value and not .Text 
         Me.Emailed.Value = True 'report Emailed checkbox true 
         Else 
    Exit Sub 'Get out since you've completed your mission... 
         End If 
         End If 
         End If 
End Sub 
 
 

What I found out is if I select an already selected checkbox, I still get prompted to add info. I could not figure out how to supress that, so I decided to get rid of the checkbox after it has been checked and make reappear only when the save button has been pushed. On Current through the form aid in making this possible.

On Current:

 
Private Sub Form_Current() 
Me.Phoned.Visible = True 
Me.Emailed.Visible = True 
End Sub 
 
 
To make sure I do not have to check the checkbox again, since first attempt clears after prompt.  When I enter the ContactPerson textbox, Phoned checkbox is checked automatically, works in conjunction with the ON current event, then disappears.  So I do cannot get prompted because the checkbo has vanished.
 
On Enter 
 
Private Sub ContactPerson_Enter() 
Me.Phoned = True 
Me.Phoned.Visible = False 
End Sub 
 
Private Sub Email_Enter() 
Me.Emailed = True 
Me.Emailed.Visible = False 
End Sub
 
 

What I think may be needed is a way to skillfully go through the ContactPerson and Email textboxes to validate the email or phone number added there, keep bogus ones out.

There has to be a code for that.

But this is where we are for now puppydogbuddy, thanks for reading my rambling and again thank you. You really got me going with this one.

Oh, here's another thing that may be needed. If user must add precise information, it'd be nice I could make sure the character length of the Comment field is at least 255 chars long.

I thought of setting legal words like 'unavailable' , 'not in service', and so one for the comment box so I'd know user did in fact attempt to add added info in comment, but each user enters info differently and may just say, 'no email' or something not included in the code.

Also may not satisfy the idea to have added info, credible info to figure out why a dummy email or phone had to be added.

If I can figure this out, it should be added to the above, perhaps then as an Article, I think that'd be good there

In a bit!

[puppydogbuddy]

Dokoll,
This link should help you with screening the emails:

http://www.fabalou.com/Access/Modules/reading_email.asp

[Dököll]

Dokoll,
This link should help you with screening the emails:

http://www.fabalou.com/Access/Modules/reading_email.asp

Neat, will give that a whirl...

Good afternoon:-)