Break up Field Data to other Fields

[Robert M]

I have a database of emails that I am work with to send our clients pertinant information. My problem is that some clients have multiple email address all croweded into a single field.
I have created a program that checks each individual character to assure that it is a properly set up database I.E. Name.Name@server.ext This has caught quiet a few emails that are would not send. I believe I can modify this program to break down the multi emails in a field to single emails to a field. However, I am not sure how to copy the field of an extracted email to a proper field and record. Any help with this is greatly appreciated.
The program to extract the emails, check their extensions and mark them as good or bad is...

Option Compare Database
Option Explicit
'SET CRITERIA TO OPEN EMAIL_CHECK_LIST FORM
Private Sub Form_Open(Cancel As Integer)
'DEFINE VARIABLES
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim Place As Integer 'Counts the number of places in the Email Field
Dim RecCount As Integer, RecTotal As Integer
Dim Cnt As Integer, Cntr As Integer
Dim Hold As String 'Builds Single Email
Dim ChkIt As String 'Checks the Character of the Email Field for ;
Dim Chk As String

'SET DATABASE AND OPEN RECORDSET
Set DB = CurrentDb
Set rs = DB.OpenRecordset("Email_Only_List")

'SET TOTAL RECORDS, RECORD COUNT AND GOTO FIRST RECORD OF RECORDSET
Me.Recordset.MoveLast
RecTotal = Me.Recordset.RecordCount
RecCount = 0
Me.Recordset.MoveFirst

'GO THROUGH EACH RECORD INDIVIDUALLY
Do While RecCount < RecTotal
Place = 0
Cnt = 90
Cntr = 0
Hold = ""

'CHECK EACH CHARACTER IN FIELD
Do Until Cntr = Cnt
Cntr = Cntr + 1
Place = Place + 1
ChkIt = Mid(Email, Place, 1)

'CHECK FOR ENDING OF EMAIL ADDRESS OR FIELD
Select Case ChkIt
Case ";"
EmailCheck (Hold)
'END OF EMAIL IF ; VERIFY EMAIL TO BE GOOD OR BAD
Case ""
EmailCheck (Hold)
'END OF FIELD IF ' ' VERIFY EMAIL TO BE GOOD OR BAD
Exit Do
Case Else
Hold = Hold + ChkIt
'INCREMENT VARIABLE AND CHECK NEXT CHARACTER
End Select
Loop

'INCREMENT COUNT AND GO TO NEXT RECORD
RecCount = RecCount + 1
Me.Recordset.MoveNext
Loop
'CLOSE EMAIL_CHECK_LIST FORM AND OPEN BAD_EMAIL_FRM TO CHECK ON ERRORS
DoCmd.Close
DoCmd.OpenForm "Bad_Email_frm"
End Sub
'VERIFY EMAIL AS GOOD OR BAD
Private Sub EmailCheck(Hold)
On Error GoTo Err_Handle_Click
Dim ChkIt As String
ChkIt = Right(Hold, 3)
Select Case ChkIt
Case ".md"
'Passes Through, Does Nothing
Case ".us"
'Passes Through, Does Nothing
Case "Com"
'Passes Through, Does Nothing
Case "edu"
'Passes Through, Does Nothing
Case "gov"
'Passes Through, Does Nothing
Case "mil"
'Passes Through, Does Nothing
Case "net"
'Passes Through, Does Nothing
Case "org"
'Passes Through, Does Nothing
Case Else (I am thinking that at this point is where I create the new field in the new record)
'IF NOT MARKED AS BAD THEN MARK AS BAD, ELSE END IF
If Chk = 0 Then
Forms!Email_Check_List.Chk = -1
End If
End Select

Exit_Finish_Up_Click:
Exit Sub
Err_Handle_Click:
MsgBox Err.Description
Resume Exit_Finish_Up_Click

End Sub


Thank you for your help with this program.

Robert M

 

[jal]

You can have a separate table called Emails (2 columns shown below). For example this employee has three email addresses

EmployeID.......EmailAddress
12345.............john@yahoo.com
12345.............john@hotmail.com
12345.............john@gMail.com

 

[Robert M]

Thank you jal, that is the direction I am probably going to have to take, however, as an example the original field with the email address would be...

john@yahoo.com;john@hotmail.com;john@gMail.com

And what I am wanting to do is break that example down to a seprate field that would take the single record and create 3 records as in...

Rec# EmailField
1 john@yahoo.com
2 john@hotmail.com
3 john@gMail.com

my problem is that I am unfamiliar with that part of code that would open the table and copy the data to the field and advance the record to copy the next email.

Thank you for your help and suggestion of a seporate database

Robert M

 

[jal]

Ok attached is a sample. You'll see that the "EmailAddresses" table is initially empty. But when you click the button on the form, the code populates the table by breaking up the long email strings into individual emails. Maybe that will get you started.

 

[Robert M]

Thanks for the forum.zip. Running it caused it to crash right at the begining so I guess it is more for show and study. I do have my email extration program up and running where it copies the emails. Only problem is that it also copies the multiple emails to the same record. Will study up on the 'split' command. Never have seen that and it looks like it will solve a lot of my problems with this email extraction program.
Thanks for your help jal.

Robert M

 

[Robert M]

jal; here is the modified program you so graciously sent me. Thank you for your help with that. The program is running well and copying but it is not breaking down the multiple emails from a single record to multiple records.

Private Sub Form_Open(Cancel As Integer)

Dim RSource As DAO.Recordset
Dim RTarget As DAO.Recordset
Dim Hold As String, CopyGroup() As String
Dim CopyCat As Variant

Set RSource = CurrentDb.OpenRecordset("EMail_Only_List")
Set RTarget = CurrentDb.OpenRecordset("EMailOnly_tbl")

'DELETE RECORDS IN EMailOnly_tbl
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * FROM EMailOnly_tbl"
DoCmd.SetWarnings True

Do While RSource.EOF = False
Hold = RSource("Email")
Hold = Replace(Hold, ";", "")
CopyGroup = Split(Hold)

For Each CopyCat In CopyGroup

RTarget.AddNew
RTarget("EMO") = RSource("Email")
RTarget.Update

Next

RSource.MoveNext

Loop

MsgBox "Fine!"

End Sub

 

[jal]

Two lines of code look suspicious here. First of all, you're using an empty string (see the red below).

Hold = Replace(Hold, ";", "")

You're supposed to be inserting a blank space because the Split command splits on spaces.

Hold = Replace(Hold, ";", " ")

Secondly, you're supposed to be adding the split emails to the new table. Instead you are adding the original unsplit email:

RTarget("EMO") = RSource("Email")

Try this instead:

RTarget("EMO") = CopyCat


Finally, I'm left confused as to why your Target table only has one column? In the sample I gave you I proposed two columns:

EmplID....EmailAddress

I'm not saying you're wrong - I am just not sure what you're situation is that you would only need one column.

 

[Robert M]

Thanks for finding my oversights. I'll get them changed out and try again. Also as to only a single field in my table, twas due to my single mindedness of just needing one field to hold the email. Didn't think about needing an ID field so as to back track and find the original. Will take care of that too.

Thanks for your help and patience

Robert M

 

[smig]

you must have 2 fields in your emails table, or how the hell will you know later who is the ouner of every email

 

[Robert M]

By the simple process of matching the emails of the one table with the emails of the other. A bit hard and a little more time consuming but doable. Got the corrections made now smig, thanks for your advice.

Robert M