copy the date from sub form to main form?

  • Thread starter Thread starter Krae
  • Start date Start date
K

Krae

Hello,

I have two forms in one form. 1 - main form 2 - subform
Both are 1 to many relationship

If you are in first record of the form. You type the
date in the main form - note_date field. For example,
you put 11/1/03.

I start to add info in the subform inside the main form.
The subform has the note_date field too. You type
11/2/03 in the subform. Once you leave the note_date
field after you put date 11/2/03. I expect the main
form - note_date is updated automatically.

If you go to second record in subform and you put 11/4/03
in the note_date of the subform then I expect the
note_date in the main form is updated automatically too.

How can I write the code in note_date field in the
subform to update the note_date in the main form?
Maybe use "afterupdate" sub?

Thanks,
Krae
 
The main form note_date should *always* the the date of the most recent note
for the record? If so, you can save yourself lots of trouble by removing the
date from the main form's table, and asking Access to get the value for you.

The Control Source for the text box on the main form would be something like
this:
=DMax("note_date", "YourSubformTable", "MyForeignID = " & Nz([MyID], 0))

That's assuming:
- the subform's table is called "YourSubformTable";
- the subform control's LinkChildFields is "MyForeignID";
- the subform control's LinkMasterFields is "MyID";
- "MyID" is a numeric field. If it's Text, you need:
=DMax("note_date", "YourSubformTable", "MyForeignID = """ & [MyID] & """")

If you want to go to the trouble of writing the value back to the main form,
you would need to use the AfterUpdate event procedure of the subform to
lookup the most recent note date, and write it back to the parent form:

Dim varResult As Variant
varResult = DMax("note_date", "YourSubformTable", _
MyForeignID = " & Nz([MyForeignId, 0))
If Not IsNull(varResult) then
With Me.Parent
If .note_date >= varResult Then
'do nothing: it already has the date.
Else
.note_date = varResult
.Dirty = False
End With
End If
 
Back
Top