Data from form to insert query (1 Viewer)

forson96 · Sep 13, 2019

Hi guys, I suspect I am going to need to use some SQL here and I am slightly lost. Would appreciate some help.

I have created a DB which tracks my daily sales activity (e.g. phone calls, emails). I have tables for:

tCustomer
tCompany
tOutreach

It's all pretty simple stuff, within tOutreach I have variables such as:

OutreachType, OutreachDate, FollowUpDate, FollowUpType, CustomerID, Notes, FollowUpActioned?

I have a daily to do list taken from FollowUpDate/Type which I sift through most mornings and typically I will do things by Type, so for example on OutreachType is "Marketing Email", this goes out to clients monthly and I have a daily list to work with. Once I have sent all the emails, I would like to be able to automatically update the boolean FollowUpActioned to TRUE and then create a new record for each individual recording the outreach and assigning myself a fresh follow up action for each individual, as the Outreach type was all the same, the follow up actions will also be exactly the same.

The update part of this process I understand and is quite simple. I have a form where I select the date and outreach type (so to continue my example I would select today's date and "Marketing Email" in my form and a query will pull all the records of that type and update the boolean to true.

Creating the new records for each individual is more complex it seems and I would appreciate your help.

I have created a subsequent query that can call all of the CustomerID values based on the same criteria provided in my form. Within the same form I have created a space to include the updated details for each value outside of customer ID. What I need to create, is some sort of function that will write those values for each customer, into the Outreach form.

arnelgp · Sep 13, 2019

after updating, you need to make exactly same record as the one you updated for follow-up?

forson96 · Sep 13, 2019

arnelgp said:
after updating, you need to make exactly same record as the one you updated for follow-up?

Thanks for getting back to me. Not quite, the two operations I need to happen are:

1) The boolean is updated to true to reflect the fact I have taken the action (this I can do)

2) There is a new record created for each individual I emailed, the data for the values OutreachType, OutreachDate, FollowUpDate, FollowUpType, Notes, FollowUpActioned will be provided by a form. The data for CustomerID is taken from the same style of search used to update the records.

Hope that makes sense

arnelgp · Sep 13, 2019

can you give an example of what the new record will have after you update the current record FollowUpActioned to True.

forson96 · Sep 13, 2019

arnelgp said:
can you give an example of what the new record will have after you update the current record FollowUpActioned to True.

Yeah of course, the previous record would be something like this (I'm relatively new to this so not certain how to format this style of stuff:

OutreachType: Email
OutreachDate: 05/09/2019
FollowUpDate: 13/09/2019
FollowUpType: Email Marketing
CustomerID: 123
Notes: Follow up with monthly update newsletter
FollowUpActioned? FALSE

The above record then needs to be changed to true, the record I need to input to the table tOutreach would read:

OutreachType: Email Marketing
OutreachDate: 13/09/2019
FollowUpDate: 17/09/2019
FollowUpType: Call
CustomerID: 123
Notes: Call regarding newsletter
FollowUpActioned? FALSE

I need to add a record for everyone who I sent the newsletter that day, I have a query that finds me the list of customerID I need to include, I am uncertain how to write the other elements in as these values will come from a form not from a table I have currently.

Thanks again.

arnelgp · Sep 13, 2019

insert a Class Module in VBA (name it clsOutReach)

Code:

Option Compare Database
Option Explicit

Public OutReachType     As String
Public OutReachDate     As Date
Public FollowUpDate     As Date
Public FollowUpType     As String
Public CustomerID       As Long
Public Notes            As String
Public FollowUpActioned As Boolean

add a button in your form to update FollowUpActioned field to True, and at the
same time add those new records to tOutReach table.
add code to it's Click Event:

** replace yourQuery with the correct name of your query:

Code:

Private Sub button_Click()
Dim thisOutReach As clsOutReach
Dim colOutReach As New Collection
Dim ID As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long

Const YOUR_QUERY As String = "yourQuery" '!!relace yourQuery with your query name
    
    'save the current customer id, if there is
    ID = Nz(Me!CustomerID, 0)
    'save any changes first
    Me.Dirty = False
    'insert the values to your tOutreach where customerID is in your Query
    Set db = CurrentDb
    'get the records from your query
    Set rs = db.OpenRecordset("select T.* from tOutReach As T Where T.CustomerID In " & _
                    "(Select CustomerID From " & YOUR_QUERY & ");")
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            'save first to thisOutReach
            Set thisOutReach = New clsOutReach
            thisOutReach.CustomerID = !CustomerID
            thisOutReach.FollowUpActioned = False
            thisOutReach.FollowUpDate = !OutReachDate + 5 '?
            thisOutReach.FollowUpType = "Call"
            thisOutReach.Notes = "Call regarding newletter"
            thisOutReach.OutReachDate = !FollowUpDate
            thisOutReach.OutReachType = "Email Marketing"
            
            'add to colOutReach collection
            colOutReach.Add thisOutReach
            
            'update FollowupActioned to True
            .Edit
            !FollowUpActioned = True
            .Update
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    'insert new record to tOutReach
    For i = 1 To colOutReach.count
        Set thisOutReach = colOutReach(i)
        With db.CreateQueryDef("", "Insert Into tOutReach " & _
                    "(CustomerID,FollowupActioned, FollowUpdate, FollowUpType, Notes, OutReachDate, OutReachType) " & _
                    "SELECT p0, p1, p2, p3, p4, p5, p6;")
            .Parameters(0) = thisOutReach.CustomerID
            .Parameters(1) = thisOutReach.FollowUpActioned
            .Parameters(2) = thisOutReach.FollowUpDate
            .Parameters(3) = thisOutReach.FollowUpType
            .Parameters(4) = thisOutReach.Notes
            .Parameters(5) = thisOutReach.OutReachDate
            .Parameters(6) = thisOutReach.OutReachType
            .Execute (dbFailOnError)
        End With
    Next I
    'requery form
    Me.Requery
    'if there is previous record in form it will be reset, so go back to it
    If ID > 0 Then
        Me.RecordSet.FindFirst "CustomerID=" & ID
    End If
    Msgbox "FollowUpActioned field updated to True and new records added to tOutReach table"
End Sub

Data from form to insert query (1 Viewer)

forson96

New member

arnelgp

..forever waiting... waiting for jellybean!

forson96

New member

arnelgp

..forever waiting... waiting for jellybean!

forson96

New member

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread