Merging multiple rows from a single field into a textbox (1 Viewer)

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
Hi,

I have a form with a subform.

On the main form I have a combobox to select a category.

The subform then displays all email addresses linked to that category.

What I now want to do is (by double clicking (on_dbl_click) my BCC textbox (shown below)) grab all of the email addresses and put them into the BCC textbox on the main form separated by a semi colon.

In the pic below I manually entered the email addresses in the BCC textbox to show how I wanted it to look.

EG


Any help would be greatly appreciated

Regards
Matt
 

DCrake

Remembered
Local time
Today, 03:32
Joined
Jun 8, 2005
Messages
8,634
I would swap you addresses subform for a list box and base the rowsource on the current subforms recordsource. Make the list box a multi select list box then let the user highlight the ones they want then have a command button that enumerates through all the selected items in the list box and concats them together with a semi colon, which is then passed to the Bcc textbox.

David
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
I would swap you addresses subform for a list box and base the rowsource on the current subforms recordsource. Make the list box a multi select list box then let the user highlight the ones they want then have a command button that enumerates through all the selected items in the list box and concats them together with a semi colon, which is then passed to the Bcc textbox.

David

I probably should have mentioned that there could be hundreds of email addresses listed in the subform :( which is why I'm after an automated action.
 

Mike375

Registered User.
Local time
Today, 14:32
Joined
Aug 28, 2008
Messages
2,548
If you get stuck there is a crude way to do that.

Firstly, create another field that joins user@domain.com to ; so that new field has user@domain.com; as its entry.

You make a little macro run by a RunMacro action.

The macro goes to the first record and copies the entry. It then goes to the BCC field and pastes. It repeats itself but with SendKeys goes to the end, in other words it pastes after the last entry. In the case of the first entry it will be OK because the start of the field is the end:) You can also use SendKeys to add a space.

The RunMacro action stops when it reaches the last record in the subform, that is, it won't run after it attempts to go past the last record.

You could also make the macro so it will go to the next record on the main form and repeat the process. Use Echo Yes, turn the lights off and watch it run:D

If this way is suitable for you then I could dig up some stuff I have made in the past that is similar that might assist. In essence this amounts to automating a manual process. It might also be easier to use another form than the sub form, that is, a stand alone form that is a replica of the subform and have it open and operate from that form.
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
that should work perfectly, should i start working on the extra field and just use a similar to this?

EmailList = EmailAddress & " " & ";"

would this actually work?
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
that would be perfect, how do i go about formatting the new field?
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
If you get stuck there is a crude way to do that.

Firstly, create another field that joins user@domain.com to ; so that new field has user@domain.com; as its entry.

You make a little macro run by a RunMacro action.

The macro goes to the first record and copies the entry. It then goes to the BCC field and pastes. It repeats itself but with SendKeys goes to the end, in other words it pastes after the last entry. In the case of the first entry it will be OK because the start of the field is the end:) You can also use SendKeys to add a space.

The RunMacro action stops when it reaches the last record in the subform, that is, it won't run after it attempts to go past the last record.

You could also make the macro so it will go to the next record on the main form and repeat the process. Use Echo Yes, turn the lights off and watch it run:D

If this way is suitable for you then I could dig up some stuff I have made in the past that is similar that might assist. In essence this amounts to automating a manual process. It might also be easier to use another form than the sub form, that is, a stand alone form that is a replica of the subform and have it open and operate from that form.

That sounds like it would work perfectly. I'll try work on the new field to combine emailaddress and ;
 

Mike375

Registered User.
Local time
Today, 14:32
Joined
Aug 28, 2008
Messages
2,548
that should work perfectly, should i start working on the extra field and just use a similar to this?

EmailList = EmailAddress & " " & ";"

would this actually work?

New FieldName:[EmailList] & "" & ";"

That creates a new field in the query with no space before the ;
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
heh i got that part working, but as an on update of the EmailAddress Field, the only problem is if the EmailAddress Field is blank, EmailList still shows the ;

How can I skip a field if blank,

ie

If Me!EmailAddress = Null Then goto next record????
Else
Me!EmailList = Me!EmailAddress & "" & ";"
End If
 

Mike375

Registered User.
Local time
Today, 14:32
Joined
Aug 28, 2008
Messages
2,548
You will need to use SelectObject macro action to move between the forms and this is important for SendKeys. You opening post looks like A2007 so not sure if it called by the same name.
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
do you have msn?

if so could you chat with me there?
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
if not, yes I have A2007 at home, but work only has 2003, so I have my version set to default version 2002-2003
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
I use 2007 at home but 2003 at work, home is set to default version 2002-2003
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
Ok I have got it working with this code,

Private Sub EmailAddress_Enter()

If Not IsNull(Me.EmailAddress) Then
Me.EmailList = Me!EmailAddress & "" & ";"
End If

End Sub

Now whenever I update the EmailAddress and hit enter to move to next field, it will only add the ; to EmailList if EmailAddress is not NULL :)

Now I just need help with grabbing all of the Emails from EmailList and add them to my text box called txtBcc as shown in the OP pic
 

Mike375

Registered User.
Local time
Today, 14:32
Joined
Aug 28, 2008
Messages
2,548
When using a copy action each field needs and entry. So your query should eliminate any records with Null. You could probably make something where a null entry is skipped but it is easier to simply not have those records present.
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
The EmailAddress is not a mandatory part of my original form which I use to enter all of my Contacts info into.

The second form, that I am working on now, will be used to send Emails to those that have given an Email Address, hence why I need all addresses to end with ;

On the Subform I have it to only show contacts that have given their Email Address and to also disable duplicates.

What I have now works for me, I just need to concate? the data from EmailList into txtBcc as 1 line (so to speak)
 

maestro83

Registered User.
Local time
Today, 14:32
Joined
Oct 15, 2008
Messages
23
If you can dig up that macro to copy and paste the entries I'll be set :)

Thanks for your help

Matt
 

Mike375

Registered User.
Local time
Today, 14:32
Joined
Aug 28, 2008
Messages
2,548
I will have a look around tonight (1pm Saturday in Sydney Australia) or tomorrow...the SendKeys is the main part as that locates where the "paste" is done.

The disadvantage of doing things this way as compared to what someone could make in a query is that it will be much slower which might or might not be a problem.
 

DCrake

Remembered
Local time
Today, 03:32
Joined
Jun 8, 2005
Messages
8,634
Just deviating from the table concept back to my suggestion, you could always have a set of buttons under/next yo your list box with Select All Deselect All Toggle. Which would work as the captions display.

I do not condem using a table and enumerating through it, however, you do not have 100% control of the contents, there may be certain people on the list that you want to exclude, with your version it is either all or nothing.

At least my version gives you the change to exclude addresses.

David
 

Users who are viewing this thread

Top Bottom