Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 14th, 2006, 01:55 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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

Reply With Quote
  #2 (permalink)  
Old December 14th, 2006, 03:15 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old December 14th, 2006, 03:38 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
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
Reply With Quote
  #4 (permalink)  
Old December 14th, 2006, 04:29 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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 ???:(

Reply With Quote
  #5 (permalink)  
Old December 15th, 2006, 08:26 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #6 (permalink)  
Old January 31st, 2007, 11:11 PM
Registered User
 
Join Date: Jan 2007
Location: Portsmouth, VA, USA.
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.



Reply With Quote
  #7 (permalink)  
Old February 1st, 2007, 11:03 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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

Reply With Quote
  #8 (permalink)  
Old February 2nd, 2007, 11:38 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:43 AM.


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