I have two tables. One a standard list of 15 emails and another multiple blocks of emails for different matters. I want to update table 2 (1 Viewer)

atrium

Registered User.
Local time
Tomorrow, 00:27
Joined
May 13, 2014
Messages
348
I need to make sure that all table 1 entries are in each of the blocks of emails in table tow.
Table 1
StandardMatterEmails - Fields are - StandardEmailsId, StandardEmail, StabdardOwner

Table 2
MatterEmails - Fields are - MatterEmailId, MatterId, EmailAddress, EmailOwner

MatterId defines the email blocks on table two

My simple logic would be
Outer loop would be table 1 - Read first entry of table one - compare it with each entry in the first block-

If you find it jump to the next block, A change in matterId
Means that you haven't found it in the 1st block so add the table 1 email to the first block
then compare to block 2 and repeat the process

When EOF on table 2
Read the 2nd entry in table 1 and go through to EOF on table 2
When EOF on table 1 the process is finished


Has anyone written any code that would do the above please

Any help would be greatly appreciated
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
why don't you just use Insert Query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
Code:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select EmailAddress, EmailOwner from MatterEmails Group By EmailAddress, EmailOwner;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
With rst
    If Not (.BOF And .EOF) Then
        .MoveLast
        .MoveFirst
    End If
    Do While Not .EOF
        With dbs.CreateQueryDef("", _
                "Insert Into MatterEmails (EMailAddress, EmailOwner, MatterID) " & _
                "Select p1, p2, StandardEmailsId " & _
                "From StandardMatterEmails Where StandardEmailsId Not In (" & _
                "Select MatterID From MatterEmails Where EmailAddress = p1 And EmailOwner = p2);")
            .Parameters("p1") = rst!EmailAddress
            .Parameters("p2") = rst!EmailOwner
            .Execute dbFailOnError
        End With
        .MoveNext
    Loop
    .Close
End With
 

atrium

Registered User.
Local time
Tomorrow, 00:27
Joined
May 13, 2014
Messages
348
Thanks Arnelgp,

I have used your code but have problems with it, probably I made changes to it.
Do you mind putting some comments in your code to help me understand how it is doing it
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:27
Joined
May 7, 2009
Messages
19,169
the code is simply adding records to MatterEmails table when
StandardEmailsId of table StandardMatterEmails is Not found
on MatterEmails for same EmailAddress and EmailOwner.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:27
Joined
Feb 19, 2002
Messages
42,970
I need to make sure that all table 1 entries are in each of the blocks of emails in table tow.
The problem is that you are missing a THIRD table. This is the junction table that connects the two tables. With the proper junction table, you can update the email string in one record and that would be reflected in all the places that refer to it.

A common example is an Order entry application. You have Customers, Orders, Products. The glue is OrderDetails which is a junction table that shows which products are on which orders. An Order has 1 customer so the relationship between customers and orders is 1-m. An Order might have multiple products so the relationship between Orders and Products is m-m and that requires a junction table to implement correctly. I believe this is the situation you have. It is just not currently modelled correctly.
 

atrium

Registered User.
Local time
Tomorrow, 00:27
Joined
May 13, 2014
Messages
348
arnegp I have had to revert to the following but it stops with an error "Wend without a While" and highlights the Wend in the third last line. I only have 2 Whiles with matching Wends. It has to be something else. Any ideas



Code:
    ' Go through the StandardMatterEmails file and if an entry isn't in the matter table add it or go to the next one
    Dim strSQL As String
    Dim rs2 As Recordset
   
    strSQL = "SELECT * FROM StandardMatterEmails Orderby MatterId"   'define the SQL result that you want to loop
    Set rs2 = CurrentDb.OpenRecordset(strSQL)
    If Not rs2.BOF And Not rs2.EOF Then
        rs2.MoveFirst
        While (Not rs2.EOF)
           'Check if each of the standard emails are recorded for each Matter file
           Me.StandardEmailFld = rs2![StandardEmail]
           Me.StandardOwnerFld = rs2![StandardEmailOwner]
           ' ----------------- We have the first Standard Matter Email - set up looping through the first block of the MatterEmails
           Dim CurrentMatterId As Long
           CurrentMatterId = 0
           Dim rs3 As Recordset
           strSQL = "SELECT * FROM MatterEmails"   'define the SQL result that you want to loop
           Set rs3 = CurrentDb.OpenRecordset(strSQL)
           If Not rs3.BOF And Not rs3.EOF Then
              rs3.MoveFirst
              While (Not rs3.EOF)
                 ' Read the first record in the first block
                 Me.MatterIdFld = rs3![MatterId]
                 Me.EmailAddressFld = rs3![EmailAddress]
                 If CurrentMatterId <> Me.MatterId Then
                    ' ----- Then its a new block
                    CurrentMatterId = Me.MatterIdFld
                    Me.OnFileFld = (Nz(DLookup("EmailAddress", "MatterEmails", "[EmailAddress] = '" & Me.StandardEmailFld & "' AND [MatterId] = " & CurrentMatterId)))
                    If Len(Me.OnFileFld & vbNullString) = 0 Then
                       ' Add an entry to MatterEmails     CurrentMatterId = rs3![MatterId]
                       DoCmd.SetWarnings False
                       DoCmd.OpenQuery "AddStandardMatterEmailQry"
                       DoCmd.SetWarnings True
                       Me.AdditionsFld = Me.AdditionsFld + 1
                    Else
                       '---- it's in this one then move to the next block of MatterEmails
                     
                    End If
                    '------ Move to the next block
                   
                 End If
                 rs3.MoveNext
              Wend
           rs3.Close
           rs2.MoveNext
        Wend
       
     End If
     rs2.Close
     '---------------------------------------------------------------------END Matter UPDATE EMAILS-----------------------------------------
 
Last edited:

atrium

Registered User.
Local time
Tomorrow, 00:27
Joined
May 13, 2014
Messages
348
Thanks guys I was able to get the above code working with a bit of tweaking.

Thanks again for your help
 

Users who are viewing this thread

Top Bottom