Concatenating using Allen Browne's Code repeatedly

[Connie Hendricks]

I am using Allen Browne's "Concatenat e values from related records" code (http://allenbrowne.com/func-concat.html). I am working for a small real estate company. Someone else has done the database, but didn't know how to create a report that we needed. So I've been researching/doing it.

1. When I compiled Browne's Macro I received one error. "Compile error: Argument not optional. re: DoCmnd.OpenForm ,acNormal, "Agents". Since the form Agents will not need to be concatenated I went ahead, ignoring the error message. Bad idea??? And if so, what can I do? I am a novice.

2. I am trying to concatenate telephone numbers that belong to the same ContactID. I have set it up using a query in order to get two telephone numbers per contact. So on the query that I am basing this concatenating query on, there are two lines per ContactID. I wanted to end up with one line with the phone numbers concatenated.

I am ending up with two identical lines per ContactID, and phone numbers are concatenated BUT each concatenation is duplicated:
6, Tel#1, Tel#2, Tel#1, Tel#2
6, Tel#1, Tel#2, Tel#1, Tel#2

Why am I getting the duplication of telephone numbers and duplication of records?

3. Once the above has been accomplished I would like to concatenate the Contacts based upon a ListingID. Each Listing ID can have many ContactID's associated with it. I want to Concatenate the Contacts for one ListingID, and Concatenate the phone numbers for all the contacts into one line. (use the phone numbers concatenated above and concatenate them again) Do you anticipate problems? Any advice?

Before:
Listing1;Contac t6; Tel#1,Tel#2
Listing1;Contac t7; Tel#3,Tel#4
Listing1;Contac t8; Tel#5,Tel#6

After:
Listing1;Contac t6,7,8; Tel#1,2,3,4,5,6

Here's the argument I have in the query:
SELECT qSellerList.Con tactID, qSellerList.Add ress, qSellerList.Occ upancy, qSellerList.Cur rentListPrice, qSellerList.Exp Date, ConcatRelated(" ContactInfo","q SellerList","Co ntactID = " & [ContactID]) AS [Names]
FROM qSellerList;


Hope I've been clear enough. Thanks in advance!
Connie

[Delerna]

2)
If you are doing this in a query and the records are truely identical then distinct should solve this.

[code=sql]
SELECT DISTINCT ........
[/code]

[Delerna]

3)
This should be the same as 2 except using different fields in the query

[Delerna]

1)
not familiar with

DoCm[B]n[/B]d.OpenForm[B],[/B]acNormal, "Agents".

I am more familiar with

DoCmd.OpenForm "Agents", acNormal

I think there are some typo's either in the code on the form
or your post