Creating a paragraph with a column - with prog & upgraded issue

S

Sara Sun

Hi all
I am using an access database to maintaining the data of a small group of
people. I am very comfortable with Access. Every now & then I send email to a
group of people, who I have to query, to find their email address (which is a
column in a table in my DB). I process in this results in excel or word. Make
this column of data into a paragraph which can be cut & pasted to e-mail’s
‘to’ address.

I am thinking of creating a report which summarizes a column of email
address with separator (; or ‘) so that anybody can copy the text & paste it
to their outlook outgoing address. Instead of me going thru the procedure of
querying & processing in excel or word. In a sentence is a way to summarize a
column of data into a paragraph??


I did get a suggestion program like this & it worked, BUT any text box of
Access is not allowing more then 2048 characters (I may be using up to
5000characters or more)
My questions are
1. is there any one who had this same problem & got a tricky way out
2. is there any way of splitting this into 2 – 3 text boxes.
3. is it feasible if I am trying it in MS- access report ( again the number
of characters plays a major issue here)

If anyone can help me out here it will be of a great help for me.

Thanks
Sara

This is the program I used

“ Then copy and paste this code to the form load event.

Dim rs As Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT EMailColumnName FROM EmployeeTableName",
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rs.MoveFirst
email = ""
While Not rs.EOF
email = email & Trim(rs.Fields(0)) & ","
rs.MoveNext
Wend
email = Left(email, Len(email) - 1)
Me.Text0.SetFocus
Me.Text0.Text = email “
 
T

Tom Wickerath

Hi Sara Sun,

Try
Me.Text0.Value = email

in place of Me.Text0.Text. I just got it to work with a string of e-mail
addresses that is 4040 characters in length, using an unbound textbox.

Also, I suggest that you add Option Explicit as the second line of code in
your form's module. Then add the following declaration to your procedure:

dim email as String

To read more about why Option Explicit is so important, please see the
following article:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
S

Sara Sun

great it did work - it was holding in more then 5500 chara.

is that any way i can change the program to
1. change the table name or query name to that of the form's each time it
executes ??
2. i have some duplicate records in the column (which is necessary), is
there anyway i can eliminate the duplicate records in the final paragraph?

Thanks for responding
Sara Sun
 
S

Sara Sun

Hi Ton
with SELECT DISTINCT & your recommendation, the unbound textbox is holding
almost around 10,000 chara

regarding the first question of mine, in the
"SELECT DISTINCT EMailColumnName FROM EmployeeTableName", ....
instead of 'EmployeeTableName' can I introduce a variable ie., i want this
to be a variable table name which can vary with the recordsource i specify in
the form. cos my front end users know to change the table or query name in
The form's recordsource but not comfortable with looking at the Visual basic
codes

Donno how much your help is appreciated.

Thanks
Sara
 
T

Tom Wickerath

Hi Sara,
instead of 'EmployeeTableName' can I introduce a variable ie., i want this
to be a variable table name which can vary with the recordsource i specify in
the form.
Click to expand...

I believe you can do this, but I question the underlying database design. It
sounds like you are storing similar data in more than one table. This is
usually never recommended. It is better to have all similar data in one
table. You can add a field (attribute) to distinguish the records in some
way. My suggestion, before attempting to change table names in code, or force
your users to change a form's recordsource, is to read up on database design.
Start at this link for a great selection of papers on this topic:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

To get you going quickly, since a re-design is likely going to be a
considerable effort (but well worth the effort), you could try something like
the following. Note that I used explicit declaration for the recordset
variable (ADODB.Recordset):


Private Sub Form_Load()
On Error GoTo ProcError

Dim rs As ADODB.Recordset
Dim strSQL As String
Dim email As String

Set rs = New ADODB.Recordset

strSQL = "SELECT DISTINCT EMailColumnName FROM " _
& Me.RecordSource
' Debug.Print strSQL '<---Uncomment for debugging

rs.Open strSQL, CurrentProject.Connection, _
adOpenForwardOnly, adLockReadOnly

With rs
While Not (.BOF Or .EOF)
email = email & Trim(rs.Fields(0)) & ","
rs.MoveNext
Wend
End With

email = Left(email, Len(email) - 1)
Me.Text0.SetFocus
Me.Text0.Value = email

ExitProc:
On Error Resume Next
rs.Close: Set rs = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Load..."
Resume ExitProc

End Sub



Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
S

Sara Sun

Hi Tom
didnt work :(.

I am maintaining & updating this 10 yr old access DB, i am sure that i am
not duplicating records. This what i am trying to do. I am creating a
paragraph of email addresses from a column in one table - My idea is to - get
this column from a pre-existing query (there are around 200+ queries), by
linking the query to a form. cos my users are fine with changing the form
properties to different queries but certainly not to be trusted with VB
codes.

I have totally lost my touch with coding, Last time i wrote some VB codes -
it was to move the mouse to get a colorful tail with the pointer on the
screen :). You are really helping me a lot, Thanks

Regards
Sara
 
S

Sara Sun

this where i am getting the error message - says syntax error.

strSQL = "SELECT DISTINCT EMail_address FROM ",_
& Me.RecordSource
- Sara
 
S

Sara Sun

Hi Tom
I already tried that, But i was getting this error message
compile error:
expected: end of statement

Thanks
Sara
 
T

Tom Wickerath

Hi Sara,

Try replacing all the code with a fresh copy. You might first try pasting
the code into Notepad, and saving it, just to make sure that there is not
something weird going on from copying the code from this message. I tested
the code before posting it, and it works fine on my PC.

If you still cannot get it to work, and you are willing to send me a
compacted and zipped copy of your database, I will take a look at it for you.
If you are interested in this option, send me a private e-mail message with a
valid reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Document Merge Formatting Issue 0
One note creating multiple tabs when collaborating? 0
LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work 0
New Outlook problem 0
Finding the proper style field and the numbered paragraph value within the style 0
Help with multi-level list 0
Paragraph numbering in Word 1
Setting column width in a table with a macro is not working 0

Top