I am not too sure how to go about this.

[u]First:</u>
I have 2 tables: "Shows" (Id, Name, Date of event, time, place, type of event...) and "Tickets" (ticketId, showId, ticketNumber, level, section, row, seatNumber, Price...) so, for each show, I have an inventory of tickets (relation of 1 to many).

[u]Now, what I have to do is:</u>
build a form that will help us add the same ticket information to a number of shows. For example, I want to add a ticket (ex: level = Floor, section = Center, row = 1, seatNumber = 1, price = $25.00) to shows selected in the form (ex: Show1, Show3, Show4, Show9)

[u]Front</u>
In the top part of the form would be the ticket information;
the bottom part would list all the shows (date of event > now) and with a control button or maybe a check mark to add the ticket.
When we select the show, the price information will show up beside the show's name in order to change it if we need too...


[u]I started the design of the form:</u>
in the top part I have all the fields with the ticket information but named parmLevel, parmSection, ... The fields are not linked to the Ticket table.

Below, I have the list of the shows I need (ShowNumber, Name, Date and Time) with an "Add" control button.

[u]I can't seem to :</u>
- find the right way to add the ticket to the shows selected
- show the price beside the show.

To add the ticket, I use an Insert Into SQL.

You are now my only hope to solve this problem...

Thanks in advance

Chantal

Sounds like you need a junction table.

tblShow
ShowID - PK
...

tblTicket
TicketID - PK
...

tblShow_Ticket
Show_TicketID - PK
ShowID - FK
TicketID - FK
...

This creates the many to many relationship I think you are looking for.

HTH



mmcdonal

I would suggest that this is a bit more complicated than merely adding a join table between the Show and Ticket tables (which implies removing the foreign key of ShowId from the Ticket table).

What you are describing exposes at least two other entities: SEAT, and PRICE.
There are also other entities implied, which includes LEVEL, SECTION, ROW, and EVENT.
Even deeper is that there are rules and entities that are not yet expressed.

So... you certainly have a lot to think about!

Woody Z
http://www.learntoprogramnow.com

If I don't need to, I prefer not changing the Ticket table. It would have a big impact.

Hence, I am still trying to work on this thing...

I have an INSERT INTO statement to add the ticket to one show... if my show originally has 100 tickets, when I run the sql, it adds 100 times the same "new" ticket... What is it I am not doing right?? here is the sql I use:

strsql = "INSERT INTO SHOW (IdEvent, TicketNumber, Level, Section, ...) " & _
"SELECT [parmIdEvent] as IdEvent, [intTicket] as TicketNumber, [parmLevel] as Level, [parmSection] as Section, [parmRow] as Row... " & _
"FROM SHOW " & _
"WHERE IdEvent = " & Me![IdEvent] & ""

How can I add just one ticket ???:(

This is what is causing the problem:

"WHERE IdEvent = " & Me![IdEvent] & ""

First of all, you can change this to:

"WHERE IdEvent = " & Me![IdEvent]

Secondly, since there are 100 instances of IdEvent, then it will work on 100 records.

If you can't rebuild the table structure normally, I would do the following:

Rebuild the table structure properly as we discussed and use it to store the records. Then use your existing table as a transaction table. When a record is added to the good table structure, take the fields you need to populate the bad table and just insert one record as a transaction. Do that with code on the form... which could be based on the good tables.

Ihave to say taking the time now to rebuild this with the junction table you need will reduce the overhead for the life of the application.

HTH




mmcdonal

I'm not quite sure I understand the premise of your question. Let me give it a shot, though.

The top of your form has an area where you enter the information about the seat in unbound fields.

In the middle of your form, you have a subform that lists all the shows. A checkbox on this subform is checked if you want to add the specific seat at the top of the form to that show.

At the bottom of the form is a button that says something like "Add tickets".

In the on click event of the Add Tickets button, add the following code:

Private Sub AddTicket_Click()
    With Me.Subform1.Form.RecordsetClone
        .MoveFirst
        Do While Not .EOF
            If !AddThisShow Then
                DoCmd.RunSQL "INSERT INTO SHOW (ShowID, Section, Row, Seat) VALUES (" _
                   & str(!ShowId) & ",'" & me.Section.value & "','" _
                   & me.Row.value & "'," &str(me.seat.value) & ")"
            End If
            .MoveNext
        Loop
    End With
End Sub

Note: I made assumptions that certain fields were numbers and which were strings. There are examples of both numbers (those that have the str() function) and strings (which need the single-quotes ' added to the SQL statement.

This loops through each record displayed in the subform, and adds a record to your show table if the AddThisShow checkbox is checked. When the values are inserted into the shows table, the showid is taken from the current record in the subform, and the seat information is taken from the fields on the form.

Thanks a lot ltdavecass,

I will try this and let you know if it works but it is exactly what I am trying to do.

Chantal

I am trying to make this thing work, but I am against a little glitch: how can I had a subform in a form with unbound fields... in other words, in my main form I have no field in common (yet) with the subform. The link is made after I add a ticket to the event.

When I want to add the subform to the main form, I am supposed to determine the "father" and "son" fields... but I have no "father" field...

Am I clear ???

Chantal