Open New Form - Based on Results of Calculated Field

[labprof]

Is it possible to make Access open a form based upon the results of a calculated field on another form?

I have a form for data input into an underlying table.
The field names on the form are FA1, FA2, FA3.
I have another field named FASUM which is the sum of FA1+FA2+FA3.
And then another field which is named FAAVG which is
FASUM / 3.
I want to set a criteria tht if the average of the three fields is >= a set number (lets say 10)for example, then it triggers a pop-up form that says "Do You Want to Issue An Alert?" with two action buttons... 1 would be yes which would run a macro that printed and emailed a report and the second button would just be NO and would cancel and close the pop-up form.

Could someone please coach me on how this could be accomplished? I sincerely appreciate your help and patience.

Labprof

 

[PHV]

In the VBA help have a look at the DoCmd.OpenForm method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ssatech]

How about something like:

Private Sub FAAVG_Enter()

Dim intResult As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim intResponse As Integer

If Me.FAAVG > 10 Then
strTitle = "Alert?"
intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
strMsg1 = "Do You Want to Issue An Alert?"
intResult = MsgBox(strMsg1, intMsgDialog, strTitle)

If intResult = vbNo Then
intResponse = acDataErrContinue

ElseIf intResult = vbYes Then

DoCmd.RunMacro "NameOfYourMacro"

End If
End If
End Sub

 

[labprof]

Maybe I need to explain my dilema and goal a little more clearly. Forgive me for being so simple minded.

My form is based on an underlying table that has these fields.

Field Names: 1AILC, 1BILC, 2AILC, 2BILC, 3AILC, and 3BILC
Field Names: 1ATS, 1BTS, 2ATS, 2BTS, 3ATS, 3BTS

I have two unbound text boxes that calculate the sum of the data that is entered in these fields.

Text Box 1 Name is Text395
The formula and control is:
=([1AILC]+[1BILC]+[2AILC]+[2BILC]+[3AILC]+[3BILC])

Text Box 2 Name is Text397
The formula and control is:
=([1ATS]+[1BTS]+[2ATS]+[2BTS]+[3ATS]+[3BTS])

And then I have a third unbound text box named Index
The formula for this is
=([Text397]/[Text395])

So what I am trying to achieve is this.
If the Index is =>10
I want the system to open another pop-up form called "AlertNotification".

On that form I have the action buttons to either cancel the notification or elect to send an email notification.
Actually the email notification is implemented through a macro.

So to my Access Gurus.....
How might I get the form to pop-up when the Index field
is =>10?

I hope I have better explained my goal and would sincerely appreciate anyones assistance that might help me achieve this.

I am fairly new to Access and my code writing skills are weak. Although I am beginning to understand some of what you all have been helping me with.

Again, your assistance is appreciated.

Labprof

 

[Pampers]

You could try this in the afterupdate() - or maybe the on currect even of the form


Private Sub Index_AfterUpdate()
If Me.Index >= 10 Then
DoCmd.OpenForm "PopUp"
End If
End Sub

Pampers
Just let it go...

 

[TheAceMan1]

How are ya labprof . . .

Use the [purple]Forms AfterUpdate[/purple] event with the code provided by [blue]pampers[/blue].

Calculated fields do not trigger the AfterUpdate event!

See Ya! . . . . . .

 

[labprof]

I must be doing something wrong.
I tried using the code provided by Pampers on both the field itself "Index" AfterUpdate and on the Form AfterUpdate and neither causes the AlertNotification form
to open when the Index number is >=10.

Could it be because the field Index is in Percent format?

Name................ Index
Control Source...... ([Text397]/[/Text395])
Format ............. Percent
Decimal ............ Auto

Does anyone think that because the field is formatted as percent that this might prevent the code from working?

Labprof

 

[ItIsHardToProgram]

In the VBA help have a look at the DoCmd.OpenForm method.

It seems your problem is in that direction.

 

[mkirros]

The format of a field or control should not affect the value returned by it, only the way that value is presented, so the percent format should not be what's causing your problem.

I do have one additional question. Is the form in question being used as a data entry form to update the underlying table, or is the data being pulled from the table when the form is opened?

 

[labprof]

The form that is in question is used for data entry into an underlying table. The Index field just does a calculation of a percentage of observations for a particular attribute.

All the field names identified in the earlier post are for the table fields and also are the field names on the form.

I just need the to be able to have the system automatically pop up another form called "AlertNotification" when the Index number is >= 10%

I did look into the VBA help in regards to DoCmd.OpenForm
but I could not really understand how ~ or the proper syntax to use for conditional expression or statement.

Guys.... please be patient with me. I am a little thick sometimes.

Labprof

 

[ssatech]

Know what you mean! It seems the code does not seem to work in an unbound field.

Although, it does work if the field has the focus:

Private Sub Index_Enter()

If Me.Index >= 0.1 Then
DoCmd.OpenForm "PopUp"
End If

End Sub

 

[labprof]

Thanks ssatech.....

So I'm not crazy after all.

Do you know how to make an unbound field have focus?

Labprof

 

[TheAceMan1]

labprof . . . .

AhHa!

[blue]Could it be because the field Index is in Percent format?[/blue]

Yes it could . . . and most certainly is!

[blue]The format of a field or control should not affect the value returned by it . . . [/blue]

Unless its in [blue]percent[/blue] format, which [blue]multiples the actual value by 100![/blue]

[blue]labprof[/blue] has been asking to ping Index against a value of ten. [purple]If this is the result of formatting he/she should be pinging against 0.1[/purple].

[blue]I hope this is clear! . . . as it explains why code that should work . . . doesn't![/blue]

See Ya! . . . . . .

 

[labprof]

ssatech and TheAceMan1....

Great!!! I changed the code to read

Private Sub Index_Enter()

If Me.Index >= 0.1 Then
DoCmd.OpenForm "Index"
End If

End Sub

But now my only problem is that the form opens behind the current form.

Also I have 6 fields that need to be filled in. I don't want the form to open until all 6 fields are filled in completely. Is there a way to not have the form open until the last field has data entered?

Thanks for all your help.

Labprof

 

[TheAceMan1]

labprof . . .

Set the [blue]PopUp[/blue] property of the form to [blue]Yes[/blue] . . .

See Ya! . . . . . .

 

[ssatech]

Also I have 6 fields that need to be filled in. I don't want the form to open until all 6 fields are filled in completely. Is there a way to not have the form open until the last field has data entered?"

Why not use the Before Update of your 6th field:

Private Sub 6thFieldName_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Field1) Or IsNull(Me.Field2) Or etc Then
MsgBox "Not all field have been filled out"
End If

End Sub

Play around with Before Update Event....

 

[TheAceMan1]

ssatech . . .

Look to the forms BeforeUpdate event!

Put the cursor on the event line & hit F1 . . .

See Ya! . . . . . .