Creating a directory

[OldManEd]

I have 2 related tables. One is for Groups with fields like name, address,
town, etc. The second table has records for individuals in each group (3-5
members in each group). The tables are related by IDs for the Groups and
individual IDs for the members.

I want to create a report with two columns.

Group Name
Group Address
Group Town/State/Zip
Group Phone
Member#1, Member#2, Member #3, Member #4, Member#5

I tried to use a query for both tables but got up to 5 records for each
group. .I then create a report with the group data in a 2 column report base
on data from only the Group table. Same as first 4 lines above.

Can someone suggest how to add the member's names from the "individual"
table to this report?

Thanks,

ed

 

[Allen Browne]

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

You will need some understanding of VBA code to do this.

 

[OldManEd]

Allen.
Please explain how this works. I notice this is a function, not a sub
procedure. How/where is it called?

Do I created an unbounded control (for Member#1) and 4 more and somehow call
this function to fill each box? I tried something similar but there were no
events to trigger the VBA code I want to use which would open the second
table and find a record/name.

I'm somewhat above a beginner but this one stumps me.

Thanks,

ed

 

[Allen Browne]

The function needs to be pasted into a module.
You can then call it like Date() or any of the built-in functions.

If you have never worked with VBA code, this may be beyond you.

 

[OldManEd]

My VBA experience is limited but I don't think that's my problem. My problem
is triggering the function. Can I....

Create a module and write (paste seems simpler!) this code (modified for my
'individual' table)into it. Then create an unbounded control and have
"=Name()" as the Control Source??? Can I duplicate this for names #2 to #5?

In another report I use the On Format event for the Detail Section to
trigger an option to add a border to a field's value "if IsNull" is true or
no border if false. Would this On Format event work here?

Ed

 

[Allen Browne]

Yes: you can use the Control Source of a text box. Something like:

=fConcatChild("tblPerson", "tblGroupID", "TheName", "Long", [PersonID])

If you prefer, you could place the expression in teh Field row in query
design.

The specifics will depend on how your group and person tables are connected,
but I'm assuming that one group can contain many persons.

 

[OldManEd]

Allen,
I tried to adapt this code to my database. I want to pass a GroupID to the
second table to created the string of names. I think my SQL statement is
much simpler than the one in the Northwind example.

I'm having trouble with two lines:

set db=CurrentDb 'Is db the whole database file? Does it matter if no
tables, queries, reports are open. I've been working only in VBA editor.

Also, I have replaced
set rs=db.OpenRecordset(strSQL, dbOpenSnapshot) from the example

with

set rs=db.OpenRecordset("Select * from tblIndividuals")

but this doesn't work. Is dbOpenSnapshot needed? I tried it and still get
hung on this line.

Yes: you can use the Control Source of a text box. Something like:

=fConcatChild("tblPerson", "tblGroupID", "TheName", "Long", [PersonID])

If you prefer, you could place the expression in teh Field row in query
design.

The specifics will depend on how your group and person tables are
connected, but I'm assuming that one group can contain many persons.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My VBA experience is limited but I don't think that's my problem. My
problem is triggering the function. Can I....

Create a module and write (paste seems simpler!) this code (modified for
my 'individual' table)into it. Then create an unbounded control and have
"=Name()" as the Control Source??? Can I duplicate this for names #2 to
#5?

In another report I use the On Format event for the Detail Section to
trigger an option to add a border to a field's value "if IsNull" is true
or no border if false. Would this On Format event work here?

Ed

 

[Allen Browne]

Sorry: I don't have a clue where you are here.
The example doesn't need these replacements.

CurrentDb is an object Access recognises as is.