Not a lick of VBA

[jdbegg]

Hi all,

I've never written a lick of VBA, so this may be an easy problem to solve.

I have a parent/child table construct. In the Parent (Action_Item_Main_Table) I have an element named Current_Status. In the child table (Action_Item_Status_Table) I have the element Status. I know this isn't the way to design it, but I don't know how to get around what I want to do any other way.

Status in the child table has a date associated with it. The element is named Status_Date. What I want to do is to get the MAX Status_Date in the Child table and display the Status on the Main Form. My workaround has been to have a subform where I go and look up the lastest date, get the status and manually put it in to Current_Status.

Can somebody help me with this one?

Thanks,

jdbegg

 

[Rich]

=Max([YourDateField]) in an unbound textbox in the subform footer and then reference that textbox on the main form

 

[jdbegg]

Rich,

That helped in that I was able to get the max date for the selected Action Item, but not the Status associated with the Status_Date. How do I do that? I was thinking that a nested SQL statement would be the only way do this and that it would have to be in VBA. If there's a better way, I'm all for it!!!

Thanks,

jdbegg

 

[WayneRyan]

jd,

Follow the DMax with a DLookUp:

Me.MyStatus = DLookUp("[Status]", "Action_Item_Status_Table", "Status_Date = #" & Me.Maxdate " And Primary_Key = " & Me.PrimaryKey)

Wayne

 

[jdbegg]

Wayne,

Thanks for the help. I know this is an ignorant question, but where does the code go? In the Control Source of the Current_Status field on the main form?

I'm attaching a picture of my table relationships. I don't know if that will help.

jdb

 

[jdbegg]

Rich,

I maxed my date field in my subform footer with an unbound textbox and that part worked. My problem is referencing that textbox on the main form. I thought it would be as easy as creating an unbound textbox on the main form and putting this in the control source =[Forms]![Action_Item_Main_to_AI_Child_Form]![Max_Status_Date]

I'm getting a #Name? error in the textbox on the Main Form.

I changed the way I did the form/subform a little. Instead of building the forms on tables, I built them on a query. I'm not sure of this, but I seem to remember that it is a best practice to build forms against a query than by just using the tables.

I've read many posts that say to create an unbound textbox on a subform's footer and reference it on the main form, but don't know why or how this works.

Thanks for the help,

jdb

 

[Jon K]

Try placing the following line of code in the After Update Event of the unbound text box on the subform (replacing with the correct main form name and text box name):-

Private Sub Max_Status_Date_AfterUpdate()

  Forms![nameOfMainForm]![nameOfUnboundTextbox] = Me.Max_Status_Date

End Sub

Whenever the value of Max_Status_Date on the subform is changed, its value is passed to the textbox on the main form.