Complicated form data source

[George Bowyer]

Well I find it complicated.

I have a table of people, each with an ID, and I have a table of events, each with an ID.

I want to design a form in such a way that when you look at each person it gives a row of event fields, one for each event, into which you can put a Number for the number of tickets that each person wants (each person will only want to go to a few events).

If you enter a number, I want the form to generate new record in a separate table (tickets) with three fields - the Person's Id, the Event ID and the number of tickets.

I only want one ticket record per person per event (so if the numer of tickets changes I want the form to amend the number, not create a new record.

So, I want the Form to show "page" for each person, with "fields" for each event, the info being stored in a separate table, which the form is able to cycle through, despite the fact that there will not be records for every person.

I am a bit flummoxed as to how to design it.

Can anyone give me any guidance, please.

 

[George Bowyer]

Hmm. Not really following this.

The three pieces of info that I need are the ID of the Person, the ID of the event and the Number of tickets.

I'm no sure which of these you are proposing should be stored in which of the new tables (tblPurchase and tblLineItem).

Also not sure exactly how this helps me get the form structure that I need from this

To supply a bit more detail, each person has been sent a piece of paper (pro-forma) with their name at the top and a list of events with a little box in which to write the number of tickets required.

I am trying to make a form that looks as similar as possible to that profroma(to make it ever-so-simple for the operators to enter the information when the pro-forma is retunred).

So I'm looking to get a form based on the Customer table (that you can just search for the right name) with an array of boxes into which people can put numbers to match the ones on the pro-forma.

Once the info is all entered. I can then print lists of which people are coming to which event (with how many tickets) and how many events each person is going to.

 

[RV]

Hmm. Not really following this.

WHat Uncle Gizmo tells you is what datastructure you would need to accomplish what your want.
Your main process is 'bout selling tickets, therfore you need a main form based on your actual Purchases, not on your Customers.

You actually confirm this yourselves by the additionalinfo you state in your latest update.

Review the NorthWind sample database, it will help to to understand how you should set up your applic.

RV

 

[George Bowyer]

I have already go a form that does what I want using forms like those contained in Northwind.

I have a list box to select a Customer and a list box with all the Events. Double Click on an event and it creates a record in tblCustEvents with the CustID, the EventID and a field for number of tickets. I want this table in this format because it makes it easy for me to generate the reports that I need.

This info is then shown in another list box that I can then manipulate to delete incorrect entries and increase the number of tickets.

All of this is no problem; however, it is NOT what I am asking how to do.

What I want to do is create an idiot-proof form for the volunteer munchkins who will be using it that looks as exactly as possible like the pro-formas that they will be receiving back from the clients.

The pro-formas look something like this :

Name : ………………………….
Address : ……………………………….

Indicate in boxes the number of tickets you require for each event.

Event 1 [ ] Event 2 [ ] Event 3 [ ]

Event 4 [ ] Event 5 [ ] Event 6 [ ]

Event 7 [ ] Event 8 [ ] Event 9 [ ]

Etc…

I want to create the form to look EXACTLY like that. No drop down lists and no double clicking.

The operator will use a find button to search through the Clients to find the relevant person.

If info is already entered for that client in the “tblCustEvents” (format as above) then I want it to show up in the boxes when the client is found on this form.

I want the action of the operator entering a number in the relevant box to either create a new record in tblCustEvents or edit an existing one as appropriate.

At the moment, I am working on a theory that the boxes could be a sub-form based on a cross-tab query based on tblCustEvents, but the problem with that is that it is uneditable, so that I would have to use some sort of after-update event on the on the boxes to make the changes.

It’s very messy, and it seems to me that there should be a neater way to do it?