Get extra records into Datasheet

[groengoen]

I have a database table which has Client Insurance policies. Each policy has the possibility of 2 ID'S (eg. husband and wife). To query this database I have a form with a list control for the Names, Policy Number, ID1, ID2 etc... and a subform which is a datasheet. When I query a policy, the subform datasheet displays all the policies with ID1's = to the selected ID1.

I want to also include on this datasheet all the policies with the this ID1 value in the ID2 position. I have tried the following code in the AfterUpdate event of the subform, with no result:

Private Sub Form_AfterUpdate()
Dim C As Recordset
Set C = Screen.ActiveForm.RecordsetClone
CritText = "ID1 = " & Chr(34) & [ID2] & Chr(34)
C.Find CritText
If Not C.NoMatch Then
[FormNo] = C![FormNo]
[Cover1] = C![Cover1]
[Name2] = C![Name2]
[Cover1] = C![Cover1]
[Cover2] = C![Cover2]
[Company] = C![Company]
[Policy_No] = C![Policy_No]
[Start_Date] = C![Start_Date]
[Term] = C![Term]
[Class] = C![Class]
[Premium1] = C![Premium1]
[Premium2] = C![Premium2]
[ID1] = C![ID1]
[ID2] = C![ID2]
Else
DoCmd Requery
End If
End Sub

Does anyone have any ideas on how to get these secondary ID's onto the datasheet,

Thanks

Groengoen

 

[Sammy B]

secondary ID's

Groengoen,

Am I right in assuming that you have Insurance record with 2 ID's pointing to a people file? Or is there 2 separate policies for the husband wife example?

Sam

 

[groengoen]

I have in insurance policy with 2 ID's, ID1 and ID2, and the husband could have a number of policies with ID1 set to H001 (say) and the wife's id (W001 say) could be on the husband's policy as ID2. The wife could have policies in her own name with ID1 set to W001 and the husband could be included as ID2 on her policies: eg

ID1 ID2 Name1 Name2 Policy
H001 W001 Husband Wife 1
H001 Husband 2
H001 Husband 3
W001 Wife 4
W001 H001 Wife Husband 5

etc.

What I have in my Connection between the Master form and the Child subform is

Master field Child field
ID1 ID1


What I am trying to get is something like:

Master field Child field
ID1 ID1
ID1 ID2

But if I just try that I get no corresponding records.

What I want to see is that if I select a policy, eg H001 it would pick up all the H001's including those in the ID2 field i.e if I look at policy 1 on the main form I should see 1, 2, 3 and 5 on the subform, i.e all the policies relating to the husband, including the one taken out in his wife's name.

Thanks for having a look at my problem

 

[Sammy B]

Are you using Access 2000?.. If so I will do up a dummy db and show you how I would handle this. (That is if I can get it to work!)

Sam

 

[groengoen]

I am on Access 2002, but I think a solution for 2000 should also work?

 

[Sammy B]

Ok, I did up a dummy db. As you can see from the query you are now able to select from 'Families'. You could tie the query into a drop box on a form or create a subform that used the FamilyID as a join.

Let me know if this works for you.

Sam

 

[groengoen]

Thanks very much! I will have a look and see whether I can put it into action

 

[groengoen]

Your idea would work ok, but I am trying to do it using just the policy table. I don't want to have to generate a family table, as this would mean a whole system of updating this, which doesn't exist at present. Thank you for the idea, however, and maybe I can adapt it.

Cheers,

Groengoen