add new records to table

[cbrsix]

Hi,



I have 3 tables, ClientList, AccountList, LogHistory. The ClientList has the client's resident address. The AccountList has the client's mailing address, if it is different, if not, that field is left blank. The LogHistory will record what was sent to the client and the address it was sent to. This is where I want to add the new record to.

I created a query that contains the client's name, address, mailing address and any other relevant information (for this purpose it is not necessary to list all of them). I also have an unbound form that contains a "Print" button and a "Cancel" button. If the query has results on any given day, the unbound form opens based on this code.

If DCount("*", "NameOfQuery") > 0 Then
               DoCmd.OpenForm "NameOfPopUpForm"
               End If

If the user clicks "Print" then I want the client's corresponding reports to print (which I am having no problem doing) and I want the LogHistory to have a new record added showing the client's information and the appropriate address (resident or mailing) in the appropriate fields in the LogHistory table. The below code is what I have that adds a new record based on the current forms information.

Dim db As DAO.Database
    Dim rs As DAO.Recordset
     
     Set db = CurrentDb()
     Set rs = db.OpenRecordset("log", dbOpenDynaset, dbAppendOnly)

            rs.AddNew
                rs!accountid = Me.Id
                rs!addysent = IIf(IsNull(Me.mailaddy), Me.addy, Me.mailaddy)
            rs.Update
     rs.Close
     db.Close
     Set rs = Nothing
     Set db = Nothing

I also have this code that I use to send emails to the clients and update fields in the query.

    Dim rst As Object
    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myItem As Object
    Dim myAttachments, myRecipient As Object
    Dim recipient As String
    Dim file_name As String
    Dim mySubject As Object
    Dim dbs As Object
    
    strSql = "PRIMARYNAMEq" 'Select the Query where you want your information to be drawn from
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSql)

        rst.MoveFirst
        While Not rst.EOF
            recipient = "D" 'This is the email address that you corresponds to your recipient
            Set myOlApp = CreateObject("Outlook.Application")
            Set myItem = myOlApp.CreateItem(olMailItem)
            Set myAttachments = myItem.Attachments.Add(Attachment)
            Set myRecipient = myItem.Recipients.Add(recipient)
            '"Message Subject String Here"
            myItem.Subject = "XXXXXX"
            '"Put Message Body Text Here"
            myItem.Body = "Hi"
            myItem.Display
            rst.Edit
            rst!Sent = True
            rst.Update
            rst.MoveNext
        Wend
        'DoCmd.Close acForm, "XXXXX" 'Closes the form
        'DoCmd.OpenForm "EmailConfirmation" 'Opens Email Confirmation Form
        Set myRecipient = Nothing
        Set myAttachments = Nothing
        Set myItem = Nothing
        Set myOlApp = Nothing
        Set rst = Nothing

This code moves through the query until EOF. I thought I would be able to integrate parts of the two codes to get it to work but I can't figure it out since the email code does not add any new records to a third table.

I have looked around and tried a few things, but can't seem to get it to work how I want it to. Any suggestions?

[ranman256]

Do not use code to add records this way. (you can, yes, but its the hard way of doing things)
This is what queries are for. append or update queries.


update table set [sent] = true where [id] = id

[cbrsix]

I thought about doing an append query but I don't know how to properly add the if statement in there to account for the address

[drexasaurus]

Do you simply want to add the email address it was sent to for the log table? And then call it from the email function? It seems to me you'd want to consider altering the log table to contain email address information (since it seems to possible only record mailing address info presently?), adding an email address field or at least a boolean switch to indicate what kind of address we're looking at.

I would then make the code to alter the log table a sub or function (in a module) that takes an id, the address (logic of which address to use probably outside of the function), and one for an email log, something like this (definitions):
Public Sub LogMailedToAddress(id as Long, address As String) and Public Sub LogSentToEmail(id As Long, emailAddress As String)
(I started to think about the logic to do it in one function, but it's possible it might make it complicated)

You would then insert the call to the sub where ever you needed to log these items throughout the rest of your development without worrying as much as how to insert one process into another.

[cbrsix]

I am not sending things via email with this function. That email code I posted was separate code that I tried to use to integrate with this

Dim db As DAO.Database
    Dim rs As DAO.Recordset
     
     Set db = CurrentDb()
     Set rs = db.OpenRecordset("log", dbOpenDynaset, dbAppendOnly)

            rs.AddNew
                rs!accountid = Me.Id
                rs!addysent = IIf(IsNull(Me.mailaddy), Me.addy, Me.mailaddy)
            rs.Update
     rs.Close
     db.Close
     Set rs = Nothing
     Set db = Nothing

because in the email code, I had it moving to the next item through the query.

I want to have the resident or mailing address entered to the LogHistory table.

[ssanfu]

In your email code, be aware that in the line:

Dim myAttachments, myRecipient As Object

"myAttachments" is declared as a variant and
"myRecipient" is declred as an object.



In VBA, unlike other languages, you must explicitly declare a variable as a type.

Dim myAttachments As Object, myRecipient As Object

That is, unless you meant to delcare "myAttachments" as a variant.........

[drexasaurus]

Oh? If you want to loop through each individual record in the form's recordset, you could do something like this:

* I would still add the code you have to a subroutine called something like LogMailingAddress, that way you won't have to worry about adding rows to one recordset while looping through another (separate the logic)
* Then I would loop through the data on the form like you've demonstrated in the email example. On the form of addresses to be mailed to, maybe you can try to loop through a clone of the form's recordset something like:

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
With rst
    If .RecordCount > 0 Then
        Do While Not .EOF
            Debug.Print ![Id].Value
            LogMailingAddress ![Id].Value, ![addy].Value, nz(![mailaddy].value)
        .MoveNext
        Loop
    End If
End With

I hope I got what you're trying to do this time