Filter a report

[RonMcIntire]

All:

I am developing a directory using using a query with a FamilyRec table linked to an IndivRec table. The IndivRec table includes a FamRelation field (Head, Spouse, Child, etc) that I'm filtering for each printed field.

I wish to print the directory of each family with Head, Spouse and Children. I understand how to filter varous fields with a recordset of the query in the underlying module of the report but I don't understand how to filter for each FamilyID record. I'm using the Format property in the detail section of the report.

Can anyone help?

Thanks,

Ron

 

[rjoubert]

I may be misunderstanding what you're attempting to do here, but can you just add a group to the report to group by the FamilyID?

 

[dhookom]

I am having trouble understanding what you are attempting to do.

I understand how to filter varous fields with a recordset of the query in the underlying module of the report

doesn't make much sense. You don't need code to filter a report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RonMcIntire]

All:

I'm sorry but in an attempt to make my question as short as possible, I muddied the water some. Let me start over.

I have a query with two tables: FamilyRec and IndivRec joined in a One-To-Many relationship. The IndivRec table includes a FamRelation field that will contain either Head of Houshold, Spouse or Child. Thus, one record will have the name of the HOH, one record the name of the Spouse and any number of records the names of each child.

I want my printed directory to have the following layout:

FamilyID
FamilyName
HOH Name:
Spouse Name:
Addr:
City, ST ZIP:
HPhone:
Children: (Child1; Child2; Child3, etc)

In the query, I can set the critera of the FamilyID to one FamilyID number to show all the members of that family easy enough. In the code, I can create a recordset to build the appropriate HOH, Spouse and list of children fields by using a series of IF statements in a DO LOOP to pick out the data I need.

The problem is that this method collects data on the entire query rather just one FamilyID number. I don't understand how to filter for each familyID.

I am running the code from the Format property in the detail section of the report. Should I use the Print property instead?

Hope that's a bit clearer.

Ron

 

[dhookom]

If you are running code, it might help if you showed it to us. If I understand correctly, you want to concatenate all the children names together. There are FAQs in either this forum or the query forum that describe how to do this. There is also a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RonMcIntire]

Duane:

You're right but I have no problem generating the children's names. Its when I want to generate ONLY the children's names for FamilyID 1005 that I run into trouble.

Here is my code.

Option Compare Database
Option Explicit

' This routine picks the HOH, Spouse and children names from the recordset
' sets the HOH and Spouse names into an unbound textbox name then generates
' a list of childrens names and sets them into a Childrens names textbox.

' There may be better ways but I use a recordset because I understand
' how it works.

'PROBLEM: THE ROUTINE COLLECTS ALL THE CHILDRENS NAMES FOR THE QUERY AND
' SETS THEM INTO THE CHILDRENS NAME TEXTBOX THEN PRINTS THEM FOR
' EVERY FAMILY RECORD.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Dimension the variables
Dim db As DAO.DATABASE
Dim rs As DAO.Recordset

Dim vChildStr As String
Dim vHOHStr As String
Dim vSpouseStr As String

' Open the Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblIndivRec", dbOpenDynaset)

'Initialize Family Relation Strings.
vChildStr = ""
vHOHStr = ""
vSpouseStr = ""

' Find the Head of Household and Spouse
' Move to the first record in the recordset and search for the HOH and Spouse names.
rs.MoveFirst
Do
If rs("FamRelation") = "HOH" Then
vHOHStr = rs("HOH")
End If

If rs("FamRelation") = "Spouse" Then
vSpouseStr = rs("Spouse")
End If
Loop Until rs.EOF

'Generate the string of Children.
' Move back to the first record then search through the recordset again.
rs.MoveFirst
Do
If rs("FamRelation") = "Child" Then
vChildStr = (vChildStr & rs("FName") & "; ")
End If
rs.MoveNext
Loop Until rs.EOF

'Close the recordset and the DAO database
rs.Close
db.Close

' Set the HOH and Spouse variables into the appropriate textbox names
nHOH = vHOHStr
nSpouse = vsphousestr

' Remove the semicolon from the last child name and set the
' variable into the nChildren textbox.
If Len(Trim(vChildStr)) <> 0 Then Me.nChildren = left(vChildStr, Len(vChildStr) - 2)

End Sub

 

[dhookom]

You could use my generic function in your query. Also, in your code, you could be opening a record set based on a sql statement rather than the entire table.

  Dim strSQL As String
  strSQL = "SELECT * FROM tblIndivRec WHERE FamilyID=" & Me.txtFamilyID
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[RonMcIntire]

Duane:

Thanks, that just may do the trick. It was what I was thinking about but didn't know how to do it. Thanks for your help. I'll give it a shot and see what happens.

Ron

 

[RonMcIntire]

Duane:

Two more questions.

1. Should I be using the On Print property rather than the On Format property?

2. Will your SQL suggestion go from one FamilyID record to the next automaticlally resulting in a complete directory?

Thanks,

Ron

 

[dhookom]

I rarely if ever use code in a report to do stuff like this. If I knew the table structure and had some sample data, I could probably tell you how to get rid of all the code in your report and build your expressions in the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]