Novice - ComboBox - from a table of multi values.

[Ruegen]

Hi,



I have a problem and could gratefully use some help.

I have a combo box on a form that drops down to show the name of the shows but not the two performers that I have assigned to it - it just shows their individual ID keys instead of their names. If I edit the lookup query for that combo box the query its self runs fine but when I go back to the form and use the combo box I get two results of the two shows (I think because I've added the names to the query). I'd like to get the names to show up instead of their ID keys in the combo box drop down.

I've supplied a couple of images.

[alansidman]

Please post your SQL statement for your query. You probably need to add the table that the combo box relies on to your query.

[Ruegen]

Please post your SQL statement for your query. You probably need to add the table that the combo box relies on to your query.

Hi alansidman, - I'm making the form directly from a table into a form - but there is the statement.

[Ruegen]

If I add any column in the table (SQL) like say FullName (a calculated column that calculates the first and surname for viewing purposes) I get two results of the same show when I would rather just one result and the two performers names listed by each other like their number IDs are...

[Ruegen]

You could say I just want to have the ID show once not twice in the query

[June7]

Combining values from related records to a single field requires VBA. Review: http://allenbrowne.com/func-concat.html

[Ruegen]

Really? It's a multivaule field I am displaying and it shows me already the IDs combined without the code - all I would prefer is to show the names rather than the ids
My Shows table has no trouble when pulling the information directly..

[June7]

Oh, sorry, I don't use multi-value fields. I don't think multi-value field will allow that in query that simply joins the two tables as done in the combobox RowSource, although I am surprised that the combobox list reflects both names, as if the multi-value field is automatically expanded. To get the associated names might have to expand the multi-value field by query then join that expanded query to the Performers table then use the VBA code.

Review:
http://office.microsoft.com/en-us/ac...33722.aspx#BM5
http://office.microsoft.com/en-us/ac...010149297.aspx

[Ruegen]

Used a junction box instead - problem solved in a way...

[June7]

What is a 'junction' box?

[Ruegen]

What is a 'junction' box?

Well not a box, a table, but made to do this...

[June7]

Yes, a junction table is appropriate for many-to-many relationship. Use form/subform arrangement for data entry.

[Ruegen]

Yes, a junction table is appropriate for many-to-many relationship. Use form/subform arrangement for data entry.

Just out of interest, I use the junction table to "apply" the performers to the many shows they do - how would I in the subform do the same thing? I'd like to simply add the performers to the show from the show form rather than using the junction table form....

I'm guessing a continuous form?

[Ruegen]

Ah answered my own question - works too

Now I just want to figure out how to refresh another subform on the list that simply lists the names as links (works, just doesn't refresh when I add a performer in the other list).

Thanks for your suggestions - really helps makes things works better

What I am trying to do next is refresh the subform on the left... hopefully by macro...

[June7]

You are adding a performer to what - associating existing performer with a show in the junction table or are you adding a new performer to the performers table? What is the left subform for?

Try Requery or Refresh action. Both are available in macros.