Default value from separate table

[dgtampa]

Hello all,

I have a form named "frmLevel_Review," which contains controls named "txtBeneficiary_Name," "txtReview_Date," and "txtCurrent_Level," whose row source is a table named "Level_Review."



What I need on the form is the default value for the control "txtReview_Date" to be the value from a field named "Admit_Date" on a separate (but related) table called "Beneficiaries." I would like this to happen after the user selects a name from the control "txtBeneficiary_Name."

The idea is that the form will 1) automatically populate the "txtReview_Date" with a Beneficiarie's admit date for new records while 2)allowing the user to update existing records with a new review date (note: I do not want the updates to cascade back to the "Admit_Date" in the table "Beneficiaries"; I only want the updates to apply to records in the source table for "frmLevel_Review."

I have attached a relationship report if anyone needs to review it...

This is my first post here, so I apologize in advance if I am not posting correctly

[June7]

This will require code. I use only VBA.

Use the AfterUpdate event of txtBeneficiary to get the Review_Date and set the field. One way is:

If IsNull(Me!Review_Date) Then
Me!Review_Date = DLookup("Admit_Date", "Beneficiaries", "ID=" & Me.txtBeneficiary)
End If

Suggest the txtBeneficiary control be a combobox with a RowSource that queries the Beneficiaries table.

[dgtampa]

Thank you sooo much June7! That did the trick!