Subform calculations

foureyes · #1 07-22-2009, 12:02 AM

Hi

I am new to VBA and have spent all day trying to get this sorted so appreciate any help!!

I have a sub form that lists the percentage of the sale that belongs to each salesperson. So in may cases one sales person will make 100% of the sale, but sometimes its split across 2 for example, 50% each. The field that keeps this data in called "Split". I also have a total in my footer that sums all "Split". When my footer total doesn't add upto 100% I want a msg box to appear advising the user.

Problem is that I can seem to get access to run the footer calc quick enough. So, if the user enters in error 150% and then tabs to a new record it’s allowed because it hasn't calculated the total by the time it creates a new record. If however, the user tries for a third record it will bring up the msg box as the 1st record is still over 100%.

I have tried various things, hoping that someone out there has come across this before and has a solution. Thanks in advance!!

RuralGuy · #2 07-23-2009, 09:41 AM

The "sum" in the footer can not update until the record you are working on is saved. That happens when you move to a new record. That should give you a puzzle to think about for a bit.

tlittell · #3 07-24-2009, 12:45 PM

The data validation you describe doesn't sound complicated and should run quickly. In what event (form1_beforeupdate, text1_change, etc) did you code your validation check? Could make a difference. Include your code if you can.

foureyes · #4 07-26-2009, 08:34 PM

Thanks for your help!!

I have done the addition now in my code and that seems to calculate it instantly and then it displays an error message.

My next problem is after the error message has been OK'd I want the cursor to move to the previous field but when I say Me.recordset.moveprevious it doesn't seem to work.. don't suppose you know why this maybe? I have pasted my code below:-

Code:


			
Private Sub Placement_Split_AfterUpdate()
Dim dSplitCntr As Double
Me.testbox1.Requery     'testbox is just so that i can see in the form whether the calc is working

Forms!Fm_SubForm.Recordset.MoveFirst
    
Do While Not Forms!Fm_SubForm.Recordset.EOF
    For Each Item In Forms!Fm_SubForm.Controls
       If Item.Name = "Placement Split" Then
           dSplitCntr = dSplitCntr + Item.Value
       End If
       
    Next
    Forms!Fm_SubForm.Recordset.MoveNext
Loop
Me.testbox1.Requery

Forms!Fm_SubForm!testbox1 = dSplitCntr
If dSplitCntr > 1 Then
Response = MsgBox("Error", vbOKOnly)
        Me.Recordset.MovePrevious

thanks!

tlittell · #5 07-27-2009, 06:20 AM

Do you get an error when it tries to .moveprevious or just nothing happens? Remember there has to be a valid record for it to move to, so if you're testing your error handling on the 1st record then there's nothing to moveprevious to.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Subform and calculations issues	rustyCrVx	Access	1	05-02-2009 01:37 PM
Time calculations	jimandann	Programming	2	02-17-2009 09:27 PM
Calculations in linked forms	Dena Grabinar	Forms	0	08-22-2008 03:34 AM
retrieving data from a subform into another subform	kaydash	Forms	1	12-10-2005 01:52 PM
Subform in a Subform and relationships	St3ph3n	Database Design	3	12-06-2005 03:34 PM