Dököll 2,364
Recognized Expert Top Contributor
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 5032
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 2,364
Recognized Expert Top Contributor
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.Conta ctDate) Then
Dököll 2,364
Recognized Expert Top Contributor
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 2,364
Recognized Expert Top Contributor
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 2,364
Recognized Expert Top Contributor
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 in the VB project,
works fine in C# project.
SystemModal and ApplicationModal MsgBoxStyle were all tried and there
was no help.
|
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
rejected. A supervisor opens frmVacationWeeks and either approves or
rejects dates the employee has requested for vacation. When the
supervisor closes frmVacationWeeks, if he has failed to click a
checkbox to approve or reject a date, I want a...
|
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 is not much help here either.
Googling has given me a little help.
This is my current understanding -- I would appreciate any comments or
corrections...
"" -- this means an empty string when applied to String data type, and also
to Variant...
|
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 the check box in
datagrid
If the value is null then keep it unchanged as (Unchecked)
|
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 the example to work. I created the
following two classes, provided with the example:
*-*-**-*-*-*-*-*-*-*-*-*-**-*-*-*-*-CheckBoxColumn
Class:-*-*-*-*-*-*-*-*-*-*-**-*-*-*-*-**-*-*-*
| |
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 isnot any checkbox or proceduce on my form.
if the D&T picker control is unbound to any field, no message appear.
Can anyone explain to me what is trouble ?
Thanks ur time visitting my post.
Luan from VietNam
|
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, when it is either checked or unchecked, I would like it to stamp
todays date or remove the date from the control source. I can see how
to do this if there are seperate fields for the checkbox and date, but
I would like to combine them into one field...
|
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 current date into one of the fields in the form?
In Design View I put a checkbox alongside a field (Honorarium1).
I clicked to select it, then in its Properties I typed Honorarium1 as the Control Source.
When I clicked in the checkbox in...
|
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 be ticked.
I am quite new to access to please only easy solutions
Many Thanks
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |