If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Code help Please
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 |
#3
|
|||
|
|||
Code help Please
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 |
#4
|
|||
|
|||
Code help Please
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 |
Thread Tools | |
Display Modes | |
|
|