Code help Please

  • Thread starter Thread starter hope
  • Start date Start date
H

hope

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,Customers,",")

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
(e-mail address removed),[email protected],[email protected]
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)


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
Click to expand...
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
Click to expand...
String, strFieldSeparator As String) As String
'USAGE:
' fContatenateRecords(FieldName to concatenate, Table; Query; or SQL
Click to expand...
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
Click to expand...
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
Click to expand...
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,Customers,",")

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
Click to expand...
"ContacEmail" and make them like
 
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",",")
Click to expand...

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.
Click to expand...

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



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)


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
Click to expand...
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
Click to expand...
String, strFieldSeparator As String) As String
'USAGE:
' fContatenateRecords(FieldName to concatenate, Table; Query; or SQL
Click to expand...
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
Click to expand...
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
Click to expand...
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,Customers,",")

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
Click to expand...
"ContacEmail" and make them like
(e-mail address removed),[email protected],[email protected]
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
Click to expand...
Click to expand...
 
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.
 
Back
Top