Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 14th, 2006, 01:55 PM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default A little challenge...

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

 
Old December 14th, 2006, 03:15 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 14th, 2006, 03:38 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 14th, 2006, 04:29 PM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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 ???:(

 
Old December 15th, 2006, 08:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 31st, 2007, 11:11 PM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



 
Old February 1st, 2007, 11:03 AM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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

 
Old February 2nd, 2007, 11:38 AM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
query challenge alexxx Access 5 October 31st, 2006 03:34 PM
Memobox Challenge rocknrisk Access 8 July 30th, 2005 02:36 AM
Fancy a challenge that would really help me out? Brettvan1 VB.NET 2002/2003 Basics 2 June 2nd, 2005 09:42 AM
A Challenge... AForgue XSLT 3 November 5th, 2003 04:14 PM
Challenge.... developerz BOOK: ASP.NET Website Programming Problem-Design-Solution 2 September 29th, 2003 04:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.