Problems with Sub Form

[Bill4tektips]

I have a form with a field called NCNumber and in the Properties I put in the Default Value "=DMax("NCNumber","tblNonConformances")+1" because I want the number to increment. This works well in the form but when I make it a sub form it does not work. Any ideas?

 

[kjv1611]

When you say "it does not work," are you getting any error messages? Anything showing in the field?

 

[BSman]

How is the subform related to the main form? Check the properties of the fields in the table to be sure that you are not running into a problem with required fields.

You might need (in the subform) to put in some VBA code to set the control to the dMax value you mention for the subform's property of OnCurrent, so that if the control/field is blank you set it to the incremented value.

Bob

 

[Bill4tektips]

kjv1611, No error messages, it just displays the highest number in the table +1 and not by individual Audit Reference as it does on the form on its own.

 

[Remou]

Is this subform a continuous subform? If so, the above cannot work.

 

[Bill4tektips]

BSman, In the subform properties OnCurrent I have the following

Private Sub Form_Current()
'If Me.NewRecord Then'
' Note that <FieldName> is the name of your text field on your form'
'Me!<NCNumber> = DLookup ("[MaxValue]", "qryMaxVal") + 1'
'End If '
End Sub

When I look at qryMaxValue this is what I see

MaxValue: DMax("[NCNumber]","tblNonConformances","[AuditReference]=2 ")

As I am a novice at this game I have no idea if this is right or wrong. Any help you can give will be appreciated.

 

[BSman]

I assume that you don't actually have all of the lines in your function remarked out.

I'd suggest changing the code to something like this:

Private Sub Form_Current()
If Me.NewRecord = true then

me.NCNumber = DMax("[NCNumber]","[tblNonConformances]","[AuditReference]=2") + 1

End if
End Sub

If you create your query in the query builder, instead of putting the DMax expression in the query you should just return MaxValue by right clicking the sort column and selecting Totals which will make it a group by query. Instead of grouping by NCNumber (the only column you should return), select Max and you will get the highest value of NCNumber. You can include the criteria AuditReference = 2 by selecting Where in the GroupBy cell of the query builder grid (just like you selected Max instead of GroupBy for NCNumber), and uncheck the box so only NCNumber will be returned.

Bob