Code help Please

Hi

Access 97

I'm lost on this code please can you help

=================================
Below is some simple code that will concatenate a single field's value from multiple records into a single string separated by a user defined character. There is no error trapping
(by design), USE AT YOUR OWN RISK.

code:start
Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator As String) As String
'USAGE:
' fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
'NOTE:
' DAO recordset is being used to provide backward compatability with Access 97
' Make sure you enable the DAO reference

On Error Resume Next 'To prevent query from hanging no error trapping involved
'If no records are return, you should look for the problem with your SQL SELECT statement

Dim curDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp As String

Set curDB = CurrentDb()
Set rst = curDB.OpenRecordset(strRecordset)
With rst
If .EOF And .BOF Then
fConcatenateRecords = "" 'no records returned
Exit Function
End If
.MoveFirst
While Not .EOF
strTemp = strTemp & .Fields(strField) & strFieldSeparator & " "
.MoveNext
Wend
.Close
End With
strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
fConcatenateRecords = strTemp
End Function
end code
===================================




Ok now do I

1...... Copy and paste all of this into a module?

2.......make a command button on a form and on the onclick event as a [event procedure]

3.......In the [event procedure] paste this

fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)

BUT like this

fContatenateRecords(ContactEmail, Customers; DO NOT KNOW ABOUT THE REST OF THESE LINES
Query; or SQL SELECT recordset, Separator character)

I have put in
strConcatenated=fContatenateRecords(ContactEmail,C ustomers,",")

BUT when I compile it I get a error

"Variable not defined"

Now when I look in the help under that error I see
You use a "Option Explict statement" hmmmmmmmmmmmmmmm


So as I see it I have in my table a field named ContacEmail
My table is called Customers
So if I run the code it would get all the emails from the records "ContacEmail" and make them like

BUT where does it put them when it as done?

Have I go that right?

As always, any and all help is greatly appreciated.

Thank you in advance.

John

It sounds like you want to concatenate the Field "ContactEmail" in the Table
"Customers" using comma as the separator. In this case, you need to enclose
Field name and Table name in double-quotes like:

strConcatenated=fContatenateRecords("ContactEmail" ,"Customers",",")

Beware that most e-mail clients won't be happy with a long list of e-mail
addresses.

--
HTH
Van T. Dinh
MVP (Access)


wrote in message
...
Hi

Access 97

I'm lost on this code please can you help

=================================
Below is some simple code that will concatenate a single field's value
from multiple records into a single string separated by a user defined
character. There is no error trapping
(by design), USE AT YOUR OWN RISK.

code:start
Public Function fConcatenateRecords(strField As String, strRecordset As
String, strFieldSeparator As String) As String
'USAGE:
' fContatenateRecords(FieldName to concatenate, Table; Query; or SQL
SELECT recordset, Separator character)
'NOTE:
' DAO recordset is being used to provide backward compatability with
Access 97
' Make sure you enable the DAO reference

On Error Resume Next 'To prevent query from hanging no error trapping
involved
'If no records are return, you should look for
the problem with your SQL SELECT statement

Dim curDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp As String

Set curDB = CurrentDb()
Set rst = curDB.OpenRecordset(strRecordset)
With rst
If .EOF And .BOF Then
fConcatenateRecords = "" 'no records returned
Exit Function
End If
.MoveFirst
While Not .EOF
strTemp = strTemp & .Fields(strField) & strFieldSeparator & "
"
.MoveNext
Wend
.Close
End With
strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
fConcatenateRecords = strTemp
End Function
end code
===================================




Ok now do I

1...... Copy and paste all of this into a module?

2.......make a command button on a form and on the onclick event as a
[event procedure]

3.......In the [event procedure] paste this

fContatenateRecords(FieldName to concatenate, Table; Query; or SQL
SELECT recordset, Separator character)

BUT like this

fContatenateRecords(ContactEmail, Customers; DO NOT KNOW ABOUT THE REST OF
THESE LINES
Query; or SQL SELECT recordset, Separator character)

I have put in
strConcatenated=fContatenateRecords(ContactEmail,C ustomers,",")

BUT when I compile it I get a error

"Variable not defined"

Now when I look in the help under that error I see
You use a "Option Explict statement" hmmmmmmmmmmmmmmm


So as I see it I have in my table a field named ContacEmail
My table is called Customers
So if I run the code it would get all the emails from the records
"ContacEmail" and make them like

BUT where does it put them when it as done?

Have I go that right?

As always, any and all help is greatly appreciated.

Thank you in advance.

John

Hi
thank you for the help

It sounds like you want to concatenate the Field "ContactEmail" in the Table
"Customers" using comma as the separator. In this case, you need to enclose
Field name and Table name in double-quotes like:

strConcatenated=fContatenateRecords("ContactEmail ","Customers",",")

I put that in BUT it came back with a error
Compile error
Variable not defined
===================================
Beware that most e-mail clients won't be happy with a long list of e-mail
addresses.

Yes I understand that
I have a small data base that i use for my members about 200
I just email them once a month for the next moths details

Should I use some other way of doing this then?

As always, any and all help is greatly appreciated.

Thank you in advance.

John



On Tue, 8 Jun 2004 08:24:26 +1000, "Van T. Dinh" wrote:

It sounds like you want to concatenate the Field "ContactEmail" in the Table
"Customers" using comma as the separator. In this case, you need to enclose
Field name and Table name in double-quotes like:

strConcatenated=fContatenateRecords("ContactEmail ","Customers",",")

Beware that most e-mail clients won't be happy with a long list of e-mail
addresses.

--
HTH
Van T. Dinh
MVP (Access)


wrote in message
.. .
Hi

Access 97

I'm lost on this code please can you help

=================================
Below is some simple code that will concatenate a single field's value
from multiple records into a single string separated by a user defined
character. There is no error trapping
(by design), USE AT YOUR OWN RISK.

code:start
Public Function fConcatenateRecords(strField As String, strRecordset As
String, strFieldSeparator As String) As String
'USAGE:
' fContatenateRecords(FieldName to concatenate, Table; Query; or SQL
SELECT recordset, Separator character)
'NOTE:
' DAO recordset is being used to provide backward compatability with
Access 97
' Make sure you enable the DAO reference

On Error Resume Next 'To prevent query from hanging no error trapping
involved
'If no records are return, you should look for
the problem with your SQL SELECT statement

Dim curDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp As String

Set curDB = CurrentDb()
Set rst = curDB.OpenRecordset(strRecordset)
With rst
If .EOF And .BOF Then
fConcatenateRecords = "" 'no records returned
Exit Function
End If
.MoveFirst
While Not .EOF
strTemp = strTemp & .Fields(strField) & strFieldSeparator & "
"
.MoveNext
Wend
.Close
End With
strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
fConcatenateRecords = strTemp
End Function
end code
===================================




Ok now do I

1...... Copy and paste all of this into a module?

2.......make a command button on a form and on the onclick event as a
[event procedure]

3.......In the [event procedure] paste this

fContatenateRecords(FieldName to concatenate, Table; Query; or SQL
SELECT recordset, Separator character)

BUT like this

fContatenateRecords(ContactEmail, Customers; DO NOT KNOW ABOUT THE REST OF
THESE LINES
Query; or SQL SELECT recordset, Separator character)

I have put in
strConcatenated=fContatenateRecords(ContactEmail,C ustomers,",")

BUT when I compile it I get a error

"Variable not defined"

Now when I look in the help under that error I see
You use a "Option Explict statement" hmmmmmmmmmmmmmmm


So as I see it I have in my table a field named ContacEmail
My table is called Customers
So if I run the code it would get all the emails from the records
"ContacEmail" and make them like

BUT where does it put them when it as done?

Have I go that right?

As always, any and all help is greatly appreciated.

Thank you in advance.

John

1. Do you have a Field "ContactEmail" in Table "Customers"?

2. Have you dimensioned strConcatenated (if you use "Option Explicit")?

I would recommend sending e-mailing customers with a list of 200 e-mail
addresses on the e-mail. Most customers will be more happy with "personal
touch", i.e. only *his/her* e-mail address on the e-mail.

--
HTH
Van T. Dinh
MVP (Access)



wrote in message
...
Hi
thank you for the help

It sounds like you want to concatenate the Field "ContactEmail" in the
Table
"Customers" using comma as the separator. In this case, you need to
enclose
Field name and Table name in double-quotes like:

strConcatenated=fContatenateRecords("ContactEmail ","Customers",",")

I put that in BUT it came back with a error
Compile error
Variable not defined
===================================
Beware that most e-mail clients won't be happy with a long list of e-mail
addresses.

Yes I understand that
I have a small data base that i use for my members about 200
I just email them once a month for the next moths details

Should I use some other way of doing this then?

As always, any and all help is greatly appreciated.

Thank you in advance.

John