473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MSGBox: Check empty date field, checkbox control

Dököll
2,364 Expert 2GB
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
10 4960
puppydogbuddy
1,923 Expert 1GB
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
2,364 Expert 2GB
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
1,923 Expert 1GB
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
2,364 Expert 2GB
Dyn-O-Mite!

Works like a charm:-)

Super thanks, puppydogbuddy!

So simple...

Have a wonderful week-end, thanks!!!
Dec 7 '07 #5
puppydogbuddy
1,923 Expert 1GB
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
2,364 Expert 2GB
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
1,923 Expert 1GB
Dököll,
Wow, you have been busy! Thanks for providing your "final" code.
Dec 11 '07 #8
Dököll
2,364 Expert 2GB
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
1,923 Expert 1GB
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
2,364 Expert 2GB
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

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

Similar topics

2
by: js | last post by:
Hi all, I currently encounter a problem and it is urgent to me. After calling the MsgBox.Show(), the message box is shown with non-modal mode, what is the possible reason??? This only happen...
17
by: jdph40 | last post by:
Question 1: In Access 97, I have a form (frmVacationWeeks) with a subform (sbfrmPostVacDates). The subform can have up to 33 records and each record has 2 checkboxes, one for approved and one for...
8
by: Lyn | last post by:
I am trying to get my head around the concept of default, special or empty values that appear in Access VBA, depending on data type. The Access Help is not much (help), and the manual that I have...
1
by: Neal | last post by:
HI all, I have an issue with check box in datagrid. I would like it to check and uncheck based on a database value of NULL or a Date Value. If there is a date value for that column then check...
1
by: sianan | last post by:
I tried to use the following example, to add a checkbox column to a DataGrid in an ASP.NET application: http://www.codeproject.com/aspnet/datagridcheckbox.asp For some reason, I simply CAN'T get...
3
by: luanhoxung | last post by:
hi all ! i have a date & time picker control bound to 1 field. it worked fine. But i always get the message :" Can't set value to Null when checkbox properties = FALSE" when i open my form. there...
4
by: andrewbda | last post by:
I have tickbox on a form, and I would like to use a date field as the control source. i.e. I would like to have it display as ticked when a date exists in the field, and vice versa. Also,...
3
by: jomats | last post by:
I am a newbie trying to learn Access from several books. I'm sure this is an easy one to answer..for someone, not me. Is it possible to insert a checkbox on a form which, when checked, inserts the...
1
by: AP | last post by:
I am creating a database form that has a date field and a check box. When a user enters a date then I want the check box to automatically be null and if no date is entered I want the check box to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.