the value of the last record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a form and a sub form.
One of the fields in the sub forms is date. I have some records in the sub
form for each record in the main form (one to many relation)
I would like to have the date of the last record of the sub form.

How can I get this value?

Thanks a lot,
Liat
 
Use DMax() to lookup the subform's table and get the maximum value of the
date field.

The ControlSource of the text box on the main form will be:
=DMax("NameOfDateField", "NameOfSubform'sTable",
"[NameOfSubform'sForeignKey] = " & Nz([NameOfMainForm'sPrimaryKey], 0)

After changes in the subform, you may need to ReCalc the main form (or move
record) before it displays the new value.
 
Thank you very much for your answer.

What is the NameOfSubform'sForeignKey?

I just used the Max(), what is the difference?

How can I ReCalc after data was entered?

Thanks a lot,
Liat

Allen Browne said:
Use DMax() to lookup the subform's table and get the maximum value of the
date field.

The ControlSource of the text box on the main form will be:
=DMax("NameOfDateField", "NameOfSubform'sTable",
"[NameOfSubform'sForeignKey] = " & Nz([NameOfMainForm'sPrimaryKey], 0)

After changes in the subform, you may need to ReCalc the main form (or move
record) before it displays the new value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Liat said:
I have a form and a sub form.
One of the fields in the sub forms is date. I have some records in the sub
form for each record in the main form (one to many relation)
I would like to have the date of the last record of the sub form.

How can I get this value?

Thanks a lot,
Liat
Click to expand...
Click to expand...
 
Normally a subform is bound to a table that is related to the main form's
table. The field in the subform's table that relates to the main form's
table is called the foreign key. Substitute the name of this field in the
expression.

Max() will not work in this context.

If necessary, use the AfterUpdate event of the subform to recalc the main
form. The code for the event procedure would be:
Me.Parent.ReCalc

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Liat said:
Thank you very much for your answer.

What is the NameOfSubform'sForeignKey?

I just used the Max(), what is the difference?

How can I ReCalc after data was entered?

Thanks a lot,
Liat

Allen Browne said:
Use DMax() to lookup the subform's table and get the maximum value of the
date field.

The ControlSource of the text box on the main form will be:
=DMax("NameOfDateField", "NameOfSubform'sTable",
"[NameOfSubform'sForeignKey] = " & Nz([NameOfMainForm'sPrimaryKey],
0)

After changes in the subform, you may need to ReCalc the main form (or
move
record) before it displays the new value.

Liat said:
I have a form and a sub form.
One of the fields in the sub forms is date. I have some records in the
sub
form for each record in the main form (one to many relation)
I would like to have the date of the last record of the sub form.

How can I get this value?

Thanks a lot,
Liat
Click to expand...
Click to expand...
Click to expand...
 
Back
Top