Have a combo box lookup values on a different subform

[RitaMoloney]

On my subform (frmsCollectionDetails) I need a combo box that look up values (CollectionID, CollectionType) in a different subform(frmsCollections) on a different form. (there is a one-to-many relationship between these tables).
On selecting the CollectionType, I want the corresponding CollectionID to be save to the subform’s (frmsCollectionDetails) CollectionID field and, If possible have the CollectionType value saved to the Description field.
The combo box must only show the collections types values that have been entered into frmsCollections.

The two Table Structures

tblCollections
CollectionID (pk)
CollectionType;- this is a combo box which contains; Priests Collections; Station Offerings and Planned Giving.
NoCollections
AmtPerCollection

tblAccDetails
AccDetailsID
CollectionID (fk)
CollectionDate
Status
Description
AmtPaid

Is this possible and has anyone any idea on how to go about do it.

Thanks in advance for you help.

 

[MStef]

The Combo Box can't look upa velues in
Forms (subforms), only in the tables.

 

[RitaMoloney]

Thanks for that, I didn't know.

Rita