Input value changing on refresh

[Guest]

Hi,

I am working on a budget system. The user can apply a budget by month
against an account or by year and apply a spread which distributes the total
by 12 i.e. 120 so 10 in each month.

There are two option buttons. One for by month and one for by year. There
are 13 text boxes underneath running from left to right. The months start at
April and end in March. The 13th box is the total.

The bug is that when you enter data and choose by year and then decide to
change a month, when you switch back to by year the total has not updated.
The data in the months are correct though. When you select by month the total
is right.

I got it to work but I could not enter anything in the total year as I put a
..controlsource on the text box to equal Jan through to dec. What do I have to
do to get the total to calculate the changes done in a month but still allow
me to overide the year value again if necessary? Here is the code.

If fraEntryType.Value = 1 Then

txtYearTotal.ControlSource =
"=[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]"
txtYearTotal.Requery
txtYearTotal.Locked = True
Jan.Locked = False
Feb.Locked = False
Mar.Locked = False
Apr.Locked = False
May.Locked = False
Jun.Locked = False
Jul.Locked = False
Aug.Locked = False
Sep.Locked = False
Oct.Locked = False
Nov.Locked = False
Dec.Locked = False

CmbSpreadMethods.Enabled = False
cmbWeightingScheme.Enabled = False
btnApplySpread.Enabled = False

ElseIf fraEntryType.Value = 2 Then

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
cn.Execute ("UPDATE thebudgettable SET YearTotal =
jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+[dec] WHERE CostCentre = '" &
cmbCostCentre.Value & "' and AcType = '5'")

txtYearTotal.Requery
txtYearTotal.ControlSource = ""
txtYearTotal.Locked = False
Jan.Locked = True
Feb.Locked = True
Mar.Locked = True
Apr.Locked = True
May.Locked = True
Jun.Locked = True
Jul.Locked = True
Aug.Locked = True
Sep.Locked = True
Oct.Locked = True
Nov.Locked = True
Dec.Locked = True

Form.Refresh
txtYearTotal.Requery
CmbSpreadMethods.Enabled = True
If CmbSpreadMethods = "CustomWeights" Then
cmbWeightingScheme.Enabled = True
Else
cmbWeightingScheme.Enabled = False
End If
btnApplySpread.Enabled = True
End If


Form.Refresh

Set cn = Nothing

You can see that when by year is selected all the month text boxes are
locked and when by month is selected the txtyeartotal box is locked.

I appreciate any help.

Nathan

 

[Guest]

Eveything is OK now.