Validating data in a subform against data in main form

I have a main form with a subform.  Each form is bound to its own table but 
are linked through a primary key (customerID).  
On the main form, I have a customer's date of birth (dob).  
On the subform, I have the customrer's date of order (doo).
Basically, I want to validate that the date of order entered by the user is 
later than the date of birth.

How can I do this?  I tried to do it in the subform's BeforeUpdate event but 
it failed.  It didn't seem to recognize dob (customer's date of birth).  
Perhaps, I was not referring to dob properly.

I'm new to Access and grateful for your help.  I hope my question is clear 
and didn't confuse anyone.  Thank you so much.
0
Utf
2/14/2010 8:37:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

5 Replies
1183 Views

Similar Articles

[PageSpeed] 9

Angela wrote:

>I have a main form with a subform.  Each form is bound to its own table but 
>are linked through a primary key (customerID).  
>On the main form, I have a customer's date of birth (dob).  
>On the subform, I have the customrer's date of order (doo).
>Basically, I want to validate that the date of order entered by the user is 
>later than the date of birth.
>
>How can I do this?  I tried to do it in the subform's BeforeUpdate event but 
>it failed.  It didn't seem to recognize dob (customer's date of birth).  
>Perhaps, I was not referring to dob properly.
>
>I'm new to Access and grateful for your help.  I hope my question is clear 
>and didn't confuse anyone.


If Me.doo < Parent.dob Then
	MsgBox " . . .


-- 
Marsh
MVP [MS Access]
0
Marshall
2/14/2010 10:25:46 PM
On Sun, 14 Feb 2010 12:37:01 -0800, Angela <Angela@discussions.microsoft.com>
wrote:

>How can I do this?  I tried to do it in the subform's BeforeUpdate event but 
>it failed.  It didn't seem to recognize dob (customer's date of birth).  
>Perhaps, I was not referring to dob properly.

That would seem to be the case. Please post your code so someone who can't see
your screen might be able to help fix it...
-- 

             John W. Vinson [MVP]
0
John
2/14/2010 10:32:09 PM
Thanks in advance for your help.  Below is part of the code.  It's still not 
working unfortunately.  I get an error at the line containing the name of the 
main form.

The name of the main form is: z frm 1abc test.

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If IsNull(doo) = False Then
        If Me.doo <= [z frm 1abc test].dob Then
            msg2 = msg2 & "Date of purchase must be later than date of 
birth" & vbCrLf
        End If

   End If
   
End Sub

"John W. Vinson" wrote:

> On Sun, 14 Feb 2010 12:37:01 -0800, Angela <Angela@discussions.microsoft.com>
> wrote:
> 
> >How can I do this?  I tried to do it in the subform's BeforeUpdate event but 
> >it failed.  It didn't seem to recognize dob (customer's date of birth).  
> >Perhaps, I was not referring to dob properly.
> 
> That would seem to be the case. Please post your code so someone who can't see
> your screen might be able to help fix it...
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
2/14/2010 11:52:01 PM
On Sun, 14 Feb 2010 15:52:01 -0800, Angela <Angela@discussions.microsoft.com>
wrote:

>Thanks in advance for your help.  Below is part of the code.  It's still not 
>working unfortunately.  I get an error at the line containing the name of the 
>main form.
>

Either use the Parent! object to get the current subform's parent form, or use
the Forms! collection:

   If IsNull(doo) = False Then
        If Me.doo <= Parent![dob] Then
            msg2 = msg2 & "Date of purchase must be later than date of 
birth" & vbCrLf
        End If


or


   If IsNull(doo) = False Then
        If Me.doo <= Forms![z frm 1abc test].dob Then
            msg2 = msg2 & "Date of purchase must be later than date of 
birth" & vbCrLf
        End If


-- 

             John W. Vinson [MVP]
0
John
2/15/2010 1:40:05 AM
It's working now!!  Thanks so much John & Marshall.  I appreciate it!

"John W. Vinson" wrote:

> On Sun, 14 Feb 2010 15:52:01 -0800, Angela <Angela@discussions.microsoft.com>
> wrote:
> 
> >Thanks in advance for your help.  Below is part of the code.  It's still not 
> >working unfortunately.  I get an error at the line containing the name of the 
> >main form.
> >
> 
> Either use the Parent! object to get the current subform's parent form, or use
> the Forms! collection:
> 
>    If IsNull(doo) = False Then
>         If Me.doo <= Parent![dob] Then
>             msg2 = msg2 & "Date of purchase must be later than date of 
> birth" & vbCrLf
>         End If
> 
> 
> or
> 
> 
>    If IsNull(doo) = False Then
>         If Me.doo <= Forms![z frm 1abc test].dob Then
>             msg2 = msg2 & "Date of purchase must be later than date of 
> birth" & vbCrLf
>         End If
> 
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
2/15/2010 2:29:01 AM
Reply:

Similar Artilces:

Msgbox entry validation
The following Macro will not loop more than twice the error entries. Will someone help me correct it to loop indefinely? Thanks Sub Entry_validation() Dim Teststring As String Dim Inputstring As String Cells(1, 1) = "ABC" 'as given filename Cells(2, 1) = "DEF" 'as given filename Cells(3, 1) = "GHI" 'as given filename Cells(4, 1) = "JKL" 'as given filename errorloop: Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort to abort en...

Data Validation -> Validation list is larger than the cell width
I have a workbook that I've just added data validation to one cell. FWIW, the data validation is based on a named range. Here's the (perceived) problem. When I've set up data validation in the past, the selection is the same width as the cell. In this case, the list starts almost a full cell width to the left. What am I missing? Thanks, Barb Reinhardt Barb, In Excel2003 I can replicate what you describe under the following conditions: 1. the named range contains entries that are wider than the data validation cell 2. The data validation cell is r...

Activated Outlook and lost all data on Outlook Express
When I downloaded Windows XP Patch 2 it included Outlook. I installed the patch and activated Outlook. My old Outlook Express disappeared, along with a lot of files that I hadn't read/responded to/saved. Is Outlook Express still somewhere in my computer system? How do I access it to review and save some of the files that were there? -- TeacherGuy If you use outlook 2003, try to import data from outlook express to outlook 2003. from menu of oulook 2003, file > import & export > import internet mail and addresses > Outlook Express 4.x, 5.x, 6.x "TeacherGuy"...

Validating against a DTD
Hello All, I am trying to validate an xml file against a DTD. I went through a lot of examples especially: http://www.xmlforasp.net/codebank/util/srcview.aspx?path=../../codebank/System_Xml/XmlValidatingReader/Validator/validator.src&file=validator.cs&font=3 but its not working in my case. First of all, when i try to add my DTD file in XmlSchemaCollection object, it throws an error saying DocType element is expected. I am using NITF DTD for this and there is no DocType element in it. Any help will be appreciated. Thanks. VD You should not add DTDs to XmlSchemaCollection. XmlSchema...

Clearing the data in the socket..
Hai.. Can anyone tell me how to clear the data in the socket after receiving it?? Urgent... Thanx in advance.. "Wallace" <princevictor.moses@gmail.com> wrote in message news:1140602678.861429.68730@o13g2000cwo.googlegroups.com... > Hai.. > > Can anyone tell me how to clear the data in the socket after > receiving it?? //... static const unsigned nRead = (10*4096); BYTE pTmp[nRead]; memset( pTmp, 0, nRead ); int nActual = Receive( pTmp, nRead ); //... I am not sure I understand, calling Receive(...) will remove the data. if the data is greater than (10...

Validation dropdown list is not coming visible
Hi, I'm using XP and 2003. I have a problem that I have once found resolution, but now it came again and cannot remember what needs to be done. Here is the problem: There is a column where Validation is specified in right manner with error message in case of error. When selecting the cell - dropdown list is not coming visible. In case I write something wrong it gives error message. If I recall correctly - it was something to do with some kind of list. How can I change this to work properly? I tried to paste new column from another file where the column is working as I want...

Data Migration Framework Error
Hi All, Im having trouble importing email activites into MS CRM. When running the migration tool , Im getting the following error in the logs for every email I've set up as an activity. I've checked the guids for the owninguser and the objectid (contact), and they all exist. Im not creating any associated records in the cdf_ActivityParty table, as I only want to store the link for the "regarding" item so to speak. Thoughts anyone? Regards, Cameron 21/10/2004 - 8:43:18 AM -- [ERROR] Failed to migrate object: crmActivityUnknownError 21/10/2004 - 8:43:18 AM -- <a...

irritating warning for negative data on log charts
I know perfectly well that I can't plot negative values on a log chart. That doesn't mean that my data doesn't have negative numbers in it. Is there any way that anybody knows to disable this irritating message? My productivity drops 100% because every time I scroll around in a sheet that happens to have a negative number on a log chart somewhere, this irritating dialog pops up. I don't want to have to build my own log charts that ignore negative numbers (I could, but what's the point of a built-in log chart if you can't use it). I don't want to have to w...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Error on filtering data in a from
Hi all, I have a form that displays dates in week, montt, quarter and year format. I use Week: Format([DateCreated],"ww") in the query behind the form. In the form footer i have a text field that performs the Sum of control [fruits] However... When i choose to use the default filter function (right click select etc) and filter all records by week or month etc ...i receive this error message: “The object does not contain the Automation Object ‘ isNull’” Why? All suggestions are appreciated, Thanks! ...

How to find out repeated data
Can anybody tell how we can find out any repeated data in the worksheet easily and then delete it. "kingkingking" <kingyin97@yahoo.com.hk> wrote in news:u5NTxyVoIHA.4760 @TK2MSFTNGP06.phx.gbl: > Can anybody tell how we can find out any repeated data in the worksheet > easily and then delete it. > how is your data setup now? what version of excel are you using? if its all in 1 column in excel 2003 or earlier - highlight your data - click data - click filter - click advanced filter - click unique records only checkbox and ok - this will filter all your duplicate...

unable to enter numerical data in cells
I Have just activated excel 2003. I am unable to enter data into any cells. I am also unable to enter information into microsoft word. How do I rectify this problem? I am familiar with excel 2000 and never encountered any problems. What happens when you try to enter data in cells or in Word? Error messages? Nothing? From where did you install MS Office 2003? Retail CD? Trial CD? Gord Dibben MS Excel MVP On Fri, 7 Jul 2006 08:35:02 -0700, Genek185 <Genek185@discussions.microsoft.com> wrote: >I Have just activated excel 2003. I am unable to enter data into any cells....

Spreading data accross multiple records
I have a date that I need to apply to all of the null feilds in a table. There are over 3,000 and trying to select the nulls in a query and then manually typeing in the same date on each one would be tedious. Is there anyway to apply a date to all of the null feilds in a table? I just don't want any of the records to have that feild blank. your help would be much appreciated. William UPDATE tblNulls SET tblNulls.Dates = #1/1/2008# WHERE tblNulls.Dates Is Null; Replace tblNulls.Dates with the actual table and field names. You would do such an update query for each column/field in t...

Migrate CRM Data from CRM Server to another CRM Server
we have a staging and a production CRM server and i'd like to migrate the Production server data to the staging server database. Can anyone recommend a tool or process that i can that doesn't involve rebuilding the staging server? thanks Please ignore - looks like Data Migration Framework is the way to go, based on other posts "Eve" wrote: > we have a staging and a production CRM server and i'd like to migrate the > Production server data to the staging server database. Can anyone recommend a > tool or process that i can that doesn't involve rebuildi...

data labels in 2007
In 2003 I used to be able to hover over a data point on a chart and it would give me the value... in 2007 it seems I am not able to do that... is that correct? how can I see the data point value on my chart without double clicking on it... want to just hover over it Apparently it doesn't work unless you activate the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "John" <John@discussions.microsoft.com> wrote in message news:BB7B2633-5061-477B-B5FC-EC6F6B10BEAB@mic...

display data as a percentage of a subtotal in excel pivot table
how can data be displayed as a percentage of a subtotal in a pivot table? for example, I can display salesperson A's 1st quarter sales as a percentage of yearly sales. What I want to do is display salesperson A's 1st quarter sales as a percentage of quarter 1 total sales. You can add columns to your data table, and calculate the quarter, and the percent of quarter total for each row. For example, with your data in cells A1:C200 -- Salesperson Date Sales Sam 1-Jan-05 200 In cell D1, type: Qtr In cell D2, type: =CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4) In ce...

reinstall-validation problem
Hardware hit from lightning so new hd and video card and start over. All goes well with install using a slipstreamed XP-Pro SP3 disk but I notice it never asks for the Product Key during install yet it Activates itself during one of the boots and I think little of it until ... During the updates which followed (100+!!!) I notice Windows Defender doesn't seem to run or leave itself installed. As it is running on my laptop figured I just download it. When I run it, it wants to first do the Genuine Advantage bs which I let it, at which time it says "bogus". Ugh, g...

afxdb.h CDBVariant, new string data types?
We make software that uses ODBC to import data from databases and we have recently found out that we can no longer import strings from Access databases made with Access 2003. Environment: Visual Studio 2003 MFC Application on Windows XP. When running the code in a debugger it turns out that our code that was using the CDBVariant data type DBVT_STRING to handle strings has stopped working. Fields with text in the database no longer are of the type DBVT_STRING, but instead two new data types are used: DBVT_ASTRING and DBVT_WSTRING. When looking at the CDBVariant in the debugger it seems like yo...

#DIV/0! Is there a way to suppress this display when no data is entere?
I'm using Excel to track my expenses so I can find ways to reduce them. I enter the data from all my retail receipts into a spread sheet that includes columns to compute the "price per unit" for some items. This is very handy for comparing the per dose cost of medications, or the per ounce cost of items in different sized containers. My approach is very straightforward; I've set up three columns like this: Price Units Unit Price In the "Unit Price" cells I have the formula =sum(Price/Units) But now all the cells in the "Unit Price" column display ...

File - Get External Data - Import - Text Type - Advanced
File - Get External Data - Import - Text Type - Advanced - Specs PROBLEM Can anyone assist me with a problem ? When attempting to specify an import specification using ; File - Get External Data - Import - Text Type - Advanced - Specs Is it possible to specify a Spec layout, save it and then - at a later point - amend the Spec ? If anyone could assist, I would be very grateful. Kind regards, Michael Sure. Just import a sample text file and follow the steps you outlined! Be sure to click SaveAs... when you are ready to save it. Then give it a name. -- Joe Fallon Access MVP "...

Data change as scrolling
Hi, Just been told how to use Freeze pane option so my days of the week scroll down the worksheet. My A column has the months in it. I.E I have January in A1 and April in A10. B1, B2 etc has the days of the week. How do I get the A1 cell to change to a different month as the row scroll down to it? Thanks Have you thought about designing the w/s differently? If you list all months in col a a1=Jan a2=Feb etc then have the days in b,c,d etc The way I understand it your sheet will have all the data in cols a & b. It won't look pretty and by my suggestion you should ...

Data into an Excel Form
I created a form in Excel 2 fields are intended to capture Employee ID# and the other Employee Name (no need for separate fields for First & Last). I have a list of names & ID# also in Excel which I need to create 200+ individualized forms. I would like to generate the forms without having to type the Names & ID# into each field. Is there some sort of merge feature I can use that will save me time? Any solutions will be greatly appreciated. Depending on what this is for, but if it is for print then I'd make the form in Word and merge using the Excel list as data s...

Validation
Julie the same can be achieve without going thru the double drop down boxes. You could do it just using the combobox alone. Thank again Please stay in the ORIGINAL thread. The archives will thank you. -- Don Guillett SalesAid Software donaldb@281.com "JLong" <anonymous@discussions.microsoft.com> wrote in message news:034101c49c3b$889b5020$a501280a@phx.gbl... > Julie the same can be achieve without going thru the > double drop down boxes. You could do it just using the > combobox alone. Thank again ...

Data Validation
I have set up a data form and have data validation rules on the various input cells. I see that there is an option called "Ignore blanks" in the data validation settings, which is supposed to restrict someone from entering past an input cell without entering valid data, if the "Ignore blanks" box is unchecked. I have tried to do this, but it does not seem to work. Please could someone tell me what I am doing wrong? Provide information, or better yet, a screen capture of the Data Validation dialog box with the Setting tab selected (User ALT+PrintScreen to copy the imag...

validating macro
Hi I am sending a spread sheet out to branchs I need them to complete a sheet (common info) before they do anything else on the workbook. I am looking for a macro or code for VBA which who check (common info) see if the cells are complete if not bring up a error screen informing the user to complete the form and then take them straight to the (common info) sheet. and if possible let them go to the tab which they want to go to. Use an extended version of something along these lines: (if there are lots of cells use a 'for row numbers x to y' or 'do until' statment, o...