Message box when DOB meets certain criteria

Hi all,

I am a relative novice to the ways of Access, I am currently setting up a
database in Access 2003.  

I have a form field (DOB) that if the date of birth entered makes the person
between 2 and 16 years old I would like a message box to appear notifying the
user (and be able to turn it off).  Similarly if an existing record is opened
and a child has it's second birthday I would also like the message to appear..
.I have posted a similar request and have got thus far:

Private Sub DOB_BeforeUpdate(Cancel As Integer)
If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
      ("yyyy", 16, [DOB]) < Date Then
MsgBox "Please contact the relevant department"
  Cancel = False
End If
End Sub

Any help would be vert much appreciated...

Sam

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1

0
SamMexico
4/19/2010 12:34:22 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
1015 Views

Similar Articles

[PageSpeed] 40

On Mon, 19 Apr 2010 12:34:22 GMT, "SamMexico via AccessMonster.com"
<u59312@uwe> wrote:

For testing age between 2 and 16 you need an AND condition, not an OR:
If DateAdd("yyyy", 2, [DOB]) > Date AND ...

If you want to test for exactly 2nd birthday, that would be:
If DateAdd("yyyy", 2, [DOB]) = Date then msgbox "Happy 2nd b'day"

-Tom.
Microsoft Access MVP


>Hi all,
>
>I am a relative novice to the ways of Access, I am currently setting up a
>database in Access 2003.  
>
>I have a form field (DOB) that if the date of birth entered makes the person
>between 2 and 16 years old I would like a message box to appear notifying the
>user (and be able to turn it off).  Similarly if an existing record is opened
>and a child has it's second birthday I would also like the message to appear..
>I have posted a similar request and have got thus far:
>
>Private Sub DOB_BeforeUpdate(Cancel As Integer)
>If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
>      ("yyyy", 16, [DOB]) < Date Then
>MsgBox "Please contact the relevant department"
>  Cancel = False
>End If
>End Sub
>
>Any help would be vert much appreciated...
>
>Sam
0
Tom
4/19/2010 2:45:38 PM
> .................................................Similarly if an existing 
record is opened
> and a child has it's second birthday I would also like the message to appear..

Where you have the code now, "DOB_BeforeUpdate", only fires when you 
add/change the DOB.

You'll also need the code in the Form Current event to have the message 
display for existing records.


> user (and be able to turn it off).  

Do you mean that you want to be able to not have the message box display for 
any record at any time, or when the message box appears, you want to close 
the message box but still have it available to display if the DOB is between 
2 and 16??


HTH
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"SamMexico via AccessMonster.com" wrote:

> Hi all,
> 
> I am a relative novice to the ways of Access, I am currently setting up a
> database in Access 2003.  
> 
> I have a form field (DOB) that if the date of birth entered makes the person
> between 2 and 16 years old I would like a message box to appear notifying the
> user (and be able to turn it off).  Similarly if an existing record is opened
> and a child has it's second birthday I would also like the message to appear..
> .I have posted a similar request and have got thus far:
> 
> Private Sub DOB_BeforeUpdate(Cancel As Integer)
> If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
>       ("yyyy", 16, [DOB]) < Date Then
> MsgBox "Please contact the relevant department"
>   Cancel = False
> End If
> End Sub
> 
> Any help would be vert much appreciated...
> 
> Sam
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1
> 
> .
> 
0
Utf
4/20/2010 3:25:01 AM
Hi Steve, thanks for the response.  When I open the record in the form (by
search box) and the person has a birth date that means they have reached the
age of 2 I would like a message box to appear so that the user can notify the
relevant department.  Similarly if I enter a new record and the person is
between the ages of 2 and 16 I would like the same message to appear.

This message should only appear once and not everytime I open a record where,
say, the person is 14...

I hope that makes sense....?

Sam

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1

0
SamMexico
4/20/2010 8:09:39 AM
OK, the message box should appear only one per person if their age is between 
2 and 16.

Just so we're using the same terms, Tables have fields, Forms have controls. 
Controls are unbound or bound to fields.

One way would be to add a field to the table where the DOB is stored. Maybe 
call it "Notified"; the datatype should be Boolean.
 
Add a control (a check box) bound to the field to the form.


Then I would modify to:

'----------------------------------
Private Sub DOB_BeforeUpdate(Cancel As Integer)

   If Not Me.Notified Then
      'age between 2 and 16?
      If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
         ("yyyy", 16, [DOB]) < Date Then
         
         MsgBox "Please contact the relevant department"
         'You've been notified, so set the flag
         Me.Notified = True

      End If
   End If
End Sub
'----------------------------------

If you add a person (baby), the message box won't open because it is too 
young. But if you open the form in two years, the message box should appear. 
If you don't also have the code in the form current event, you will never be 
notified.

So, you need this code also (could be needed for more than one form):

'--------------------------
Private Sub Form_Current()

   If Not Me.Notified Then
      'age between 2 and 16?
      If DateAdd("yyyy", 2, [DOB]) > Date Or DateAdd _
         ("yyyy", 16, [DOB]) < Date Then
         
         MsgBox "Please contact the relevant department"
         'You've been notified, so set the flag
         Me.Notified = True

      End If
   End If
   
End Sub
'--------------------------

Like I said, there are several ways to do this. Maybe someone else will have 
a better way.

HTH
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"SamMexico via AccessMonster.com" wrote:

> Hi Steve, thanks for the response.  When I open the record in the form (by
> search box) and the person has a birth date that means they have reached the
> age of 2 I would like a message box to appear so that the user can notify the
> relevant department.  Similarly if I enter a new record and the person is
> between the ages of 2 and 16 I would like the same message to appear.
> 
> This message should only appear once and not everytime I open a record where,
> say, the person is 14...
> 
> I hope that makes sense....?
> 
> Sam
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1
> 
> .
> 
0
Utf
4/20/2010 11:31:02 AM
Reply:

Similar Artilces:

Tagging messages on server side
Hello, We need to mark all messages that arrive at one particular mailbox so that we can use this mark to trigger Outlook rule when replaying on behalf of this mailbox. We tried to use assign category rule, but it's client-side. Agent scripting isn't suitable as it runs only once a minute and somebody could make quick replay before it has triggered. Any suggestions? Janis Irbe Janis: It sounds like you need to write some custom code. Exchange 2000/2003 allows you to write "store sinks" that can process a message in one or more mailboxes. When you are reading do...

Redeployement Tool / Error Message
Hello, I would like to "duplicate" a Dev environement to a Test one. All my CRM environment are in the same Active Directory and in the same OU. I am using the redeployement tool and strictly follows the redeployment procedure: Step 1: Backup Databases Step 2: Backup Customizations, Workflow and Customs Reports Step 3: AD. No modifications Step 4: Restore Databases Step 5: Run the Redeployment Tool I chose the "keep existing user mapping" --> All my existing users acounts are mapped And then the error message "An error occurred when populating Microso...

Plotting if a certain criteria is met
I am trying to select data for a bar chart. In Column B i have one of two labels: ES or ZN. I would like the bar chart to plot only the values in corresponding rows of column Q that have "ES" in the B column. Thus if: B Q 1 ES 2 2 ZN 4 3 ES 3 The the chart should only show 2 bars: one for Q1 (2) and one for Q3 (3). What would be the formula that i could write in the Chart Data Range box that would accomplish this? Thanks EG Hi, You could try something as simple as putting the following in R1 and copying down. =IF(B1="ES"...

how do i curtain certain records
Hello all I have a customer who uses goldmine record curtaining so certain users only have access to these confidential records. If in MSCRM I want to set ten records that are highly confidential to one person but I want them to see all other records and all other users to see all other records how do we go about this. Would you do the following 1) setup a business unit for these records How would i limit these records to other users higher up the business unit or to someone in another business unit which is not necessarily at the top end of the business unit? 2) Restrict user acces...

New users don't get email box when created in Active Directory
When I create a new user on my Windows 2000 server, in Active Directory, I automacticly get the option to create a mailbox for this new user in the Exchange 2003 server. I make sure the option is checked, but when I finish the process of making the new user, the new user doesn't get a mailbox. I'm able to go into the "E-mail Addresses" tab inside the properties box for the new user, and add a New SMTP address for that user. Then I have to go into ADSI Edit to change the "msExchaUserAccountControl" value to 0. ( I did this because I got errors 1022 and 9562 in...

Can't see email message content
I can received messages but when I open them, the message content is blank and I get a continuous hour glass. I have done virus scans - no viruses. I have contacted my ISP and they basically told me the don't know what's happening. Can you help? I have the same issue and was told by ISP to contact Microsoft...help!!! >-----Original Message----- >I can received messages but when I open them, the message content is blank and I get a continuous hour glass. I have done virus scans - no viruses. I have contacted my ISP and they basically told me the don't know what's...

Supress error message
I am using the formla: =average(b35:b47) to put the average of cells b35 to b47 into cell b48. Is there a way to avoid the cell b48 showing the result : #DIV/0! when I have no entries in the range of aforementioned cells? Thank you Jim try this ARRAY formula which must be entered/edited with CSE (ctrl+shift+enter) =AVERAGE(IF(C1:C5<>"",C1:C5)) -- Don Guillett SalesAid Software donaldb@281.com "Jim Anderson" <janderson@wi.rr.com> wrote in message news:5R6rd.2025$NO5.1347@twister.rdc-kc.rr.com... > I am using the formla: > =average(b35:b47) > to put...

Manufacturing Error Message
Our company is encountering several problems with our GP - Business Ready 9.0 deployment. 1 - The Item Engineering Data window in inaccessable with the error message "Manufacturing Module Inventory not registered. Please contact Sys Admin or your Microsoft Business Partner." Our MBS advised that if the option is selectable (not greyed-out) then the feature is registered and should be functioning. The window is accessable through Fabrikam. I searched through the knowledgebase for similar occurances but nothing has resolved our issue. 2 - Users created with the instruction ...

Outllok Today doesn't show message count
The last time Outlook hosed over my PST file, even though I've created a completely new one, my "Outlook Today" page won't show the number of messages in any folder that include on the page. I'm using Outlook 2002 (10.4712.4219) SP-2. Any ideas? -- Brian Tillman Internet: Brian.Tillman at smiths-aerospace dot com Smiths Aerospace Addresses modified to prevent SPAM. 3290 Patterson Ave. SE, MS 1B3 Replace "at" with "@", "dot" with "." Grand Rapids, MI 49512-1991 This opinion doesn't represent that of my company ...

Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to 1. copy range A1 to E1 to every row where the word "Header" is in colmn F. 2. Then copy paste the whole sheet as values. 3. Then the range now standing left of "header" must be copied to the empy cells beneath each heading. For example a b c d e f 1)12/12/2005 F001 SAO3 1 CCE Header 2) Detail ...

office 2010 outlook meeting request
Hi, Has anyone encountered not being able to display meeting requests in Outlook 2003. I'm still on SBS2k3 and Office2k3, i downloaded the compatibility updates but it was for 2007. Any ideas will be great. Thanks April Hi April, Ok is this Outlook 2010 or Outlook 2003? (Because Subject says office 2010 and content says outlook 2003?) We would need more details on if you think this is related to SBS2003? If you think this is an Outlook issue This might be a better forum. http://social.answers.microsoft.com/Forums/en-US/category/officeoutlook If this is a SBS rel...

Messaging Interface Error #3
When I try to delete a message from my inbox, I get the following error message "The messaging interface has returned an onknown error. If the problem persists, restart Outlook." I have restarted Outlook a number of times, with no result. My Deleted folder is empty. All help will be greatly appreciated. ...

Base combo box off of yes/no
Hello, I have a combo box that displays certain task for a company. Each task is primarily specific to a certain department, however, one department may be able to do the tasks of another. For instance a person in the office can do drafting and may make molds. While someone in the shop can make molds but would never do drafting. The way i was thinking about setting this up was have a table with a field that lists the task then in the fields next to it list my departments such as accounting, engineering, shop, etc with a yes or no box. so for instance, say i list Task = Mold Making,...

Counting with Date Range as Criteria
A B 1 Date Successful 2 10/1/09 Y 3 10/1/09 N 4 10/5/09 Y 5 11/3/09 Y I want to put a formula into a worksheet called "Analysis" to count the number of Y (successful) records for a given date range in a worksheet called "Master". So, I want the Anaylsis worksheet to show me "2" when I ask for the number of Y's in column B for the date range 10/1/09 to 10/30/09 in column A. See my ans to your PREVIOUS post -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Julie&q...

Removing certain things from 1 column
I can't figure out how to remove a specific thing from my column.. have mailing addresses with number and street name in column a, cit and state in cloumn b and mailing zip in column c... The zip cod came in in zip+4 format... so it reads 91206-4695. The entire coloum is like that and I wanted to know i there was any way to remove the -4695 through out the records. Not al the of the zip codes are the same but they all contain the - with numbers after. Is there any way to get rid of this -- ABASSANETT ----------------------------------------------------------------------- ABASSAN...

How do I add Bcc: line to Outlook messages?
I can't find out how to add a Bcc: line to my email messages. Any help would be greatly appciated. Thanks in advance, George the good. View-> BCC field with Outlook as the editor Little down arrow on the Options button with Word as the editor -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "George the good" <santulli@ix.netcom.com> wrote in message news:1e15401c4548a$c776acc0$a001280a@phx.gbl... >I can't find out how to add a Bcc: line to my email ...

Comination of 3 Combo boxes fills Text box
Hi, I have a subform 'OrderDetailsSubform' (datasheet view) on which I have 3 combo boxes - cboCategories, cboSizes, cboProducts. They are all synchronised to filter down by selecting Product Category, Product Size, then the Product itself. The subform's controlsource is a query OrderDetailsExtended. The next field is an text box txtUnitPrice. I want this to populate with the price for the product selected in the combo boxes. I am stumped however, as the ProductID (which is what would distinctly identify the product and the price) isn't actually selected in any of ...

counting based on another criteria
I have two columns, one contains a list of job titles the other a lis of responses to a question: A B Clerk Clerk Analyst Clerk Analyst Enginee -- Message posted from http://www.ExcelForum.com Hi and what do you want to count?. You may have a look at the COUNTIF function or SUMPRODUCT. >-----Original Message----- >I have two columns, one contains a list of job titles the other a list >of responses to a question: >A B >Clerk >Clerk >Analyst >Clerk >Analyst >Engineer > > >--- >Message posted from http://www.ExcelForum.com/ > >. &...

Import messages over network
I need help with information regarding import or export messages over a LAN from Outlook Express 6.0 into Outlook 2000. Thanks, Robert First copy the OE6 files to the other computer, and import them into OE there (if you need help with this step, check here: http://insideOE.tomsterdam.com). Once that's done, you can export from OE to Outlook on that computer. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Robert" <dersorob@att.net...

Slow down checking for new messages
Gurus, Running Outlook 2003 in Corporate mode. Exchange 2003 server on the back-end. Is there anyway to make my Outlook client check for new messages only like say, every 30 minutes? I am trying to organize my time better and I find that email being delivered to me as soon as it is sent is a distraction. -- Spin No, but you can impose some self-discipline. Set a time each hour when you will deal with incoming mail such as on the half-hour. If there is no need to respond immediately, then don't - keep working on the task at hand until half past the hour. -- Milly Staples ...

Any send / receive email message is in Text mode Only
Hello, I have an issue that I have been unable to resolve. I have Microsoft Outlook 2002 (with Service Pack 3)and any message that I send and for the most part any message that I receive are in Text Mode Only even so I have configure Outlook 2002 to send new emails in Rich Text (I also try HTML but it doesn't make any difference). BTW, I have installed every Microsoft XP critical patch and every update for Microsoft Outlook 2002, I have even look in the knowledge base but I can resolve this issue. Finally, I would appreciate some advice as to how can I resolve this issue and I t...

How do I "redirect" a message?
Where is the option for redirecting a message (that is, forwarding a message while retaining the sender's address)? This is called REDIRECT in eudora and other mailers. Then the recipient answers it, the answer goes to the originator and not myself. Thanks. George George in Seattle <anonymous@discussions.microsoft.com> wrote: > Where is the option for redirecting a message (that is, > forwarding a message while retaining the sender's > address)? This is called REDIRECT in eudora and other > mailers. Then the recipient answers it, the answer goes > to th...

Outlook 2007 crashes on certain email
I'm running Outlook 2007 on Windows 7. It's been running just fine until today. Today I received an email from a known/trusted source (an online purchase confirmation). When I attempt to open the email, Outlook crashes. Same thing with the Reading Pane with this email selected. No other emails seem to explerience this issue. The error in the event log reads: Faulting application name: OUTLOOK.EXE, version: 12.0.6514.5000, time stamp: 0x4a89dc70 Faulting module name: wwlib.dll, version: 12.0.6514.5000, time stamp: 0x4a89ddc8 Exception code: 0xc0000005 Fault offse...

Invalid format message at startup
Excel 2000. When I open it there's a message saying a logo file in Publisher is an invalid file format. I created the file earlier today, in Publisher, but there's no connection to Excel. I checked all the settings and file properties, including book1.xls, I could think of to make sure. The nessage doesn't come up in Word, only Excel. Click it off and it goes away until the next time I open Excel. There are no startup macros or anything I can see that would look for this file. Any ideas much appreciated. The file may be in your XLStart directory; XL opens/attempts to op...

How to count rows that match two criteria? #2
Hi! I hope the title uses the right terminology, but here's what I am trying to do (with greatly simplified example). Single workbook. Excel 2002. I'm using two worksheets, but the same could apply within a single worksheet. In worksheet CCC, column C contains the list of valid strings for the AAA!A cells. Each string appears once and only once. On worksheet AAA, each cell in column A might contain any ONE of several strings (MMM, OOO, PPP, etc) or it might be empty. There may be several of each string in this column, and some strings may not appear at all. On the sa...