vbMsgBoxResults-nested if,then,else

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with a form in Access 2000. My (bound) form has 12 text boxes for
Jan-Dec and a text box for the year total. These text boxes are bound to an
underlying query. I have placed an unbound text box on the form. In this text
box, I want the user to be able to enter an amount. This amount will be
divided by 12 and the result will populate the Jan - Dec text boxes. The
amount entered in the unbound box will populate the year total text box.

I want the user to be able to enter amounts in the Jan-Dec fields OR use the
text box that will split it equally for them.

I have written code in the unbound text box's BeforeUpdate event that checks
to see if the year's total box is 0, if so, take the value entered and divide
by 12, populate the Jan-Dec text boxes.

Elseif the value is greater than 0, then I have a msgbox that tells them the
existing data will be overwritten, do they want to continue? Ok or Cancel.
This is where I have a nested if statement. If the vbmsgboxresult is cancel
then I use the cancel = true line. If the result is Ok, then divide the
number by 12 and put it in Jan-Dec.

It doesn't work right. I've tried putting the If statment in the "Enter"
event, to evaluate the year total box to see if there is an amount greater
than 0 in there. This works good, but I still can not get the nested msg box
results to work.

I also have a subform based on a totals query, linked to the main form, that
shows the new year total. I would like for it to update as well. If I use the
Requery code, it runs the code again, I think because the focus is still on
the unbound text box at this point.

(and I would like to reset the amount in the unbound text box to zero when
it goes to the next record... do that in the form events?)

Can you suggest a better way? I sure will appreciate it. Thanks very much.
Donna
 
Hi Donna,

If I understand you right, you want to update the textboxes if either
the year total is empty
or
the year total is not empty and the user clicks OK to the messagebox.

Often it helps to turn things upside down. The above two conditions are
equivalent to this:
cancel the process if the year total contains a non-zero value
and
the user clicks Cancel
which you can do something like this (txtYearTotal is the name of the
year total textbox):

Private Sub XXX_BeforeUpdate(Cancel As Integer)
...
If Me.txtYearTotal.Value <> 0 Then
If MsgBox(blah blah) = vbCancel Then
Cancel = True
Exit Sub
End If
End If
'update the textboxes
...
End Sub

However, you should be aware that your table structure, with one field
for each month and a 13th for the year's total, may give you problems
further down the line. One problem is data integrity: it's very hard to
ensure that the amount in the YearTotal field is always the sum of the
12 month fields. Many kinds of queries will also be difficult. The
standard relational database approach to this situation is to use a tall
narrow table, with one record per month and fields for year, month and
amount. This makes your current task a bit more complicated but is more
versatile and more reliable in general.
 
Thank you John. I have all 3 scenarios working now. I agree with your
thoughts on having the Total in the main table. I am working with someone
else, and they want the total in there. Hopefully we will be able to cross
check it for errors without wasting a lot of time. Thanks so much for your
help!
Donna
 
Back
Top