Module in Subforms

[gg80]

I am using W7 and A2007. I have 8 look alike subforms as part of several parent forms. When certain fields are updated on a subform, I would like some code to be activated. One of the simple procedures is:
If (IsNull(Me.Sort) Or Me.Sort = 0) Then
Me.Sort = Me.MaxSort
End If

It works fine in the subform. When I try it In a module:

With Screen.ActiveForm
If (IsNull(.Sort) Or .Sort = 0) Then
.Sort = .MaxSort
End If
End With



I get error 2465 “Application defined or object-defined error.”
The compiler doesn’t show any problem.
Can someone tell me what I am doing wrong? Couldn’t find answer with Google or here. Any help much appreciated.

[June7]

You want a generic procedure that can be called from a bunch of forms? My first question is why 8 identical forms? Next question is why saving aggregate data?

I've never tried the ActiveForm method. I expect the issue is that the subform is not the 'active' form, the main form is the active form. Subforms are actually a Subform/Subreport container control on the main form. These container controls hold an object (table, query, form, report).

I do have procedure that can be called from several forms and the calling form is important to the procedure so I pass the form name to the procedure as an argument of the procedure.

[gg80]

Thanks for looking at this and the quick reply. Its a long story, but I need the sub forms. It appears that you don't use the reference "activeform". I have used it sucessfully on parent forms, never tried it on subform.

I'm afraid that I don't understand what "saving aggregate data" is about. The code I showed is simply to handle situations in which the user makes an entry in a record that has a null or 0 in the "Sort" field.

[June7]

What is MaxSort value? Use of Max in name made me think this value was the result of Max() which is an aggregate function used in GROUP BY query or result of DMax() function used in a textbox ControlSource.

Why isn't user just entering data into a textbox bound to Sort field?

The only way I can think for the module code to know which subform should be acted on is to pass the subform reference as argument. What is the code that calls the module procedure?

Do you want to explain why you have 8 identical forms instead of 1?

[gg80]

Half the subforms are duplicates on the same main form. I have a left and a right conaining half the data in each so that the operator doesn't have to scroll to get at items on the long list. The 4 major subforms are lists of options related to different types of sailboat sails; Main, Jib etc. Your right, that max refers to an aggregate function. I could just as easily given it a hard value, like 100, to ensure that it had a value, but just seemed neater to make it equal to the max sort number. The operator should change the number to fit another sort category, but, if he doesn't, it will be listed at the bottom in later reports and the need to change becomes obvious.

i found a fix, mostly because you told me that "ActiveForm" refers to the parent, not the subform. Here is what I did: (This is onUpdate from a field in the subform)

Dim Msort As Single
DoCmd.GoToControl "MaxSort"
Msort = Screen.ActiveControl
DoCmd.GoToControl "sort"
If (IsNull(Screen.ActiveControl) Or Screen.ActiveControl = 0) Then
Screen.ActiveControl = Msort
End If

Thanks much for working on this.