Cascading Combo box and Many to Many Relationship

[20028431]

This is driving me mad!

I have been trying for 3 days now to work out how to do this with no success.

I have tables for Customer, Bookings, Entertainment, Act, Agent, and several others.
I have created a form to view existing bookings with Customer details as mainform and Bookings and Entertainment as subforms and this works fine.

I am trying to create forms to allow the booking clerk to enter a new Booking and am looking to have a Cascading Combo box for Entertainment Type (eg. Disco, Magician, Harpist etc) which upon selecting, offers the relevant Act Names in a second Combo Box.

I have built cascading combo boxes before but all have been based upon a one to many relationship - I just can't get my head round what I need to do for a many to many relationship.

The following 3 tables show details of table

Entertainment
EntID (PK) - autonumber
Type - text

Act
ActID (PK) - autonumber
ActName - text

EntertainAct
EntID - number
ActID - number


I am using Access 2007

Can someone please help before I smash my head against the wall!!!

[beacon]

Have you checked out the thread below? It really helped me out with cascading.

http://bytes.com/topic/access/insigh...mbo-list-boxes

[FishVal]

The second ComboBox.RowSource should be join of tables [Act] and [EntertainAct]. This could be easily filtered by EntertainAct.EntID field.

Regards,
Fish

[OldBirdman]

This is the CLASSIC business model. Any Customer can buy any Service/Product/Act and any Act can be supplied to any Customer.. When you can't get your head around something, usually you haven't really defined your goal.
First, try NorthWind, supplied with Access (all versions, I think) for ideas.

Then take it step by step, maybe with paper and pencil (am I showing my age here?)
Customer calls booking clerk. What needs to be done first? Maybe bring up customer form? Verify that they are prior customer, all bills paid, addresses/phone #s up to date?

Customer will want an Act? By Entertainment or by Name? By either? He will want to know if available for some date or date-range? What do you want to see now? Possible Acts are those "Not Booked" and those should be filtered. From the available acts, you can generate the possible Entertainments.

Apparently Acts can be listed by multiple Entertainments. If you know the Acts that meet the original filter criteria, probably dates, then you build the necessary comboboxes.

Comboboxes dependent on prior choices don't always cascade. At least not in the usual sense. This can allow for some creative solutions.

These are just my thoughts, to get you thinking straight, not to supply specific answers.

[ADezii]

@20028431
Along FishVal's Trend of Thought in Post #3 only a little more unnecessarily complicated, which I always seem to do:

1. cboEntertainment
   1. Column Count = 2
   2. Column Widths = 0;1
   3. Bound Column = 1
   4. Row Source = "SELECT [Entertainment].[EntID], [Entertainment].[Type] FROM Entertainment; "
2. cboArts
   1. Column Count = 3
   2. Column Widths = 0;0;1.5
   3. Bound Column = 1
   4. Row Source = None
3. AfterUpdate() Event of cboEntertainment
   

   Private Sub cboEntertainment_AfterUpdate()
   Dim strSQL As String
    
   strSQL = "SELECT Entertainment.EntID, Act.ActID, Act.ActName FROM Act INNER JOIN " & _
            "(Entertainment INNER JOIN EntertainAct ON Entertainment.EntID = EntertainAct.EntID) " & _
            "ON Act.ActID = EntertainAct.ActID WHERE Entertainment.EntID= " & Me![cboEntertainment].Column(0)
    
   Me![cboActs].RowSource = strSQL
   Me![cboActs].SetFocus
   Me![cboActs].Dropdown
   End Sub

4. We'll allow you to cheat and Download the Attachment

[20028431]

Many Thanks to you all - will follow your suggestions tomorrow and post back to let you know how I got on.
Once again many thanks

[NeoPa]

Some excellent advice contained here.

I couldn't resist posting after reading ADezii's post. Priceless (in the nicest way of course) :D

To the OP - Welcome to Bytes!

[20028431]

Hi Guys

Just wanted to say a massive big thank your for all your responses but in particular to ADezii (I like unnecessary complication!!).

I did download the zip file to see your example and then worked through the steps you oulined only to find that my initail problem was with my AfterUpdate SQL INNER JOIN coding duh!!!!!

Once again many thanks for your time.

[NeoPa]

A pleasure dealing with you. It's always nice when a member posts back with thanks and an update.

Don't worry about the problem being somewhere else all along. That is so common, and just looking deeper into things often helps.