How do I populate a textbox with information related to a record I have selected?

[lccrews]

I have a form with (4) subforms on it displaying ingredients. There is one field [Item] that all (4) subforms share in common. What do I need to do in order to make this happen:



1. click on record in subform (the [Item] field)
2. look up that Item on tbl_BulkItems and display [Descr] in Textbox1

So when a user clicks [Item] on any record in the datasheet, it's description is populated in a textbox. Any help is appreciated!

Update:

I figured it out. Using the below code, you can set the value of a textbox on the main form to the value of the query in VBA. Here it is:

Private Sub Item_Click()
Dim rst As DAO.Recordset
Dim sSQL As String


sSQL = "SELECT tbl_BulkItems.Descr FROM tbl_BulkItems WHERE (((tbl_BulkItems.Item)='" & Me.Item & "'));"


Set rst = CurrentDb.OpenRecordset(sSQL)


Description = rst![Descr]


Forms!frm_WhereUsed!Item = Description

rst.Close
Set rst = Nothing


End Sub

[kd2017]

This is assuming your [Item] field is a unique identifier in tbl_BulkItems.

Open your subform and right click your item field, then click "Build Event..." and get into the VBA editor and add something like this to the OnClick event (may need to adapt to your specific needs):

Me.Parent!Textbox1.Value = DLookUp("[Descr]", "tbl_BulkItems", "[Item]=" & Me.Item )

You'll need to add this code to the [Item] field control for each distinct subform.