Ticketing program for community theater

[Bill]

Greetings,

Our community theater does six productions per year with six shows per production and have 17 rows of 10 seats each for a total of 6120 unique seats per year. All current ticketing is done manually on a paper seating chart so we have no digital tracking of patrons and a mile long paper trail.

I envision an interface illustrating the seating chart (170 boxes on a form) each form with a diffrent date. The order taker would have the ability to click on a particular seat to bring up a new patron record. Once the data is input (Name, phone, email, payment type, etc.) the color of the box changes to indicate it is no longer available with the patron name in the box. It would be nice if it would be easy to populate adjacent seats with the same data in the case of multi-ticket purchases.

I would also like to store the records of patrons for future mailings and communications, the details of which I can handle my self.

There are store bought solutions but I think I can do this myself with a little direction.

Do you feel access is the proper program for this?
Will it lend itself to this application?
Has anyone created a program similar?
Is there any design considerations I should know?

There are the seat/row and date field combined will create a unique key.

Thanks in advance for your input.

Bill

[maximus]

yes access will be just fine i had designed something similar and will be happy to help you out

the only thing that I want to know is that do you have individual seat numbers like 26,27,28 or do you lookup seats with grid ref like Col1,Row2

if you can give me this information i will give you a complete design pattern.

[mltheater]

i realize that this is an old thread but can you tell me how this is accomplished?

[qa12dx]

make a unbound form, insert unbound text box. delete the label. text box control source=1
copy and paste as many seats u need. change the control source value to corrospond to seat number.

in event for each text box
onclick - setvalue - item - seat_booked (textbox described later), expression - iif([seat_booked]=0,[seat1textbox],[seat_booked]&","&[seat1textbox])

at bottom of page insert another unbound text box. change lable to "Seats booked". set textbox default value=0

when u r ready to check out, use the "seats booked" textbox value to store in order table. for this feild i'd use multi value field. if u don't know how to make multi value field, google it, many examples. that way u can store more than 1 seat number to 1 purchase record.

now if u want the selected seats darkened after purchase, do requery when u get back to the form and use conditional set property macro, where u have a visible.no textbox with purchased seat numbers

[ssanfu]

for this feild i'd use multi value field.

I would advise against this. It is way more trouble that it is worth.

Here are some models that might help you design your table structure.
The main site is http://databaseanswers.org/data_models/
The TicketMaster example is at http://databaseanswers.org/data_mode...ster/index.htm
There is an Event Reservations example: http://databaseanswers.org/data_mode...ions/index.htm
"Restaurant Event Bookings" is kinda like an event: http://databaseanswers.org/data_mode...s_bookings.htm

You can cut out the tables that are not necessary for your requirements. There will be some VBA involved - probably in marking a seat sold and changing the colors.

Sounds like a fun project.

[mltheater]

in event for each text box
onclick - setvalue - item - seat_booked (textbox described later), expression - iif([seat_booked]=0,[seat1textbox],[seat_booked]&","&[seat1textbox])


could you explain this a little more? thanks

[qa12dx]

seats_booked will be a textbox where u capture the seat numbers a person is booking. when you click on a textbox which is a seat number

onclick - setvalue - item - seat_booked (textbox described later), expression - iif([seat_booked]=0,[seat1textbox],[seat_booked]&","&[seat1textbox])

this is setting the seat value in the seats_booked textbox. like i said have the default value of seats_booked textbox as 0. it is sometimes harder to deal with null fields. so suppose u click seat 26, it sets seats_booked value as 26. next you click seat 27, now it sees that there is a value in seats_booked, so it takes what is there an adds [seats_booked]&","&26. any text value has to be in quotes in access. now u have a value, 26,27. as many seats you click it will keep adding that seat value. so you end up with a value as 26,27,28. now you can use this in a lookup query where the creterian will be where table.seats in([seats_booked]), or u can use the value to store to a multi value field along with the date stamp.

the tricky part will be when someone changes his/her mind. you will have to do the opposite of above to take out the seat value unchecked.
if u want to make something like this. start making the db, when you get stuck post here and i'll help u.
i'm new here now not sure if/how u can link a q to me...

[mltheater]

coulds you do a sample? i am just not grasping this for some reason.

[qa12dx]

theater.zip
here, take looky