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 fConcatenateRec ords(strField As String, strRecordset As String, strFieldSeparat or As String) As String
'USAGE:
' fContatenateRec ords(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.OpenRecor dset(strRecords et)
With rst
If .EOF And .BOF Then
fConcatenateRec ords = "" 'no records returned
Exit Function
End If
.MoveFirst
While Not .EOF
strTemp = strTemp & .Fields(strFiel d) & strFieldSeparat or & " "
.MoveNext
Wend
.Close
End With
strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSe parator) + 1))
fConcatenateRec ords = 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
fContatenateRec ords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
BUT like this
fContatenateRec ords(ContactEma il, Customers; DO NOT KNOW ABOUT THE REST OF THESE LINES
Query; or SQL SELECT recordset, Separator character)
I have put in
strConcatenated =fContatenateRe cords(ContactEm ail,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" hmmmmmmmmmmmmmm m
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 "ContacEmai l" 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 9 2413
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
fContatenateRec ords(FieldName to concatenate, Table; Query; or SQL
SELECT recordset, Separator character) BUT like this
fContatenateRec ords(ContactEma il, 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:
fContatenateRec ords("ContactEm ail", "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; me@hotmail.com; yo*@hotmail.com
It would also be possible to use string variables.
Example:
Dim strFieldName As String, strTableName As String, strConnector As String
strFieldName = "ContactEma il"
strTableName = "Customers"
strConnector = ";"
Then call the function like this:
fContatenateRec ords(strFieldNa me, 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.co m> wrote in message
news:e2******** *************** *********@4ax.c om... 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 fConcatenateRec ords(strField As String, strRecordset As
String, strFieldSeparat or As String) As String 'USAGE: ' fContatenateRec ords(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.OpenRecor dset(strRecords et) With rst If .EOF And .BOF Then fConcatenateRec ords = "" 'no records returned Exit Function End If .MoveFirst While Not .EOF strTemp = strTemp & .Fields(strFiel d) & strFieldSeparat or & "
" .MoveNext Wend .Close End With strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSe parator) + 1)) fConcatenateRec ords = 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
fContatenateRec ords(FieldName to concatenate, Table; Query; or SQL
SELECT recordset, Separator character) BUT like this
fContatenateRec ords(ContactEma il, Customers; DO NOT KNOW ABOUT THE REST OF
THESE LINES Query; or SQL SELECT recordset, Separator character)
I have put in strConcatenated =fContatenateRe cords(ContactEm ail,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" hmmmmmmmmmmmmmm m
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
"ContacEmai l" 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
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
fContatenateRec ords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character) BUT like this
fContatenateRec ords(ContactEma il, 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: fContatenateRe cords("ContactE mail", "Customers" , ";")
Ok put that on a command button in the onClick event
Private Sub Command29_click ()
fContatenateRec ords("ContactEm ail", "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 ; me@hotmail.com; 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; me@hotmail.com; 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 = "ContactEma il" strTableName = "Customers" strConnector = ";"
Then call the function like this: fContatenateRe cords(strFieldN ame, 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 = "ContactEma il"
strTableName = "Customers"
strConnector = ";"
fContatenateRec ords(strFieldNa me, 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
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 = fContatenateRec ords("ContactEm ail", "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.co m> wrote in message
news:3n******** *************** *********@4ax.c om... 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
fContatenateRec ords(FieldName to concatenate, Table; Query; or
SQLSELECT recordset, Separator character) BUT like this
fContatenateRec ords(ContactEma il, 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: fContatenateRe cords("ContactE mail", "Customers" , ";")
Ok put that on a command button in the onClick event
Private Sub Command29_click () fContatenateRec ords("ContactEm ail", "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 ; me@hotmail.com; 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;
me@hotmail.com; 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 = "ContactEma il" strTableName = "Customers" strConnector = ";"
Then call the function like this: fContatenateRe cords(strFieldN ame, 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 = "ContactEma il" strTableName = "Customers" strConnector = ";" fContatenateRec ords(strFieldNa me, 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
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 "FrmCustome rs"
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 "ContactEma il" 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 "ContactEma il" 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 ContatenateReco rds 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 = fContatenateRec ords("ContactEm ail", "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 >> >> fContatenateRec ords(FieldName to concatenate, Table; Query; orSQL >SELECT recordset, Separator character) >> >> BUT like this >> >> fContatenateRec ords(ContactEma il, 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: >fContatenateRe cords("ContactE mail", "Customers" , ";")
Ok put that on a command button in the onClick event
Private Sub Command29_click () fContatenateRec ords("ContactEm ail", "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 ; me@hotmail.com; 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; me@hotmail.com ; 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 = "ContactEma il" >strTableName = "Customers" >strConnector = ";" > >Then call the function like this: >fContatenateRe cords(strFieldN ame, 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 = "ContactEma il" strTableName = "Customers" strConnector = ";" fContatenateRec ords(strFieldNa me, 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
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 = fContatenateRec ords("ContactEm ail", "Customers" , ",")
strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _
strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;"
CurrentDb.Execu te strSQL, dbFailOnError
An example using an Update Query:
Dim strSQL As String, strReturnValue As String
strReturnValue = fContatenateRec ords("ContactEm ail", "Customers" , ",")
strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;"
CurrentDb.Execu te strSQL, dbFailOnError
--
Wayne Morgan
Microsoft Access MVP
<ho**@hope.co m> wrote in message
news:rm******** *************** *********@4ax.c om... 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 "FrmCustome rs" 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
"ContactEma il" 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
"ContactEma il" 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 ContatenateReco rds 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 = fContatenateRec ords("ContactEm ail", "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
Hi Wayne
Well would you believe after ALL this time and trouble I have found out what ALL the dam faults where all about
fContatenateRec ords was spelt wrong
It should have been
fConcatenateRec ords
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 strReturnVal ue = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _ strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;" CurrentDb.Exec ute strSQL, dbFailOnError
An example using an Update Query: Dim strSQL As String, strReturnValue As String strReturnVal ue = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;" CurrentDb.Exec ute strSQL, dbFailOnError
Hi Wayne
Spoke to soon An example using an Update Query: Dim strSQL As String, strReturnValue As String strReturnVal ue = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;" CurrentDb.Exec ute 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.Execu te 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
fContatenateRe cords was spelt wrong
It should have been
fConcatenateRe cords
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 strReturnValu e = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _ strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;" CurrentDb.Exe cute strSQL, dbFailOnError
An example using an Update Query: Dim strSQL As String, strReturnValue As String strReturnValu e = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;" CurrentDb.Exe cute strSQL, dbFailOnError
The usual syntax of an UPDATE SQL statement is
UPDATE sometable SET somefield = "something" ; (for text fields)
try changing
strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;"
to
strSQL = "UPDATE tblEmail SET MemoField = """ & strReturnValue & """;"
Anne
<ho**@hope.co m> wrote in message
news:ep******** *************** *********@4ax.c om... Hi Wayne
Spoke to soon
An example using an Update Query: Dim strSQL As String, strReturnValue As String strReturnVal ue = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;" CurrentDb.Exec ute 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.Execu te 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 fContatenateRe cords was spelt wrong
It should have been
fConcatenateRe cords
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 strReturnValu e = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "INSERT INTO tblEmail (MemoField, DateField) SELECT """ & _ strReturnValue & """ As Expr1, #" & Date() & "# As Expr2;" CurrentDb.Exe cute strSQL, dbFailOnError
An example using an Update Query: Dim strSQL As String, strReturnValue As String strReturnValu e = fContatenateRec ords("ContactEm ail", "Customers" , ",") strSQL = "UPDATE tblEmail.MemoFi eld = """ & strReturnValue & """;" CurrentDb.Exe cute strSQL, dbFailOnError
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.MemoFi eld = """ & strReturnValue & """;" to strSQL = "UPDATE tblEmail SET MemoField = """ & strReturnValue & """;"
Anne This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Shufen |
last post by:
Hi,
I'm a newbie that just started to learn python, html and etc. I have
some questions to ask and hope that someone can help me on.
I'm trying to code a python script (with HTML) to get values from a
html form that consists of about 10 checkbox and a textbox where user
have to key in a value to perform a search.
From python tutors, I learned that I have to use the following method:
|
by: rked |
last post by:
I get nameSPAN1 is undefined when I place cursor in comments box..
<%@ LANGUAGE="VBScript" %>
<% DIM ipAddress
ipAddress=Request.Servervariables("REMOTE_HOST")
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
|
by: x muzuo |
last post by:
Hi guys,
I have got a prob of javascript form validation which just doesnt work
with my ASP code. Can any one help me out please.
Here is the code:
{////<<head>
<title>IIBO Submit Page</title>
</head>
<style type="text/css">
|
by: Zellan |
last post by:
Hi All,
Have added a Strconv function to the AfterUpdate event of a field to give me
first letter capitalization (vbProperCase). I have added a msgbox that will
allow me to over-ride where necessary however it does not work.
The code below will allow me to manually type in capitals into the full
string when I hit the "Yes" button, but when I elect "No" the vbProperCase
is not activated and no change is made. Can anyone please help me...
|
by: KiMcHeE |
last post by:
Hi! I'm having a real difficult time trying to figure out what is
wrong with my program code. I'm trying to make a "calculator" in the C
language and I only know basic stuff. Here's my code:
#include <stdio.h>
/* global variables */
int e, f;
| |
by: jd2007 |
last post by:
Why the Ajax code below in ajax.js is causing my form not to work ?
ajax.js:
var a=0;
var b=0;
var c=0;
var d=0;
var e=0;
var f=0;
|
by: Vbbeginner07 |
last post by:
The follwing code is of a listview where i have to a to display the id and the name from a particular table ,empdetail.
This is in the second form,dispalying id and name
Private Sub Form_Load()
ListView1.View = lvwReport
ListView1.ColumnHeaders.Add , , "NAME"
ListView1.ColumnHeaders.Add , , "ID"
ListView1.Refresh
rs.Open "select * from empdetail", conn, adOpenStatic, adLockOptimistic
|
by: dragiton |
last post by:
ASP Code Redirect Error
Hello I recently relaunched a website containing asp code which used to work perfectly. However, after resetting up the SQL DB and trying to work out some site bugs I have the following problem: any suggestions?
I have a newuser login page that creates a new SQL DB table record with login name and password then takes the user to the next (page) step in the registration setup. Currently the record is created in the...
|
by: =?Utf-8?B?cm9kY2hhcg==?= |
last post by:
hey all,
please reference: http://pastebin.com/mef78b3d
As shown in the reference I am dynamically building my controls in my class.
I'd like to be able to attach javascript calls as attributes to each of my
controls if applies.
for example,
String varString = "alert('i'm a textbox')";
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |