How do I show meaningful data in a sub-form?

[richardcb]

This is probably a newbie question, please be tolerant.

I have successfully created a many-to-many relationship between two tables in my database but now want to see the results of that relationship.

So far I have created a Form from my main data table and added a Sub-form from the junction or link table. This shows the genres I have added for each item, but only as ID numbers (the numbers for the item and its linked genres). How do I go about showing the text that goes with the ID numbers?



I'm basically trying to create a Form that allows me to view each item and all of its linked genres plus giving me the ability to add new data in one place. Is this possible?

I'd appreciate any help you can give me.

[June7]

Options:

1. multi-column combobox http://www.datapigtechnologies.com/f...combobox3.html

2. The subform RecordSource can be a query that includes the lookup tables. Set the join as "Include all records from {junction table} and only those ..." Bind textboxes to the lookup table fields and set them Locked Yes

If you want to allow adding new records to lookup table 'on the fly' during data entry, that requires code in combobox NotInList event. http://www.databasedev.co.uk/not_in_list.html

[richardcb]

Thank you, June7.

[richardcb]

Options:

1. multi-column combobox http://www.datapigtechnologies.com/f...combobox3.html

2. The subform RecordSource can be a query that includes the lookup tables. Set the join as "Include all records from {junction table} and only those ..." Bind textboxes to the lookup table fields and set them Locked Yes

If you want to allow adding new records to lookup table 'on the fly' during data entry, that requires code in combobox NotInList event. http://www.databasedev.co.uk/not_in_list.html

Sorry to sound dense but where to I find RecordSource? I've searched the Property Sheet but can't see it listed.

To give you an idea of what I've done so far here is a screenshot of my form and sub-form. All I want to do is show a field next to the tag ID with the text relating to that ID. Is this possible? As always, any help would be greatly appreciated.

[June7]

RecordSource is property of form or report. Make sure the form or report is the active object (not some control or section of form/report) and then look at the Data tab.

Users really have no need to see PK/FK values. The multi-column combobox method for the Tag should hide the key and display the descriptive info. If there are several fields from the Tag table you want to display, they can all be included in the combobox RowSource (visible or not visible) then textboxes can refer to columns of combobox (this would be part of option 1 of my earlier post). Column index begins with zero, so if the value you want to show is in column 3 the index is 2.

=[combobox name].[Column](2)

[richardcb]

RecordSource is property of form or report. Make sure the form or report is the active object (not some control or section of form/report) and then look at the Data tab.

Users really have no need to see PK/FK values. The multi-column combobox method for the Tag should hide the key and display the descriptive info. If there are several fields from the Tag table you want to display, they can all be included in the combobox RowSource (visible or not visible) then textboxes can refer to columns of combobox (this would be part of option 1 of my earlier post). Column index begins with zero, so if the value you want to show is in column 3 the index is 2.

=[combobox name].[Column](2)

Once again I appreciate your help with my problem. However, I think we're moving into an area of Access I've had no experience of up to now and I'm getting a bit confused. I've been reading up about the combobox and it looks like an ideal solution but I have no idea how to apply it to my database. Is it something I can add to my existing sub-form or do I have to create a new one from scratch? I did try adding a combobox and setting source type to my tags table, however all that appeared were a series of numbers, presumably the field IDs. Also, I don't know what the 'PK/FK values' you mentioned are.

I am clearly of need of help on a more basic level than I think you may be used to in these forums; more along the lines of step-by-step instructions. Rather than take up your valuable time (and try the patience of your experienced members) perhaps you could suggest somewhere where I might find this level of help. I am grateful for all your advice and look forward to a time when I can resolve this situation.

[June7]

If I understand the relationship presented in the posted image, it looks like the app-tagID and appID fields do not need to be visible, certainly should not be user editable.

Then the tagID control can be a multi-column combobox as described in the referenced link. I have no idea what the structure of the Tags table is - what fields you would want to display in the combobox dropdown list. But just as a generic example, the combobox properties could be like:

Name: cbxTag
RowSource: SELECT TagID, TagName, TagDesc FROM Tags;
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0",1",1"
ControlSource: the field name from app-tag table to save tagID

Then if you want to display the TagDesc in textbox, ControlSource can reference the column by its index - 3rd column is index 2:

=[cbxTag].[Column](2)

Try it, you'll like it!

[richardcb]

Once again, June 7, thanks you for your help...and your patience.

To explain my database more clearly, the Tag table is a single-column list of words which describe the features of the app. All I need to see is the word which accompanies the tagID in the subform. You're right, I don't need to see app-tagID or appID fields but I'm not fussed if they are there. The subform is only there to display what tags/features are linked to each record.

In the example you've supplied, do I enter this as a new field in my existing subform or create a new subform for it?

I'm not going to let this problem beat me. One day I'll see the light and you can go and lie down in a darkened room and wait for the headaches to stop.

[June7]

If the Tag table has only the ID field and the Description field, then I don't understand what you want. There is no other info to be displayed. Just have the multi-column combobox as described but for 2 columns instead of 3. The tagID column will be hidden and the Description field will show.

I presume the subform is bound to a junction table. The combobox example described should not require another field. Use combobox instead of textbox for the tagID, which I also presume is a field in the junction table for saving the Tag table id as a foreign key.

How long are these 'words' in the Description field. Is it only a single word or are they phrases? An alternative is to eliminate the tagID field and just save the word into the junction table. The advantage is not having to join tables in queries to retrieve info associated with ID.

[richardcb]

Success! Following your excellent advice I have managed to create a field which displays the text matched to the tagID (see screen shot). It's not pretty but it works.

Now I just need to refine it. Ideally I want to hide three ID fields (I know I said I wouldn't mind leaving them) but that's a task for later.

Re your latest questions: The subform is bound to a junction table and the words in the Descriptive field are single words of no more than 10-12 characters, no phrases.

So am I right in understanding that I can replace the current tagID field with a combobox? If so how will it link to my tag table through the junction box?

Thanks for your continued patience.

[June7]

The combobox shows items in its dropdown list from the Tag table. The combobox ControlSource needs to be a field from the junction table. The tagID value of combobox will be saved to the junction table field.

[richardcb]

The combobox shows items in its dropdown list from the Tag table. The combobox ControlSource needs to be a field from the junction table. The tagID value of combobox will be saved to the junction table field.

Once more I thank you for your assistance, June7. I'll continue to play around with my database and incorporate your tips. For now I'll leave you alone to deal with less frustrating matters.

[June7]

And yes, the TagID textbox is not needed because of the combobox.