Searching for more than one record on another table

[pdauction]

Hi, I hope this is as clear as possible;

I have a multi tab form called 'Life Claims Form' that has a date on it called date of death from a table called 'Life Claim details'.

On a separate Tab on the same form I have a subform called 'Life Benefit Subform new' that has a field called 'Benefit Start Date' from a table called 'Life Benefits'.

The two tables both have a common field called 'Policy Number'. Within the 'Life Benefits' table it is possible to have multiple records per 'Policy Number' and therefore different 'Benefit Start date's.

I basically want to run an 'after update' query on the form that once the user has keyed a date in a field called 'Date of Death' it looks at all relevant records in the Life Benefits table and warns of any 'Benefit Start date's that are before the one the user has typed in.

I presume the vba should look through and check the table using a for each type command but I am at a loss how to do this.

Any help is greatly received.


For info I did get this to work for just checking one record as follows;

[COLOR=black][FONT=Verdana]Private Sub Date_of_Death_AfterUpdate()
err1 = ""
err2 = ""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Date_of_Death.Value < Policy_Start_Date.Value Then
    err1 = "Date of Death is before Policy start date."
End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Date_of_Death.Value < [Forms]![Life Claims Form]![Life Benefit Subform new].[Form]![Benefit Start date].Value Then
    err2 = "Date of Death is before Benefit start date."
End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If err1 <> "" Or err2 <> "" Then
MsgBox err1 & vbNewLine & err2, , "Warning"
End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]DoCmd.RunMacro "Life - Update calculated fields.Update year and age at death"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

Many Thanks
Paul

 

[jzwp22]

You could use a DCount() function in an IF..THEN


IF DCount("*","[Life Benefits]","[Benefit Start Date]< #" & me.[Date of Death] & "# AND [policy number]='" & me.[policy number] & "'") >0 THEN
msgbox "Benefit Records with an earlier start date than the date of death exist"
END IF

The above DCount() assumes that form on which the date of death is entered also has the policy number and that the form control names are the same as the field names. Further it assumes that the date of death field in the Life Benefits table is a date/time data type and that the policy number is a text data type.

 

[pdauction]

Firstly, thanks very much for spending time to understand my request.

Secondly, thanks for answering my query, the solution works great. I just changed the policy number bit as its actually numeric and changed the comparison operator from > to <.

Solution below;

If DCount("*", "[Life Benefits]", "[Benefit Start date]> #" & Me.[Date of Death] & "# AND [Policy Number]= & Me.[Policy Number] &") > 0 Then
    MsgBox "Benefit Records with an earlier Start date than the Date of Death exist", , "Warning"
End If

Thanks again
Paul

 

[jzwp22]

If DCount("*", "[Life Benefits]", "[Benefit Start date]> #" & Me.[Date of Death] & "# AND [Policy Number]= & Me.[Policy Number] &") > 0 Then
MsgBox "Benefit Records with an earlier Start date than the Date of Death exist", , "Warning"
End If

I'm not sure why you changed the inequality (shown in red above) considering what you said you wanted to do below:

I basically want to run an 'after update' query on the form that once the user has keyed a date in a field called 'Date of Death' it looks at all relevant records in the Life Benefits table and warns of any 'Benefit Start date's that are before the one the user has typed in.

 

[pdauction]

Yep my bad, apologies.

 

[jzwp22]

No problem.