Copy and insert records from the same table! (1 Viewer)

[Lkwdmntr]

Hello everyone,

I am hoping you might be of some help. I want to copy the records from the previous week into the same table, but with a different weekly Id.

In my code I assigned the previous week to the "prevPlanID" and the current week to the "curWeeklyPlanID". Here is the SQL I used, but it's not working.

InsertSQL = "INSERT INTO tblFoodGroupsPerWeeklyPlan([FoodGroupIDFK], [Index], [WeeklyPlanIDFK], [IncludedInDiet]) " & _
            "SELECT  tblFoodGroupsPerWeeklyPlan.[FoodGroupIDFK], tblFoodGroupsPerWeeklyPlan.[Index], " & prevPlanID & " As tblFoodGroupsPerWeeklyPlan.[WeeklyPlanIDFK], tblFoodGroupsPerWeeklyPlan.[IncludedInDiet] " & _
            "FROM tblFoodGroupsPerWeeklyPlan " & _
            "WHERE tblFoodGroupsPerWeeklyPlan.WeeklyPlanIDFK = " & curWeeklyPlanID & ";"

If anyone can figure out what I did wrong, please let me know.

Thanks

 

[CJ_London]

what does 'not working' mean?

• you get an error? if so what is the error?
• updates with the wrong data? if so, show examples of before, after and expected data
• does nothing?
• Have you used debug.print the print the sql string generated, then copy/pasted to a query sql window and see what the result is?
• Also how are you executing the sql?

5 questions, 5 answers required

 

[Lkwdmntr]

what does 'not working' mean?

• you get an error? if so what is the error?
• updates with the wrong data? if so, show examples of before, after and expected data
• does nothing?
• Have you used debug.print the print the sql string generated, then copy/pasted to a query sql window and see what the result is?
• Also how are you executing the sql?

5 questions, 5 answers required

Hi CJ, Thanks for getting back so quickly.

I am not getting an error, it's just not doing anything.

I don't know how to use the debug. Print feature.

I am using an on_click event to execute the sql. Here is the complete code for that event.

Private Sub btnCopyPreviousWeeksGroups_Click()

Dim loopNum As Integer, weekNum As Integer, prevWeek As Integer, prevLoop As Integer, prevPlanID As Integer, curWeeklyPlanID As Integer, InsertSQL As String


'Lookup the loop and week number for selected week
loopNum = DLookup("[Loop]", "tblWeeklyPlans", "[WeeklyPlanID] = " & Me.lstWeekyPlan.Column(0))
weekNum = DLookup("[WeekNumber]", "tblWeeklyPlans", "[WeeklyPlanID] = " & Me.lstWeekyPlan.Column(0))
curWeeklyPlanID = Me.lstWeekyPlan.Column(0)


'Calculate the previouse loop and week
    If loopNum > 1 Then
        If weekNum = 1 Then
            prevWeek = 12
            prevLoop = loopNum - 1
        Else
            prevWeek = weekNum - 1
            prevLoop = loopNum
        End If
    Else
        If weekNum > 1 Then
            prevWeek = weekNum - 1
            prevLoop = loopNum
        End If
    End If




'Lookup the previous Plan ID
prevPlanID = DLookup("[WeeklyPlanID]", "tblWeeklyPlans", "[UserID] = " & Me.txtUserID & " AND [Loop] = " & prevLoop & " AND [WeekNumber] = " & prevWeek)


InsertSQL = "INSERT INTO tblFoodGroupsPerWeeklyPlan([FoodGroupIDFK], [Index], [WeeklyPlanIDFK], [IncludedInDiet]) " & _
            "SELECT  tblFoodGroupsPerWeeklyPlan.[FoodGroupIDFK], tblFoodGroupsPerWeeklyPlan.[Index], " & prevPlanID & " As tblFoodGroupsPerWeeklyPlan.[WeeklyPlanIDFK], tblFoodGroupsPerWeeklyPlan.[IncludedInDiet] " & _
            "FROM tblFoodGroupsPerWeeklyPlan " & _
            "WHERE tblFoodGroupsPerWeeklyPlan.WeeklyPlanIDFK = " & curWeeklyPlanID & ";"


'Me.frmFoodGroupsPerWeeklyPlan_subf.SetFocus
'Me.btnCopyPreviousWeeksGroups.Visible = False
Me.cmdChooseFoodGroupsforWeek.Visible = False
Me.frmFoodGroupsPerWeeklyPlan_subf.Requery
Me.frmFoodGroupsPerWeeklyPlan_subf.Visible = True


Exit_PROC:
    Exit Sub
    
Err_PROC:
    MsgBox Error$
    Resume Exit_PROC




End Sub

 

[The_Doc_Man]

First, in the code segment you offered above, you defined the string but you never executed it. Normally you would use a syntax similar to

CurrentDB.Execute InsertSQL, dbFailOnError

However, I suspect there is more to it than this. Your selection syntax SEEMS to call out more than one table which will potentially lead to a Cartesian JOIN, which results in multiplying records by doing combinations and permutations.

 

[Lkwdmntr]

Thanks for the feedback, it helped me jog my brain a bit and I figured it out. I figured out the correct way to put it into a query. Heres the sql I ended up using in case you're curious.

INSERT INTO tblFoodGroupsPerWeeklyPlan ( FoodGroupIDFK, [Index], WeeklyPlanIDFK, IncludedInDiet )
SELECT tblFoodGroupsPerWeeklyPlan.FoodGroupIDFK, tblFoodGroupsPerWeeklyPlan.Index, [Forms]![frmSelectWeektoCreateMealPlan]![txtWeeklyPlanID] AS Expr1, tblFoodGroupsPerWeeklyPlan.IncludedInDiet
FROM tblFoodGroupsPerWeeklyPlan
WHERE (((tblFoodGroupsPerWeeklyPlan.WeeklyPlanIDFK)=[TempVars]![TempPrevPlanID]));

Thanks for the assistance!

 

[Gasman]

I don't know how to use the debug. Print feature.

Look at the link in my signature. It is time you learnt. It will help you a great deal, believe me.

 

[Lkwdmntr]

Look at the link in my signature. It is time you learnt. It will help you a great deal, believe me.

I most certainly will. Thanks!