Merging multiple rows from a single field into a textbox

[maestro83]

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]

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

 

[Uncle Gizmo]

You may find this thread and example useful....

http://www.access-programmers.co.uk/forums/showthread.php?t=71302

 

[maestro83]

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]

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

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]

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]

that would be perfect, how do i go about formatting the new field?

 

[maestro83]

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

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]

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]

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]

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]

do you have msn?

if so could you chat with me there?

 

[maestro83]

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]

I use 2007 at home but 2003 at work, home is set to default version 2002-2003

 

[maestro83]

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]

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]

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]

If you can dig up that macro to copy and paste the entries I'll be set

Thanks for your help

Matt

 

[Mike375]

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]

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