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
10 4960
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.
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!
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
Dyn-O-Mite!
Works like a charm:-)
Super thanks, puppydogbuddy!
So simple...
Have a wonderful week-end, thanks!!!
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,
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: Data2Data@DataMeVBA.pap" '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
Dököll,
Wow, you have been busy! Thanks for providing your "final" code.
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: Data2Data@DataMeVBA.pap " '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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| | |