Copying data from a table and putting it back in the same table (1 Viewer)

[Lkwdmntr]

Hi, I could use some advice on this one. I have to copy all the data from one form for a certain Meal Plan and put it back into the same table with a different Plan ID. I was trying to use an append query, but am having some trouble.

I have the plan id saved into a temp var called TempPlanID, or I could use a value from the form.

Any help will do, I have been at a while and am going to come back to it tomorrow, maybe with some helpful solution.
Thanks!

 

[theDBguy]

You can copy a record using several methods. Are you trying to copy all the fields or just some of them?

 

[Lkwdmntr]

You can copy a record using several methods. Are you trying to copy all the fields or just some of them?

Most of them. To explain exactly what I'm doing. The program creates weekly meal plans. After the first week, I want to copy the previous week's data. I have an append query but I'm not sure how to replace the MealDate with the MealDate + 7 days, and the plan Id needs to be replace with the new plan Id, which I have stored in a variable in the code and as a TempVars TempPlanID. Here is the append query I made, but the dates are the same and the Plan Id is zero.

INSERT INTO tblMealComponentsPerWeeklyPlan ( ActionsPerWeeklyPlanIDFK, MealDate, WeekDay, DOW, ComponentType, StartTime, FoodItem, Qty, Measurement, Sequence, MealCatagory )
SELECT tblMealComponentsPerWeeklyPlan.ActionsPerWeeklyPlanIDFK, tblMealComponentsPerWeeklyPlan.MealDate, tblMealComponentsPerWeeklyPlan.Weekday, tblMealComponentsPerWeeklyPlan.DOW, tblMealComponentsPerWeeklyPlan.ComponentType, tblMealComponentsPerWeeklyPlan.StartTime, tblMealComponentsPerWeeklyPlan.FoodItem, tblMealComponentsPerWeeklyPlan.Qty, tblMealComponentsPerWeeklyPlan.Measurement, tblMealComponentsPerWeeklyPlan.Sequence, tblMealComponentsPerWeeklyPlan.MealCatagory
FROM tblMealComponentsPerWeeklyPlan
WHERE (((tblMealComponentsPerWeeklyPlan.WeeklyPlanIDFK)=[TempVars]![TempPrevPlanID]));

I was thinking that this might be easier in VBA. Suggestions welcomed! You're the pro, I respect your opinion.

 

[theDBguy]

Hi. If you're getting a zero for the PlanID, I suspect your TempVars may be empty. To add 7 days to your MealDate, you can use the DateAdd() function. For example:

DateAdd("d",7,[MealDate])

 

[Lkwdmntr]

Hi. If you're getting a zero for the PlanID, I suspect your TempVars may be empty. To add 7 days to your MealDate, you can use the DateAdd() function. For example:

DateAdd("d",7,[MealDate])

I'm getting the zero because I'm not appending that field. If I did it would be the same Plan ID from the previous week and I need it to be the new plan Id. This is where the problem is.

 

[theDBguy]

I'm getting the zero because I'm not appending that field. If I did it would be the same Plan ID from the previous week and I need it to be the new plan Id. This is where the problem is.

Okay, are we talking about the ActionsPerWeeklyPlanIDFK field? If so, is it an Autonumber field? If so, then just take it out of your INSERT query.

 

[Lkwdmntr]

I actually meant the WeeklyPlanID. I got it to work, but now I have to update all the ActionPerWeeklyPlanIDFK values to the ones for the new Plan week. I should be able to do that with a few update statements unless you have a better suggestion. Here's the code I ended up with for the append query in case you're interested.

INSERT INTO tblMealComponentsPerWeeklyPlan ( WeeklyPlanIDFK, ActionsPerWeeklyPlanIDFK, MealDate, WeekDay, DOW, ComponentType, StartTime, FoodItem, Qty, Measurement, Sequence, MealCatagory )
SELECT [TempVars]![TempPlanID] AS WeeklyPlanIDFK, tblMealComponentsPerWeeklyPlan.ActionsPerWeeklyPlanIDFK, DateAdd("d",7,[MealDate]) AS [Date], tblMealComponentsPerWeeklyPlan.Weekday, tblMealComponentsPerWeeklyPlan.DOW, tblMealComponentsPerWeeklyPlan.ComponentType, tblMealComponentsPerWeeklyPlan.StartTime, tblMealComponentsPerWeeklyPlan.FoodItem, tblMealComponentsPerWeeklyPlan.Qty, tblMealComponentsPerWeeklyPlan.Measurement, tblMealComponentsPerWeeklyPlan.Sequence, tblMealComponentsPerWeeklyPlan.MealCatagory
FROM tblMealComponentsPerWeeklyPlan
WHERE (((tblMealComponentsPerWeeklyPlan.WeeklyPlanIDFK)=[TempVars]![TempPrevPlanID

]));

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Here's an idea, in case it helps. Leave the ActionPerWeeklyPlanIDFK empty or at zero (0) during the INSERT. You can then use an UPDATE query to change all of them at once after you determine the new ID to assign to them. Cheers!

 

[Lkwdmntr]

Hi. Glad to hear you got it sorted out. Here's an idea, in case it helps. Leave the ActionPerWeeklyPlanIDFK empty or at zero (0) during the INSERT. You can then use an UPDATE query to change all of them at once after you determine the new ID to assign to them. Cheers!

I would but I have to go by the meal category and change them with that criteria. That would be nice if they were all the same, but they are different for each meal. Thanks again!

 

[Pat Hartman]

The append query requires TWO arguments. The select clause needs the PK of the existing record for the From Clause, The Insert clause requires the PK of the parent record you want to copy to to use as the FK.