Consecutive dates with table info

[klallen810]

Hello,

I am working on a nutrition tracking database and would like to have a form that updates my “Goal Weight” Table, so I don’t have to type in every day, and every number. Basically, I have a form and a subform. The subform is a datasheet from the GoalWeight Table. It is currently empty. At the bottom of the form I have a text box for Date and Weight. I would like to be able to enter today’s date and today’s weight and then have the table populate. Basically, I would like to lose .2 lbs/day until I reach 125 lbs. So let’s say that today is 12/4/2012 and I weight 135 lbs. I would like to be able to put 12/4/2012 in to the Date text box and 135 into the Weight text box, then click “Update” and have the table look something like this:

ID Date Weight
1 12/4/2012 135
2 12/5/2012 134.8
3 12/6/2012. 134.6

Etc. until the Weight reaches 125. Also, if the weight at a particular date changes, I would like to be able to update the table with the new information. I thought of using an append query with the DateAdd function, and then writing some VBA code that will run this query, adding one day, and subtracting .2 lbs, until the goal weight is reached. I have a good working knowledge of Access, but am fairly new to VBA. Please let me know if you have any ideas! Thanks!!

 

[JHB]

I have made an example for you in the attach database, open the form named "Formular2"
I have insert a picture in form named "HowTo", to show what to do if weight change.
The code running behind the button "UpdateTable" is showed here.

Be aware of field name with reserved word like Date, Day, …. and so on.

Private Sub UpdateTable_Click()
Dim dbs As Database, rst As Recordset, Weight, DateDay As Date

Set dbs = CurrentDb

Me.Refresh
dbs.Execute ("Delete * FROM GoalWeight WHERE DateDay>#" & Format(Me.DateDay, "mm/dd/yyyy") & "#")
Set rst = dbs.OpenRecordset("GoalWeight")
rst.MoveLast
DateDay = Me.DateDay
Weight = rst![Weight]
Do
DateDay = DateDay + 1
Weight = Weight - 0.2
rst.AddNew
rst![DateDay] = DateDay
rst![Weight] = Weight
rst.Update
Loop Until Weight <= 125
Me.Requery
End Sub