VBA / SQL Insert Query

[Chrism2]

Hello Friends,

As part of my DB project, I often use

Insert Into

VBA to automate append queries for my users.

This works really well, but I have a situation which is a bit complex for me to get my head around.

My DB contains a module with three tables:

tblProjects
tblProjectTimelines (Stages of the Project)
tblProjectNotes (Memos and Documents)

One Project can have many timelines (linked by projectID)
One Timeline can have many notes (linked by projectTimelineID)



I have a system where "Projects" can be created from a set of Pre-fabricated rules. These "Pre-Fabs" are stored in three tables

tblProjects_Prefabs
tblProjectTL_Prefabs
tblProjectNotes_Prefabs

Again, the relationship is as it is before.

I have a form where the user can click a button to select a project style and save themselves having to work through adding each step manually.

So,

Dim intProjectPrefabID, intCustID, intStaffID As Integer
Dim sql, strProjectStatus, strProjectContact, strDate As String

'the ints here are defined by some forms on the box, you get the idea


sql = ""
sql = sql & "INSERT INTO tblProjects ( ProjectTitle, ProjectBrief, ProjectType, customerID, staffID, ProjectStatus, ProjectContact, CreatedDate ) "
sql = sql & "SELECT tblProjects_Prefabs.ProjectTitle, tblProjects_Prefabs.ProjectBrief, tblProjects_Prefabs.ProjectType, " & intCustID & " AS customerid, " & intStaffID & " AS staffid, '" & strProjectStatus & "' AS ProjectStatus, '" & strProjectContact & "' AS ProjectContact, #" & strDate & "# AS CreatedDate "
sql = sql & "FROM tblProjects_Prefabs "
sql = sql & "WHERE (((tblProjects_Prefabs.ProjectPrefabID)=" & intProjectPrefabID & "));"

I callback to the record I've just created with:

Dim newProj As Integer

newProj = DLookup("ProjectID", "tblProjects", "[CreatedDate] = #" & strDate & "#")

(There are not many users, so the Date and time seems to be a safe way to get the ID I've just created).


I then run this:

sql = ""
sql = sql & "INSERT INTO tblProjectTimelines ( projectID, SuggestedTimescale, TimeLineTitle, TimeLineMemo, StageLevel, TimeLineCreated, staffID ) "
sql = sql & "SELECT " & newProj & " AS projectID, tblProjectTL_Prefabs.SuggestedTimescale, tblProjectTL_Prefabs.TimeLineTitle, tblProjectTL_Prefabs.TimeLineMemo, tblProjectTL_Prefabs.StageLevel, #" & strDate & "# AS TimeLineCreated, " & intStaffID & " AS staffID "
sql = sql & "FROM tblProjectTL_Prefabs "
sql = sql & "WHERE (((tblProjectTL_Prefabs.Project_PrefabID)= " & intProjectPrefabID & "));"

Which works just fine... the new Project gets populated with all of the stages from the prefab.

...and then I run into a wall! :banghead:

How do I loop through each NEW record in tblProjectTimelines; and input the pre-staged notes from the corresponding tblProjectNotes_Prefabs that goes with each prefabricated Timeline within the project?

I've attached a cut out of this module - if anyone can comment....


Thank you!

 

[Chrism2]

Well, I have this half sussed. I figured I needed an array and For, Next Loop. This code does pretty much what I need it to do:

'The final step requires us to install the notes into each ProjectTimeline we just made.

'We already know the Project we just created, as it is stored ar newProj
'We have to cycle through each Timeline based on the same project ID and insert the requisite Notes.

Dim dbs As Database, rst As Recordset, strSQL, subSQL, Message As String
Dim varRecords As Variant, intI As Integer, intJ, intCounter As Integer


' Return reference to current database.
Set dbs = CurrentDb
Message = ""

' Build SQL that returns specified fields.
strSQL = strSQL & "SELECT tblProjectTimelines.ProjectTimelineID "
strSQL = strSQL & "FROM tblProjectTimelines "
strSQL = strSQL & "WHERE (((tblProjectTimelines.projectID)=" & newProj & "));"


Set rst = dbs.OpenRecordset(strSQL) ' Open dynaset-type Recordset object.

intCounter = 0 ' Need a counter of records, matching up to the stage level of each inserted record. The prefabs have a strict 1-2-3-4 order for this reason

rst.MoveLast ' Move to end of recordset
rst.MoveFirst ' Return to first record.

' Return all rows into array.
varRecords = rst.GetRows(rst.RecordCount)
' Find upper bound of second dimension.
For intI = 0 To UBound(varRecords, 2)
    
    'Get the Value of the tblProjectTimeLine we are currently looking at


    
    ' Find upper bound of first dimension.
        For intJ = 0 To UBound(varRecords, 1)
        
        'Now we have to insert the requsite note for EACH Timeline
        'varRecords(intJ, intI) gives you the ProjectTimelineID you have just inserted
        
            intCounter = intCounter + 1
            subSQL = ""
            DoCmd.SetWarnings (False)
           
            subSQL = subSQL & "INSERT INTO tblProjectNotes ( originatingID, ProjectNoteMemo, projecttimelineID ) "
            subSQL = subSQL & "SELECT tblProjectNotes_Prefabs.ProjPrefabNoteID, tblProjectNotes_Prefabs.ProjectNoteMemo, " & varRecords(intJ, intI) & " AS projecttimelineID "
            subSQL = subSQL & "FROM tblProjects_Prefabs RIGHT JOIN (tblProjectTL_Prefabs RIGHT JOIN tblProjectNotes_Prefabs ON tblProjectTL_Prefabs.ProjTLPrefID = tblProjectNotes_Prefabs.ProjectTLID) ON tblProjects_Prefabs.ProjectPrefabID = tblProjectTL_Prefabs.Project_PrefabID "
            subSQL = subSQL & "WHERE (((tblProjects_Prefabs.ProjectPrefabID)=" & intProjectPrefabID & ") AND ((tblProjectTL_Prefabs.StageLevel)=" & intCounter & "));"

            DoCmd.RunSQL subSQL
            DoCmd.SetWarnings (True)
            
           'Need some recordset magic here
           
            
        
        Next intJ
Next intI
rst.Close
Set dbs = Nothing

My remaining problem is that I have used (and wish to continue to use) the Attachment field in tblProjectNotes. (Inserting the correct ProjectNoteDocument from tblProjectNotes_Prefabs into tblProjectNotes).

As we know, Attachment fields are multi-field and you can't use SQL Insert into statements to copy the data across. (My data is stored on Sharepoint lists in real life, so this field is really, really handy).

I know I need to perform this task within the nested For, Next loop using the ADO / Openrecordset method, but I'm not sure how to use this to take the correct info out of the source and put it in the destination.

I have put a field in tblProjectNotes called "originatingID" which corresponds to the "ProjPrefabNoteID" in tblProjectNotes_Prefabs, so I'm hoping to use that.

If anyone has any thoughts, it would be great.