Merging query line with linked subform data

[apk19]

Hi everyone,



Love the forum and all the expertise here. I've hopefully got a quick and easy problem with a CRM database I'm building.

I'm wanting to move certain query information relating to a subform into the subform line instead of having it in a dropdown box as it currently is. I've tried to help by attaching an image to explain.

Essentially, I have three tables. "Sponsors" table (main form) > "Sponsor nominees" table (a simple relationship table linking Client IDs of sponsors with nominees. The screenshot is of this subform) > "Nominees" table (separate table with other information. The dropdown information in the screenshot). Obviously, I only want to keep one set of data in the "Nominees" table but want to display it a little more clearly.

Any help would be greatly appreciated.

[ItsMe]

Are you using lookup fields in your tables? If you are, you need to determine the ordinal position of a specific element within the lookup and assign that value elsewhere. What is that a screenshot of, a form?

[June7]

Doesn't look like a 'dropdown' box (combobox) to me. Looks like table/subtable display. Really should work with forms, not directly with tables/queries.

[NTC]

as with both IM and J7 comments - I don't quite follow your post when looking at your image. However I think I understand what you are trying to say; whether a combo box or a a lookup field type - there is an issue that it is bound to (and displaying 1 field) but in the real world it is necessary to view the values of other fields in the same record at the same time. To do this you set up unbound text boxes that call/refer to the combo/lookup field's other columns/fields.


First, the underlying query/record set of the combo/look up must include these fields so that when one triggers the drop down action you see all fields during the action of when a record is selected - - then along side the combo/lookup field you put unbound text boxes with the control set to call these other fields using this syntax:
=[ComboBoxControlName].Column(2)


I use column 2 just as an example....in the typical set up default the record set's first field/column is bound and is considered 0, the second field/column is what is displayed in the combo/lookup field and is considered 1; so I used 2 in order to display the field/column......

[apk19]

as with both IM and J7 comments - I don't quite follow your post when looking at your image. However I think I understand what you are trying to say; whether a combo box or a a lookup field type - there is an issue that it is bound to (and displaying 1 field) but in the real world it is necessary to view the values of other fields in the same record at the same time. To do this you set up unbound text boxes that call/refer to the combo/lookup field's other columns/fields.


First, the underlying query/record set of the combo/look up must include these fields so that when one triggers the drop down action you see all fields during the action of when a record is selected - - then along side the combo/lookup field you put unbound text boxes with the control set to call these other fields using this syntax:
=[ComboBoxControlName].Column(2)


I use column 2 just as an example....in the typical set up default the record set's first field/column is bound and is considered 0, the second field/column is what is displayed in the combo/lookup field and is considered 1; so I used 2 in order to display the field/column......

Thank you very much for the replies!

You were dead on with my meaning, except I was able to use the DLookup function for the correct values. I would not have been able to find it without your assistance.

[apk19]

Hi June,

It is a subform in datasheet view, within a form.

[June7]

And the main form is also in datasheet view?

I have never set up form/subform with the main form in datasheet view, only with subform in datasheet view.

[apk19]

And the main form is also in datasheet view?

I have never set up form/subform with the main form in datasheet view, only with subform in datasheet view.

Hi June7,

Main form is in Form view, definitely not datasheet. The screenshot was of teh subform (in datasheet view).

[June7]

Then I don't understand why you have an image that looks like a main form in datasheet view with a subform expanded. However, if you have solved the issue, then my confusion is moot.