DMax with a Subform Reference

[PJFry]

I have a form(fEmployee) with a subform(sfOrder). The subform has a
continuous subform(sfWorked). Each employee has multiple orders they work.
In those multiple orders they have multiple timecard entries. We track the
weekly time entries. In sfWorked, I want the default value to be the one
week after the latest date for that order number. Order numbers are unique.

I am getting wrapped around the axle when I go to reference sfWorked and
sfOrder in the condition of the DMax. In plain English, it would read like
this:
Give me the max date from tWorked where the order number on sfWorked = the
order number on sfOrder.

As expression, I am guessing it would look like this:
DMax("dtmTimeCard","tWorked","Forms!sfOrder!sfWE!lngOrderNumber =
Forms!sfOrder!lngOrderNumber")+7

How do I properly reference the subforms to make this work?

Thanks!
PJ

 

[NKTower]

The various domain functions can only look at data in tables. They may
compare the data with expressions that you pass in, which may be literal
values or expressions that reference tables. But I don't think that they can
"reach into a form" at run time to pluck out a value. So it is probably
complaining about your selection clause. If you want the max date from
tWorked where the order number is as on the form, I think it would be like
this:

Public Function Date_7_Beyond_Max(var_OrderNumber As Variant) As Variant
Dim var_MaxDate As Date As Variant

If IsNull(var_OrderNumber) Then
Dat_7_Beyond_Max = Null
Exit Function
End If

var_MaxDate = DMax("dtmTimeCard", _
"tWorked", _
"[tWorked].[OrderNumber] = " & _
Str(var_OrderNumber ) )
If IsNull(var_MaxDate) Then
Date__Beyond_Max = Null
Exit Function
End If
Date_7_Beyond_Max = DateAdd("d",7,var_MaxDate)
End Function

In your form, BeforeInsert event

var_CalcDate As Variant
' assuming that target_control is bound to the table column, etc.....

var_CalcDate = Date_7_Beyond_Max(Forms!sfOrder!sfWE!lngOrderNumber)
If IsNull(var_CalcDate) Then
.... you've got a problem
Else
Me.target_control = var_CalcDate
... etc.
End If

Alternate place to call it -
OnCurrent Event - test to see if it is the "NewRecord" and if so stuff
the value in.
I think it would be like this...

Private Sub Form_OnCurrent()
If Me.NewRecord Then
' call the function as above and put value in control
End If
End Sub

 

[NKTower]

oops -
Dim var_MaxDate As Date As Variant
should be
Dim var_MaxDate As Variant

The various domain functions can only look at data in tables. They may
compare the data with expressions that you pass in, which may be literal
values or expressions that reference tables. But I don't think that they can
"reach into a form" at run time to pluck out a value. So it is probably
complaining about your selection clause. If you want the max date from
tWorked where the order number is as on the form, I think it would be like
this:

Public Function Date_7_Beyond_Max(var_OrderNumber As Variant) As Variant
Dim var_MaxDate As Date As Variant

If IsNull(var_OrderNumber) Then
Dat_7_Beyond_Max = Null
Exit Function
End If

var_MaxDate = DMax("dtmTimeCard", _
"tWorked", _
"[tWorked].[OrderNumber] = " & _
Str(var_OrderNumber ) )
If IsNull(var_MaxDate) Then
Date__Beyond_Max = Null
Exit Function
End If
Date_7_Beyond_Max = DateAdd("d",7,var_MaxDate)
End Function

In your form, BeforeInsert event

var_CalcDate As Variant
' assuming that target_control is bound to the table column, etc.....

var_CalcDate = Date_7_Beyond_Max(Forms!sfOrder!sfWE!lngOrderNumber)
If IsNull(var_CalcDate) Then
.... you've got a problem
Else
Me.target_control = var_CalcDate
... etc.
End If

Alternate place to call it -
OnCurrent Event - test to see if it is the "NewRecord" and if so stuff
the value in.
I think it would be like this...

Private Sub Form_OnCurrent()
If Me.NewRecord Then
' call the function as above and put value in control
End If
End Sub

I have a form(fEmployee) with a subform(sfOrder). The subform has a
continuous subform(sfWorked). Each employee has multiple orders they work.
In those multiple orders they have multiple timecard entries. We track the
weekly time entries. In sfWorked, I want the default value to be the one
week after the latest date for that order number. Order numbers are unique.

I am getting wrapped around the axle when I go to reference sfWorked and
sfOrder in the condition of the DMax. In plain English, it would read like
this:
Give me the max date from tWorked where the order number on sfWorked = the
order number on sfOrder.

As expression, I am guessing it would look like this:
DMax("dtmTimeCard","tWorked","Forms!sfOrder!sfWE!lngOrderNumber =
Forms!sfOrder!lngOrderNumber")+7

How do I properly reference the subforms to make this work?

Thanks!
PJ

 

[Steve Schapel]

PJ,

Give me the max date from tWorked where the order number on sfWorked = the
order number on sfOrder.

This doesn't really work. Neither within the DMax function, nor in
plain English.

If you are looking for a value from within the tWorked table, then the
criteria has to relate to the tWorked table. The problem is not with
how you are referencing the subforms, it is the fact that you are trying
to compare the values on two subforms without relating that to your table.

Does the tWorked table have a OrderNumber field? If so, you are more
likely to be lookig for something like:
DMax("[dtmTimeCard]","tWorked","[OrderNumber]=" &
Forms!fEmployee!sfOrder.Form!lngOrderNumber)