Help saving a value after update using VBA (1 Viewer)

[bessej43]

I am using VBA to do a calulation and would like the value to be save in the form as will as the table. The Project Days value is not updating after the stop date is entered. It is not putting the value shown but the none rounding amount is kept. I also have one more that I will post separate. Here is the VBA I wrote:

Private Sub Project_Stop_Date_AfterUpdate()
'Dim Fraction As Single
Dim NumberOfDays As Single
'Dim NumberOfMonths As Single
Dim StartDay As Single
Dim StopDay As Single
StartDay = Day([Project Start Date])
StopDay = Day([Project Stop Date])

'NumberOfDays = DateDiff("d", [Project Start Date], [Project Stop Date]) / 12
NumberOfDays = (DateDiff("m", [Project Start Date], [Project Stop Date]) - 1) * 2.5
'Fraction = NumberOfDays - Int(NumberOfDays)

Select Case StartDay
Case Is <= 6
NumberOfDays = NumberOfDays + 2.5
Case Is <= 12
NumberOfDays = NumberOfDays + 2
Case Is <= 18
NumberOfDays = NumberOfDays + 1.5
Case Is <= 24
NumberOfDays = NumberOfDays + 1
Case Is <= 31
NumberOfDays = NumberOfDays + 0.5
End Select

Select Case StopDay
Case Is <= 6
NumberOfDays = NumberOfDays + 0.5
Case Is <= 12
NumberOfDays = NumberOfDays + 1
Case Is <= 18
NumberOfDays = NumberOfDays + 1.5
Case Is <= 24
NumberOfDays = NumberOfDays + 2
Case Is <= 31
NumberOfDays = NumberOfDays + 2.5
End Select

Me![Projected Days].Value = NumberOfDays
Me.Recordset.Edit
Me.Recordset![Projected Days].Value = NumberOfDays
Me.Recordset.Update
Command28_Click

'Form![Entitlements Query1].Text22.Value = NumberOfDays


End Sub

 

[Travis]

If I understand you correctly:

NumberOfDays does get the value that you want, but you cannot update the data in the recordset.

If this is the case just a few points.

1. Unless the form is created with a disconnected Recordset it will be directly connected to the Data Source.

2. If the Query that the form is based upon is not updatable then you will not be able to make changes.

3. Drop the .Value and .Text properties when in Access (They don't work the same way as they do in VB, Don't ask why that is just the way it seems to be )

 

[bessej43]

Travis,

Thank you for all your help.