Would like to require meeting criteria before moving to next record

[PamelaJoy]

Our recipes are entered in terms of what percent each ingredient contributes to the finished product. This percentage is part of the data entry of individual ingredients which takes place on the subform.

The total percent is being calculated on the footer, and is visible on the main form, so the user can readily see when the ingredients equal 100%. However, they can choose to ignore anything not equal to 100% and go right on to start building the next recipe.

I'd like to beep, flash a message, and prevent them from saving the current recipe and/or moving to the next record when the total is not equal to 100%.

Unfortunately, I know zero vba code, and this is my first db attempt so I'm not very experienced in Access either. Thank you in advance for any help and advice!

 

[RuralGuy]

I'm not sure of your structure but the place to do this is in the BeforeUpdate event of your form. Simply make Cancel = True if your control is < 1.

 

[PamelaJoy]

Thanks for your response, Rural Guy. I've read more about the Before Update events specifically and know this is what I need. Would you help me with the code, or lead me to a good resource? In COBOL it would be something like:
If TotalIngrPercent not equal to 100
then display 'Total must equal 100%'
Else
save record go to next

Thanks!

 

[RuralGuy]

COBOL!!!
The 2nd computer language I learned.
In VBA your code will look something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If Me.TotalIngrPercent <> 1 Then
      MsgBox "Total must equal 100%"
      '-- This will cancel the save and hold the focus on the current record
      Cancel = True
   Else
      '-- Do nothing and the record will save
   End If

End Sub

...using your control name of course.

 

[PamelaJoy]

Thank you, thank you! I will try it out tomorrow and Thursday.
Not SO much different than COBOL, eh?
I really do appreciate the help.

 

[RuralGuy]

Post back here is you still need assistance.
Almost *exactly* like COBOL.

 

[PamelaJoy]

It's not working so I'm not at all sure I'm doing this correctly. I have a control on the subform footer which is a Sum function (adding the percentages as ingredients are added) called TotalPercent.
On the main form I have a visible control TotalIngrPercent with the Caption 'Has the 100% rule been met?' to at least have something in place to remind data entry not to continue to the next record until each recipe has all 100% of it ingredients.
I tried placing the code on the BeforeUpdate event on the subform referencing the subform footer control; I tried placing the code on the BeforeUpdate event on the form referencing the subform footer control; I even tried placing the control on the form's BeforeUpdate event and changing the control name to the one on the form. But each time when I go to the last record with ingredients sitting at 80%, it happily lets me march on to the next record.
What am I missing here?
A thousand thank you's.

 

[RuralGuy]

What displays in the TotalIngrPercent control on the MainForm and what is the ControlSource of thet control? Post the code you have in the MainForm BeforeUpdate event starting with Private Sub and ending with End Sub.

 

[PamelaJoy]

Main form displays as: 100.00%
Control Source Name: TotalIngrPercent
Control Source: =frmRecipeDetails.Form!TotalPercent
Code in Before Update Event on Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TotalPercent <> 1 Then
MsgBox "Total must equal 100%"
'-- This will cancel the save and hold the focus on the current record
Cancel = True
Else
'-- Do nothing and the record will save
End If
End Sub

Subform Control name: TotalPercent
Control Source: =Sum([RawMaterialPercent])
No event on the subform.

 

[PamelaJoy]

It displays: 80.00% on the record it lets me pass right on through and go on to the next record . . .

 

[boblarson]

Main form displays as: 100.00%
Control Source Name: TotalIngrPercent
Control Source: =frmRecipeDetails.Form!TotalPercent
Code in Before Update Event on Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TotalPercent <> 1 Then
MsgBox "Total must equal 100%"
'-- This will cancel the save and hold the focus on the current record
Cancel = True
Else
'-- Do nothing and the record will save
End If
End Sub

Subform Control name: TotalPercent
Control Source: =Sum([RawMaterialPercent])
No event on the subform.

I think you are referring to the wrong control on the main form's code:

Shouldn't this:
If Me.TotalPercent <> 1 Then

Be either this:

If Me.TotalIngrPercent <> 1 Then

or else

If Me.frmRecipeDetails.Form!TotalPercent <> 1

 

[PamelaJoy]

I've tried them both, Bob Larson, and neither one of the code versions works. I even tried saving and closing the db, thinking maybe that would work, but it doesn't. I don't get any error messages, and it isn't in a loop (both of which I've had occur before) so maybe that's progress.
I've tried so many forms and subforms and combinations to get where I am now, which appears to be working just fine except for this. I am obviously trying to reference a control that either isn't what or where I think it is! I will try again another day, another way, but my brain is turning to mush tonight.
Thank you for your help though, both of you.

 

[RuralGuy]

Maybe tomorrow you could post your zipped up db so we could look at it. We seem to be going around in circles here. You may need to use one of the free file hosting sites if your post count is not high enough.

 

[RuralGuy]

You could also try this as a diagnostic:

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If Me.TotalIngrPercent <> 1 Then
      MsgBox "Total must equal 100%"
      '-- This will cancel the save and hold the focus on the current record
      Cancel = True
   Else
      '-- Do nothing and the record will save
      [COLOR="Red"][B]MsgBox "We think that [" & Me.TotalIngrPercent & "] is 100.00%"
[/B][/COLOR]
   End If

End Sub

 

[boblarson]

You may need to use one of the free file hosting sites if your post count is not high enough.

Actually, that isn't true. Users can post files here regardless of post count. They aren't supposed to post URL's with under 10 unless it has to do with the problem at hand.

 

[wiklendt]

i think the issue could be the fact that there's a subform involved. i don't see any reference to any subform in the code, yet it was described that the data entry is in a subform.

where is the total count - on the form or the subform? and the before update event - shouldn't that be in the subform's event? to prevent the subform from moving one and being saved?

 

[wiklendt]

sorry bob, i jsut realised you suggested a subform reference... however, we can't be sure it was implemented correctly or used correctly with correct names of controls etc...

pamela, could you explain in detail which form (main or sub) are each of the entities you describe (i.e., sum, events etc...)

 

[RuralGuy]

Actually, that isn't true. Users can post files here regardless of post count. They aren't supposed to post URL's with under 10 unless it has to do with the problem at hand.

Thanks for the info Bob. BTW, is that a new 6th green box I see? They just keep on piling up don't they?

 

[boblarson]

Thanks for the info Bob. BTW, is that a new 6th green box I see? They just keep on piling up don't they?

Yes, it is a 6th green blob (finally - I've been at 5 since August).

 

[PamelaJoy]

I stripped away the confidential info from the tables but hope I've left enough so that you can see what I am doing wrong! I only left the tables, main form and the subform in question . . . and so appreciate your taking the time to help me.

Here is db2.zip, warts and all!