Code Help PLease

[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,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
to*@tom.com,fr**@fred.com,bi**@bill.com
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

[Wayne Morgan]

Place the Public Function in a module. Give the module a descriptive name,
but not the same name as any procedure in the module or elsewhere in your
project.

You can call the function from a buttons Click event, a textbox's
AfterUpdate event, or just about any other event you can think of, it
depends on what you're trying to do and when you want it done.

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)
There are 3 parameters you need to pass.
1) The FieldName to Concatenate

2) The Name of a table or query in your database or a SQL string that will
make a valid SELECT query.

3) The character(s) you want to use in the concatenated string to divide
each record from the next one concatenated to the string.

All 3 parameters are Strings (as is defined in the function) so you will
have to pass these 3 values as strings or use string variables that contain
the values.

Example:
fContatenateRecords("ContactEmail", "Customers", ";")

In this example, the field to concatenate is ContactEmail, the table or
query name is Customers, and the character used to divide the records is a
";", giving the following result.

an****@abc.com; [e-mail]; yo*@hotmail.com

It would also be possible to use string variables.

Example:
Dim strFieldName As String, strTableName As String, strConnector As String
strFieldName = "ContactEmail"
strTableName = "Customers"
strConnector = ";"

Then call the function like this:
fContatenateRecords(strFieldName, strTableName, strConnector)

You can mix this up if you desire, some of the parameters as variable and
some as a string you type in, as in the first example. The variables could
also be replaced with the names of controls on your form or you could assign
the values of the controls to the variables and use the variables in the
function call. The function is expecting strings, so you can't pass a Null
value.

--
Wayne Morgan
Microsoft Access MVP
<ho**@hope.com> wrote in message
news:e2********************************@4ax.com... 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 to*@tom.com,fr**@fred.com,bi**@bill.com
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

[hope]

Hi Wayne

Thank you very much for a very detailed answer

Please see below comments

On Mon, 07 Jun 2004 22:14:36 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

Place the Public Function in a module. Give the module a descriptive name,
but not the same name as any procedure in the module or elsewhere in your
project.
Ok done that all ok
==========================You can call the function from a buttons Click event, a textbox's
AfterUpdate event, or just about any other event you can think of, it
depends on what you're trying to do and when you want it done.
I understand all of the above ok
===========================

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)

There are 3 parameters you need to pass.
1) The FieldName to Concatenate

2) The Name of a table or query in your database or a SQL string that will
make a valid SELECT query.

3) The character(s) you want to use in the concatenated string to divide
each record from the next one concatenated to the string.

All 3 parameters are Strings (as is defined in the function) so you will
have to pass these 3 values as strings or use string variables that contain
the values.

Example:
fContatenateRecords("ContactEmail", "Customers", ";")

Ok put that on a command button in the onClick event

Private Sub Command29_click()
fContatenateRecords("ContactEmail", "Customers", ";")
End Sub
BUT get error
Compile error
Expected =
=============================
In this example, the field to concatenate is ContactEmail, the table or
query name is Customers, and the character used to divide the records is a
";", giving the following result.

an****@abc.com; [e-mail]; yo*@hotmail.com
Ok understand the above BUT
When it as done the above where does it put them all?

Because in my table I have
FirstName
LastName
ContactEmail

So on my thinking it would go and look in all the records for the field ContactEmail
Get all the emails from the table and put them like an****@abc.com; [e-mail]; yo*@hotmail.com

But now I'm lost to where it puts them after

I'm missing something here
===================================

It would also be possible to use string variables.

Example:
Dim strFieldName As String, strTableName As String, strConnector As String
strFieldName = "ContactEmail"
strTableName = "Customers"
strConnector = ";"

Then call the function like this:
fContatenateRecords(strFieldName, strTableName, strConnector)
Oh I see
So I would put it like

Private Sub Command29_click()
Dim strFieldName As String, strTableName As String, strConnector As String
strFieldName = "ContactEmail"
strTableName = "Customers"
strConnector = ";"
fContatenateRecords(strFieldName, strTableName, strConnector)
End Sub

IS that correct?
================================================You can mix this up if you desire, some of the parameters as variable and
some as a string you type in, as in the first example. The variables could
also be replaced with the names of controls on your form or you could assign
the values of the controls to the variables and use the variables in the
function call. The function is expecting strings, so you can't pass a Null
value.

Oh I see now thank you for that info

As always, any and all help is greatly appreciated.

Thank you in advance.

John

[Wayne Morgan]

Your question now seems to be, "what do I do with the output of the
function?" To that I have to ask, "what do you want to do with the output
from the function?"

The reason for the error, "expected =", is that a function returns a value,
so it needs to "go" somewhere. You can assign it to a variable, a control on
your form or report, or write it to a table. I don't recommend storing
calculated values in a table, just calculate them when you need them. Your
tables should only contain what you need to get the information you want.

So, to get rid of the error, you could do something like:

Private Sub Command29_click()
Dim strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ";")
End Sub

Now, what you do with strReturnValue is up to you. What do you want to do?

--
Wayne Morgan
MS Access MVP
<ho**@hope.com> wrote in message
news:3n********************************@4ax.com...

Hi Wayne

Thank you very much for a very detailed answer

Please see below comments

On Mon, 07 Jun 2004 22:14:36 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

Place the Public Function in a module. Give the module a descriptive name,

but not the same name as any procedure in the module or elsewhere in your
project.
Ok done that all ok
==========================

You can call the function from a buttons Click event, a textbox's
AfterUpdate event, or just about any other event you can think of, it
depends on what you're trying to do and when you want it done.

I understand all of the above ok
=========================== 3.......In the [event procedure] paste this

fContatenateRecords(FieldName to concatenate, Table; Query; or

SQLSELECT recordset, Separator character)

BUT like this

fContatenateRecords(ContactEmail, Customers; DO NOT KNOW ABOUT THE REST

OFTHESE LINES

Query; or SQL SELECT recordset, Separator character)

There are 3 parameters you need to pass.
1) The FieldName to Concatenate

2) The Name of a table or query in your database or a SQL string that willmake a valid SELECT query.

3) The character(s) you want to use in the concatenated string to divide
each record from the next one concatenated to the string.

All 3 parameters are Strings (as is defined in the function) so you will
have to pass these 3 values as strings or use string variables that containthe values.

Example:
fContatenateRecords("ContactEmail", "Customers", ";")

Ok put that on a command button in the onClick event

Private Sub Command29_click()
fContatenateRecords("ContactEmail", "Customers", ";")
End Sub
BUT get error
Compile error
Expected =
=============================

In this example, the field to concatenate is ContactEmail, the table or
query name is Customers, and the character used to divide the records is a";", giving the following result.

an****@abc.com; [e-mail]; yo*@hotmail.com

Ok understand the above BUT
When it as done the above where does it put them all?

Because in my table I have
FirstName
LastName
ContactEmail

So on my thinking it would go and look in all the records for the field

ContactEmail Get all the emails from the table and put them like an****@abc.com; [e-mail]; yo*@hotmail.com
But now I'm lost to where it puts them after

I'm missing something here
===================================

It would also be possible to use string variables.

Example:
Dim strFieldName As String, strTableName As String, strConnector As StringstrFieldName = "ContactEmail"
strTableName = "Customers"
strConnector = ";"

Then call the function like this:
fContatenateRecords(strFieldName, strTableName, strConnector)

Oh I see
So I would put it like

Private Sub Command29_click()
Dim strFieldName As String, strTableName As String, strConnector As String
strFieldName = "ContactEmail"
strTableName = "Customers"
strConnector = ";"
fContatenateRecords(strFieldName, strTableName, strConnector)
End Sub

IS that correct?
================================================

You can mix this up if you desire, some of the parameters as variable and
some as a string you type in, as in the first example. The variables couldalso be replaced with the names of controls on your form or you could assignthe values of the controls to the variables and use the variables in the
function call. The function is expecting strings, so you can't pass a Nullvalue.

Oh I see now thank you for that info

As always, any and all help is greatly appreciated.

Thank you in advance.

John

[hope]

Hi Wayne

It must be very nice to be clever :))

Please see below comments

On Tue, 08 Jun 2004 14:52:46 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

Your question now seems to be, "what do I do with the output of the
function?"
Well yes in a nut shell
I could not work out where it all went when it had done the code
===============================To that I have to ask, "what do you want to do with the output
from the function?"
Hmmmm Ok will explain for you and put you in the pic to what I'm trying to do

This all started from not being able to use Devash script for emailing from access 97
http://www.mvps.org/access/downloads/email.zip

I could use it to bring up my email program "Forte Agent" just great.
But it would only put in one email at a time in the to; box.

I will not use Outlook so I'm stuck with the email program that I have been using for 15yrs

I have emailed dev to see if I could alter the script to have more than one email sent from a list box then to the BCC field
But I suppose he is very busy

I have a data base I made with a table called customers

Not very big about 200 customers
Which I email each month

In the table i have
FirstName
LastName
ContactEmail

A form named "FrmCustomers"
with
FirstName
LastName
ContactEmail
a Text List box with LastName ContactEmail

A button send email
I click a name in the List box then click send email
This brings up my email program with the email in the to: box

Works all ok

Now I thought if I could get a script to go and look in all the "ContactEmail" fields get all the emails and make then all on one line and put a "," in them
then put them in a memo field in the table
I then could point devash script to that memo field instead of the "ContactEmail" field
This then would put them on the to: box in my email program, (better in the BCC field)

I did a search and found a script that would do the ContatenateRecords
http://www.utteraccess.com/forums/sh...34&Main=228834

Hence this where I'm at now

Phew I hope you get the pic now

I can send you the db if you like.

==============================================
The reason for the error, "expected =", is that a function returns a value,
so it needs to "go" somewhere. You can assign it to a variable, a control on
your form or report, or write it to a table. I don't recommend storing
calculated values in a table, just calculate them when you need them. Your
tables should only contain what you need to get the information you want.

So, to get rid of the error, you could do something like:

Private Sub Command29_click()
Dim strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ";")
End Sub

Now, what you do with strReturnValue is up to you. What do you want to do?
I think you will get the answer to that from the above

As always, any and all help is greatly appreciated.

Thank you in advance.

John


--
Wayne Morgan
MS Access MVP
<ho**@hope.com> wrote in message
news:3n********************************@4ax.com.. .

Hi Wayne

Thank you very much for a very detailed answer

Please see below comments

On Mon, 07 Jun 2004 22:14:36 GMT, "Wayne Morgan"

<co***************************@hotmail.com> wrote:

>Place the Public Function in a module. Give the module a descriptivename, >but not the same name as any procedure in the module or elsewhere in your
>project.

Ok done that all ok
==========================

>You can call the function from a buttons Click event, a textbox's
>AfterUpdate event, or just about any other event you can think of, it
>depends on what you're trying to do and when you want it done.

I understand all of the above ok
===========================

>> 3.......In the [event procedure] paste this
>>
>> fContatenateRecords(FieldName to concatenate, Table; Query; orSQL >SELECT recordset, Separator character)
>>
>> BUT like this
>>
>> fContatenateRecords(ContactEmail, Customers; DO NOT KNOW ABOUT THE RESTOF >THESE LINES
>> Query; or SQL SELECT recordset, Separator character)
>
>There are 3 parameters you need to pass.
>1) The FieldName to Concatenate
>
>2) The Name of a table or query in your database or a SQL string thatwill >make a valid SELECT query.
>
>3) The character(s) you want to use in the concatenated string to divide
>each record from the next one concatenated to the string.
>
>All 3 parameters are Strings (as is defined in the function) so you will
>have to pass these 3 values as strings or use string variables thatcontain >the values.
>
>Example:
>fContatenateRecords("ContactEmail", "Customers", ";")

Ok put that on a command button in the onClick event

Private Sub Command29_click()
fContatenateRecords("ContactEmail", "Customers", ";")
End Sub
BUT get error
Compile error
Expected =
=============================

>In this example, the field to concatenate is ContactEmail, the table or
>query name is Customers, and the character used to divide the records isa >";", giving the following result.
>
>an****@abc.com; [e-mail]; yo*@hotmail.com

Ok understand the above BUT
When it as done the above where does it put them all?

Because in my table I have
FirstName
LastName
ContactEmail

So on my thinking it would go and look in all the records for the field

ContactEmail

Get all the emails from the table and put them like an****@abc.com;

[e-mail]; yo*@hotmail.com

But now I'm lost to where it puts them after

I'm missing something here
===================================

>
>It would also be possible to use string variables.
>
>Example:
>Dim strFieldName As String, strTableName As String, strConnector AsString >strFieldName = "ContactEmail"
>strTableName = "Customers"
>strConnector = ";"
>
>Then call the function like this:
>fContatenateRecords(strFieldName, strTableName, strConnector)

Oh I see
So I would put it like

Private Sub Command29_click()
Dim strFieldName As String, strTableName As String, strConnector As String
strFieldName = "ContactEmail"
strTableName = "Customers"
strConnector = ";"
fContatenateRecords(strFieldName, strTableName, strConnector)
End Sub

IS that correct?
================================================

>You can mix this up if you desire, some of the parameters as variable and
>some as a string you type in, as in the first example. The variablescould >also be replaced with the names of controls on your form or you couldassign >the values of the controls to the variables and use the variables in the
>function call. The function is expecting strings, so you can't pass aNull >value.

Oh I see now thank you for that info

As always, any and all help is greatly appreciated.

Thank you in advance.

John

[Wayne Morgan]

I would probably put the output in an unbound textbox, but a memo field
would also work. It sounds as if the result may get fairly long, in fact,
possibly longer than your email client will accept on a single line. If you
put the result into a memo field, I would probably use a table just for that
with just that one field. If you want to keep a record of this, perhaps the
memo field and a date field.

Again, like everything else, there is more than one way to do this. If you
want to append the records, you could use an Append Query to add them to the
table, if you want to keep only the last record, you could run an Update
Query to update that record. You could also manipulate the recordset
directly using DAO code in VBA.

An example of using an Append Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _
strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
CurrentDb.Execute strSQL, dbFailOnError

--
Wayne Morgan
Microsoft Access MVP
<ho**@hope.com> wrote in message
news:rm********************************@4ax.com...

Hi Wayne

It must be very nice to be clever :))

Please see below comments

On Tue, 08 Jun 2004 14:52:46 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

Your question now seems to be, "what do I do with the output of the
function?"
Well yes in a nut shell
I could not work out where it all went when it had done the code
===============================

To that I have to ask, "what do you want to do with the output
from the function?"

Hmmmm Ok will explain for you and put you in the pic to what I'm trying

to do
This all started from not being able to use Devash script for emailing from access 97 http://www.mvps.org/access/downloads/email.zip

I could use it to bring up my email program "Forte Agent" just great.
But it would only put in one email at a time in the to; box.

I will not use Outlook so I'm stuck with the email program that I have been using for 15yrs
I have emailed dev to see if I could alter the script to have more than one email sent from a list box then to the BCC field But I suppose he is very busy

I have a data base I made with a table called customers

Not very big about 200 customers
Which I email each month

In the table i have
FirstName
LastName
ContactEmail

A form named "FrmCustomers"
with
FirstName
LastName
ContactEmail
a Text List box with LastName ContactEmail

A button send email
I click a name in the List box then click send email
This brings up my email program with the email in the to: box

Works all ok

Now I thought if I could get a script to go and look in all the "ContactEmail" fields get all the emails and make then all on one line and
put a "," in them then put them in a memo field in the table
I then could point devash script to that memo field instead of the "ContactEmail" field This then would put them on the to: box in my email program, (better in the BCC field)
I did a search and found a script that would do the ContatenateRecords
http://www.utteraccess.com/forums/sh...34&Main=228834
Hence this where I'm at now

Phew I hope you get the pic now

I can send you the db if you like.

==============================================

The reason for the error, "expected =", is that a function returns a value,so it needs to "go" somewhere. You can assign it to a variable, a control onyour form or report, or write it to a table. I don't recommend storing
calculated values in a table, just calculate them when you need them. Yourtables should only contain what you need to get the information you want.

So, to get rid of the error, you could do something like:

Private Sub Command29_click()
Dim strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ";")End Sub

Now, what you do with strReturnValue is up to you. What do you want to

do?
I think you will get the answer to that from the above

[hope]

Hi Wayne

Well would you believe after ALL this time and trouble I have found out what ALL the dam faults where all about

fContatenateRecords was spelt wrong

It should have been

fConcatenateRecords

fCon t NOT "t" BUT "c" atenateRecords
NOW All you script examples work just fine

I must say that you are a very nice person and a VERY helpful one at that

You Explain things in a very detailed way that a very dum person like me can understand.

It is like a lot of things, VERY EASY when you know how.

A lot of people would have just given up on me
I thank you VERY much for all your help

All I have to do know is find out how to get devash script to put it in the BCC box

Thank you

John

=========================
On Wed, 09 Jun 2004 00:09:12 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

I would probably put the output in an unbound textbox, but a memo field
would also work. It sounds as if the result may get fairly long, in fact,
possibly longer than your email client will accept on a single line. If you
put the result into a memo field, I would probably use a table just for that
with just that one field. If you want to keep a record of this, perhaps the
memo field and a date field.

Again, like everything else, there is more than one way to do this. If you
want to append the records, you could use an Append Query to add them to the
table, if you want to keep only the last record, you could run an Update
Query to update that record. You could also manipulate the recordset
directly using DAO code in VBA.

An example of using an Append Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _
strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
CurrentDb.Execute strSQL, dbFailOnError

[hope]

Hi Wayne

Spoke to soon

An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
CurrentDb.Execute strSQL, dbFailOnError

When I run the above code I get a error
Synta error in Update Statment

I press debug

It stops on the line
CurrentDb.Execute strSQL, dbFailOnError
John

On Wed, 09 Jun 2004 10:17:04 +0100, ho**@hope.com wrote:
Hi Wayne

Well would you believe after ALL this time and trouble I have found out what ALL the dam faults where all about

fContatenateRecords was spelt wrong

It should have been

fConcatenateRecords

fCon t NOT "t" BUT "c" atenateRecords
NOW All you script examples work just fine

I must say that you are a very nice person and a VERY helpful one at that

You Explain things in a very detailed way that a very dum person like me can understand.

It is like a lot of things, VERY EASY when you know how.

A lot of people would have just given up on me
I thank you VERY much for all your help

All I have to do know is find out how to get devash script to put it in the BCC box

Thank you

John

=========================
On Wed, 09 Jun 2004 00:09:12 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

I would probably put the output in an unbound textbox, but a memo field
would also work. It sounds as if the result may get fairly long, in fact,
possibly longer than your email client will accept on a single line. If you
put the result into a memo field, I would probably use a table just for that
with just that one field. If you want to keep a record of this, perhaps the
memo field and a date field.

Again, like everything else, there is more than one way to do this. If you
want to append the records, you could use an Append Query to add them to the
table, if you want to keep only the last record, you could run an Update
Query to update that record. You could also manipulate the recordset
directly using DAO code in VBA.

An example of using an Append Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _
strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
CurrentDb.Execute strSQL, dbFailOnError

[Anne Nolan]

The usual syntax of an UPDATE SQL statement is

UPDATE sometable SET somefield = "something"; (for text fields)

try changing
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
to
strSQL = "UPDATE tblEmail SET MemoField = """ & strReturnValue & """;"

Anne

<ho**@hope.com> wrote in message
news:ep********************************@4ax.com...

Hi Wayne

Spoke to soon

An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
CurrentDb.Execute strSQL, dbFailOnError

When I run the above code I get a error
Synta error in Update Statment

I press debug

It stops on the line
CurrentDb.Execute strSQL, dbFailOnError
John

On Wed, 09 Jun 2004 10:17:04 +0100, ho**@hope.com wrote:

Hi Wayne

Well would you believe after ALL this time and trouble I have found out what ALL the dam faults where all about
fContatenateRecords was spelt wrong

It should have been

fConcatenateRecords

fCon t NOT "t" BUT "c" atenateRecords
NOW All you script examples work just fine

I must say that you are a very nice person and a VERY helpful one at that

You Explain things in a very detailed way that a very dum person like me can understand.
It is like a lot of things, VERY EASY when you know how.

A lot of people would have just given up on me
I thank you VERY much for all your help

All I have to do know is find out how to get devash script to put it in the BCC box
Thank you

John

=========================
On Wed, 09 Jun 2004 00:09:12 GMT, "Wayne Morgan" <co***************************@hotmail.com> wrote:

I would probably put the output in an unbound textbox, but a memo field
would also work. It sounds as if the result may get fairly long, in fact,possibly longer than your email client will accept on a single line. If youput the result into a memo field, I would probably use a table just for thatwith just that one field. If you want to keep a record of this, perhaps thememo field and a date field.

Again, like everything else, there is more than one way to do this. If youwant to append the records, you could use an Append Query to add them to thetable, if you want to keep only the last record, you could run an Update
Query to update that record. You could also manipulate the recordset
directly using DAO code in VBA.

An example of using an Append Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _
strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRecords("ContactEmail", "Customers", ",")
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
CurrentDb.Execute strSQL, dbFailOnError

[Wayne Morgan]

Anne is correct, I typed it in instead of copy/paste and left out a part.

--
Wayne Morgan
MS Access MVP
"Anne Nolan" <an***************@AOL.COM> wrote in message
news:2i************@uni-berlin.de...

The usual syntax of an UPDATE SQL statement is

UPDATE sometable SET somefield = "something"; (for text fields)

try changing
strSQL = "UPDATE tblEmail.MemoField = """ & strReturnValue & """;"
to
strSQL = "UPDATE tblEmail SET MemoField = """ & strReturnValue & """;"

Anne