By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,665 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,665 IT Pros & Developers. It's quick & easy.

MSGBox: Check empty date field, checkbox control

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Active_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'On current event of main form
  4. 'Check if checkbox is selected
  5. 'then show or hide subform
  6.     If Me.Active = True Then
  7.         Me.InfoOnly.Visible = True
  8.     Else
  9.         Me.InfoOnly.Visible = False
  10.     End If
  11. End Sub
  12.  
  13.  
I tried:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Active_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'On current event of main form
  4. 'Check if checkbox is selected and contact date field is empty
  5. 'then pop up an error message
  6.  
  7.     If Me.Active = True And Me.ContactDate Is Null Then
  8.         MsgBox "Please add a contact date to continue..."    
  9.     Else
  10.         ...
  11.     End If
  12. End Sub
  13.  
  14.  
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
Nov 30 '07 #1
Share this Question
Share on Google+
10 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Active_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'On current event of main form
  4. 'Check if checkbox is selected
  5. 'then show or hide subform
  6.     If Me.Active = True Then
  7.         Me.InfoOnly.Visible = True
  8.     Else
  9.         Me.InfoOnly.Visible = False
  10.     End If
  11. End Sub
  12.  
  13.  
I tried:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Active_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'On current event of main form
  4. 'Check if checkbox is selected and contact date field is empty
  5. 'then pop up an error message
  6.  
  7.     If Me.Active = True And Me.ContactDate Is Null Then
  8.         MsgBox "Please add a contact date to continue..."    
  9.     Else
  10.         ...
  11.     End If
  12. End Sub
  13.  
  14.  
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.
Nov 30 '07 #2

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Me.Active.Value = True And Me.ContactDate.Value = True Then
  3.   la la la
  4.  
  5. Else
  6.  
  7.  MsGBox "Hummm!"
  8.  
  9. End If
  10.  
  11.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Sub RunMultiSQL()
  5.  
  6.   DoCmd.SetWarnings False
  7.  
  8.   DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null")
  9.   DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE OpenDate Is Not Null")
  10.   DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE CloseDate Is Not Null")
  11.  
  12.   DoCmd.SetWarnings True
  13.  
  14. End Sub
  15.  
  16.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Sub RunMultiSQL()
  5.  
  6.   DoCmd.SetWarnings False
  7.  
  8.   DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null And OpenDate Is Not Null And CloseDate Is Not Null ")
  9.  
  10.   DoCmd.SetWarnings True
  11.  
  12. End Sub
  13.  
  14.  
While I have no errors, Active does not record True where dates are available.

Any ideas, and thanks for your input!
Dec 7 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Me.Active.Value = True And Me.ContactDate.Value = True Then
  3.   la la la
  4.  
  5. Else
  6.  
  7.  MsGBox "Hummm!"
  8.  
  9. End If
  10.  
  11.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Sub RunMultiSQL()
  5.  
  6.   DoCmd.SetWarnings False
  7.  
  8.   DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null")
  9.   DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE OpenDate Is Not Null")
  10.   DoCmd.RunSQL ("UPDATE Data_Central  SET Active = True WHERE CloseDate Is Not Null")
  11.  
  12.   DoCmd.SetWarnings True
  13.  
  14. End Sub
  15.  
  16.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Sub RunMultiSQL()
  5.  
  6.   DoCmd.SetWarnings False
  7.  
  8.   DoCmd.RunSQL ("UPDATE Data_Central SET Active = True WHERE ContactDate Is Not Null And OpenDate Is Not Null And CloseDate Is Not Null ")
  9.  
  10.   DoCmd.SetWarnings True
  11.  
  12. End Sub
  13.  
  14.  
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
Dec 7 '07 #4

Dököll
Expert 100+
P: 2,364
Dyn-O-Mite!

Works like a charm:-)

Super thanks, puppydogbuddy!

So simple...

Have a wonderful week-end, thanks!!!
Dec 7 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Dec 7 '07 #6

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. A checkbox named Phoned...
  3.  
  4. Private Sub Phoned_AfterUpdate()
  5. If IsNull(Me.ContactDate) And Me.Phoned = True Then 'Let's do some checking
  6. MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _
  7.                "Data Central"
  8. Me.Phoned = False 'report false because user must add contact info...
  9. ElseIf Me.Phoned.Value = True And Me.ContactPerson <> Null Then 'Let user in if there isn't any issues...
  10.     Exit Sub 'Get out since there are no issue
  11.         Else 'If there are issues, let user know...
  12.          If MsgBox("Please add name and phone number for incoming/outgoing phone contacts." & _
  13.                     vbCrLf & vbCrLf & "Do you have a phone number to add for this entry?", _
  14.                     vbYesNo, "Data Central Reminder") = vbYes Then
  15.     Exit Sub
  16.                     Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error
  17.         Else 'If user does not have a number, provide an option....
  18.          If MsgBox("You are required to add a precise comment for this entry." & _
  19.                     vbCrLf & vbCrLf & "Please note dummy phone number for this entry.", _
  20.                     vbYesNo, "Data Central Reminder") = vbYes Then
  21.          Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error
  22.          Me.ContactPerson.Value = "Data Central: 000-000-0000" 'Add a dummy phone number
  23.          'Note you are referencing  .Value and not .Text
  24.          Else
  25.     Exit Sub 'Get out since you've completed your mission...
  26.          End If
  27.          End If
  28.          End If
  29.  
  30.  
  31. End Sub
  32.  
  33.  
another one named Emailed...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Emailed_AfterUpdate() 'Emailed is the name of our Checkbox
  3. If IsNull(Me.ContactDate) And Me.Emailed = True Then 'Let's do some checking
  4. MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _
  5.                "Data Central"
  6. Me.Email = False 'report false because user must add contact info (Email is the name our Textbox)...
  7. ElseIf Me.Emailed.Value = True And Me.ContactPerson <> Null Then 'Let user in if there isn't any issues...
  8.     Exit Sub 'Get out since there are no issue
  9.         Else 'If there are issues, let user know...
  10.          If MsgBox("Please add email address for incoming/outgoing email contacts." & _
  11.                     vbCrLf & vbCrLf & "Do you have an email address to add for this entry?", _
  12.                     vbYesNo, "Data Central Reminder") = vbYes Then
  13.     Exit Sub
  14.                     Me.Email.SetFocus 'Set focus to this textbox to avoid an error
  15.         Else 'If user does not have a number, provide an option....
  16.          If MsgBox("You are required to add a precise comment for this entry." & _
  17.                     vbCrLf & vbCrLf & "Please note dummy email address for this entry.", _
  18.                     vbYesNo, "Data Central Reminder") = vbYes Then
  19.          Me.Email.SetFocus 'Set focus to this textbox to avoid an error
  20.          Me.Email.Value = "Data Central: Data2Data@DataMeVBA.pap" 'Note you are referencing  .Value and not .Text
  21.          'If .Text you will get an error
  22.          Else
  23.     Exit Sub 'Get out since you've completed your mission...
  24.          End If
  25.          End If
  26.          End If
  27.  
  28. End Sub
  29.  
  30.  
Thanks again!

Dököll
Dec 11 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Dököll,
Wow, you have been busy! Thanks for providing your "final" code.
Dec 11 '07 #8

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Private Sub Phoned_AfterUpdate() 
  4. If IsNull(Me.ContactDate) And Me.Phoned = True Then 'Let's do some checking 
  5. MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _ 
  6.                "Data Central Reminder " 
  7. Me.Phoned = False 'report false because user must add contact info... 
  8. ElseIf Me.Phoned.Value = True And Me.ContactPerson <> Null Then 'Let user in if there isn't any issue... 
  9.     Exit Sub 'Get out since there are no issue 
  10.         Else 'If there are issues, let user know... 
  11.          If MsgBox("Please add name and phone number for incoming/outgoing phone contacts." & _ 
  12.                     vbCrLf & vbCrLf & "Do you have a phone number to add for this entry?", _ 
  13.                     vbYesNo, "Data Central Reminder") = vbYes Then 
  14.          'Me.Phoned.Value = False 'report Phoned checkbox false 
  15.          If IsNull(Me.ContactPerson) Then 
  16.          Me.Phoned.Value = False 
  17.          ElseIf Me.ContactPerson <> Null Then 
  18.          Me.Phoned.Value = True 
  19.          'Me.Phoned.Locked = True 
  20.                   End If 
  21.     Exit Sub 
  22.                     Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error 
  23.         Else 'If user does not have a number, provide an option.... 
  24.          If MsgBox("You are required to add a precise comment for this entry." & _ 
  25.                     vbCrLf & vbCrLf & "Please note dummy phone number for this entry", _ 
  26.                     vbOKCancel, "Data Central Reminder") = vbOK Then 
  27.          Me.ContactPerson.SetFocus 'Set focus to this textbox to avoid an error 
  28.          Me.ContactPerson.Value = "Data Central: 000-000-0000" 'Add a dummy phone number 
  29.          'Note you are referencing  .Value and not .Text 
  30.          Me.Phoned.Value = True 'report Phoned checkbox true 
  31.          Else 
  32.     Exit Sub 'Get out since you've completed your mission... 
  33.          End If 
  34.          End If 
  35.          End If 
  36. End Sub 
  37.  
  38.  
  39.  
  40.  
  41. 'We need to make sure user is entering an email address if contact was made 
  42. 'If an email address is not available, we can add a dummy email address 
  43. 'but user will need to eventually add a precise comment for reason 
  44. 'to add a dummy email address as contact info... 
  45. Private Sub Emailed_AfterUpdate() 
  46. If IsNull(Me.ContactDate) And Me.Emailed = True Then 'Let's do some checking 
  47. MsgBox "You forgot to add a Contact date, please do so to contimue...", vbInformation + vbOKOnly, _ 
  48.                "Data Central Reminder " 
  49. Me.Emailed = False 'report false because user must add contact info... 
  50. ElseIf Me.Emailed.Value = True And Me.Email <> Null Then 'Let user in if there isn't any issue... 
  51.     Exit Sub 'Get out since there is no issue 
  52.         Else 'If there are issues, let user know... 
  53.          If MsgBox("Please add an email address for incoming/outgoing phone contacts." & _ 
  54.                     vbCrLf & vbCrLf & "Do you have an email address to add for this entry?", _ 
  55.                     vbYesNo, "Data Central Reminder") = vbYes Then 
  56.          If IsNull(Me.Email) Then ‘This may not be necessary, have not tested it truly
  57.          Me.Emailed.Value = False ‘Dido
  58.          ElseIf Me.Email <> Null Then ‘Dido
  59.          Me.Emailed.Value = True ‘Dido
  60.  
  61.                   End If 
  62.     Exit Sub 
  63.                     Me.Email.SetFocus 'Set focus to this textbox to avoid an error 
  64.         Else 'If user does not have a number, provide an option.... 
  65.          If MsgBox("You are required to add a precise comment for this entry." & _ 
  66.                     vbCrLf & vbCrLf & "Please note dummy email address for this entry?", _ 
  67.                     vbOKCancel, "Data Central Reminder") = vbOK Then 
  68.          Me.Email.SetFocus 'Set focus to this textbox to avoid an error 
  69.          Me.Email.Value = "Data Central: Data2Data@DataMeVBA.pap " 'Add a dummy email address 
  70.          'Note you are referencing  .Value and not .Text 
  71.          Me.Emailed.Value = True 'report Emailed checkbox true 
  72.          Else 
  73.     Exit Sub 'Get out since you've completed your mission... 
  74.          End If 
  75.          End If 
  76.          End If 
  77. End Sub 
  78.  
  79.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Current() 
  3. Me.Phoned.Visible = True 
  4. Me.Emailed.Visible = True 
  5. End Sub 
  6.  
  7.  
  8. 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.
  9.  
  10. On Enter 
  11.  
  12. Private Sub ContactPerson_Enter() 
  13. Me.Phoned = True 
  14. Me.Phoned.Visible = False 
  15. End Sub 
  16.  
  17. Private Sub Email_Enter() 
  18. Me.Emailed = True 
  19. Me.Emailed.Visible = False 
  20. End Sub
  21.  
  22.  
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!
Dec 13 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
Dokoll,
This link should help you with screening the emails:

http://www.fabalou.com/Access/Modules/reading_email.asp
Dec 13 '07 #10

Dököll
Expert 100+
P: 2,364
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:-)
Dec 13 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.