Check for values from subform

[sponge]

Hi,

I want to check a listbox value (the listbox is in the parent form) after updating a subform field to see if the listbox value is greater than 8.5 after updating. If it is, I want an error message to pop up and I want the field that the user is on cleared.

I think I might be referencing the control from the subform incorrectly:

Forms!frmParentForm!lstTotal

Any help would be much appreciated.

 

[ChrisO]

G’day Sponge

Option Explicit
Option Compare Text


[color=green]'   Assuming a bound text box and the field can accept a Null...[/color]
Private Sub txtMyTextBox_AfterUpdate()
    
    [color=green]'   If no selection is made in the list box
    '   Me.Parent.lstTotal will return Null.
    '   Null will always be regarded as False
    '   in direct comparisons, not in the IsNull() function.[/color]
    If (Me.Parent.lstTotal > 8.5) Then  [color=green]' If not Null and greater than 8.5[/color]
        Me.txtMyTextBox = Null          [color=green]' Clear the text box and if you need to[/color]
        Me.txtDummyFocus.SetFocus       [color=green]' maintain focus on the text box then shift[/color]
        Me.txtMyTextBox.SetFocus        [color=green]' focus to another control and back again.[/color]
        MsgBox "List box is set to greater than 8.5, please try again."
    End If

End Sub

Hope that helps.

ETA. I forgot the message box.

Regards,
Chris.

 

[sponge]

Hi,

Thanks for the response..

It's still not working - I tried something to that effect earlier (except I didn't use the set focus thingy)...

The listbox is summing up a field in the continuous form using a query - could that be why this isn't working?

Would it work if I referenced the query instead? If so, how would I?

I did some debugging and the listbox value came up as Null when it should have been 11.

 

[ChrisO]

G’day Sponge

I’m afraid you are becoming a moving target.

Please let me try to explain that with a quote: -

“The listbox is summing up a field in the continuous form using a query - could that be why this isn't working?”

“summing up a field” implies a single result…the sum.
Why, therefore, use a list box to display the single result?

I simply don’t understand what’s going on and need more detailed information.

Regards,
Chris.

 

[sponge]

It's a sum based on conditions of two comboboxes.

Both of these combo boxes filter the records shown in the continuous form.

I'm summing a field of what's shown in the form, based on the combo box values.

SELECT (Sum([tblData].[Value])) AS Total
FROM tblData
HAVING ((([forms].[frmParent].[cboType])=[tblData].[Type]) AND (([forms].[frmParent].[cboName])=[tblData].[ID]));

The above is the query for the listbox's row source. I wasn't sure how to utilize conditions like those with a text box so I used a listbox

 

[ChrisO]

The fog is starting to lift from the old crystal ball…I think???

If, and that’s a very big if, the combo boxes filter the sub form correctly then why not simply place a control in the footer of the sub form that totals the selected value???

If you wish to test that value, in the sub form, then simply look at that control in the sub form.

If you wish to look at the value, in the parent form, then there are three ways to do it.
1. From the parent form, look down to the control in the sub form and get its value.
2. From the parent form, call a function that returns the value in the sub form.
3. From the sub form, write the value to a text box on the parent form and allow the parent to directly access the value.

Am I getting close???

If not, please post a small demo in A97 or A2K and we can nail this parrot to its perch.

Regards,
Chris.

 

[Pat Hartman]

As ChrisO suggested, a footer on the subform is a better place for the sum. Just add the footer and a control with the controlSource of:
=Sum(Value)
Don't forget to give the control a nice name like "txtSumValue" rather than the default name generated by the form builder.

Also, the code ChrisO suggested needs to be modified since it belongs in the BeforeUpdate event so the update can be cancelled rather than in the AfterUpdate event where it is too late since the value has been saved.

Option Explicit
Option Compare Text


'   Assuming a bound text box and the field can accept a Null...
Private Sub Form_BeforeUpdate()
    
    '   If no selection is made in the list box
    '   Me.Parent.lstTotal will return Null.
    '   Null will always be regarded as False
    '   in direct comparisons, not in the IsNull() function.
    If (Nz(Me.txtSumValue,0) + Nz(Me.txtMyTextBox,0) > 8.5) Then   
        Me.txtMyTextBox.SetFocus                
        MsgBox "List box is set to greater than 8.5, please try again."
        Me.txtMyTextBox.Undo
        Cancel = True
    End If

End Sub

I made additional modifications to the code on the assumption that each subform item is being summed and you don't want the total to exceed 8.5. That means that since the current record is not yet saved, you need to add it to the values already saved to see if the sum will exceed 8.5.