dynamically update the fields

[nebon]

hallo there .. I have the following problem :
I have 3 tables

tblShow:
SHOW_ID
FILM_REF
SHOW_DATE
SHOW_TIME
SHOW_SEATS
SHOW NO
--------------------------------
tblBooking
BOOKING_NUMBER
MEMBER_REF
SHOW_ID
BOOKING_DATE
BOOKING_SPEC_NO
---------------------------------
tblFilm
FILM_REF
SEASON_ID
ORDER_NO
FILM_TITLE
FILM_CAT_TYPE
FILM_PRICE_PW
FILM_NO_WEEKS
FILM_DESC
FILM ITEM NR



okai now to the question , I am trying to create a booking where the user only needs to select the Film_title , then the query should automatically locate the appropriate shows and list them in a list with show_date and Show_Time , I have tried to do this in a query , but his wouldnt work , could someone help me by telling me how to do this in vb instead!? please ? thanks alot in advance !

 

[Pat Hartman]

You need a query whether you do this in VBA or not so a querydef is best. However, there is one little problem, there is no way to get there from here. The primary key of tblFilm needs to be in tblBooking to create the many-to-many relationship between tblShow and tblFilm.

Take a look at the Many-to-Many sample db that I posted earlier today.

 

[Travis]

In most cases I agree with Pat. In this one, I don't.


First from what I see:

tblBookings.[SHOW_ID] ->tblShow.[SHOW_ID]

tblFilm.[FILM_REF] -> tblShow.[FILM_REF]

This is all the relationship that you need (Films have multiple shows [one-to-many]). A booking has only one show [one-to-one].


On a form add these fields:

An Unbound ComboBox with two columns (FILM_REF and FILM_TITLE). Set the First column to zero length. You can create a Query off of the tblFilm to just return these two fields with a sort on the Title.

Next add a Bound ListBox (Bind it to the [SHOW_ID]), set it to three columns the first column zero length. Create a Query and make it the RowSource of the ListBox

SQL of Query

Select SHOW_ID, SHOW_DATE, SHOW_TIME From tblShow Where [FILM_REF]=Forms![Your Booking Forms Name].[The ComboBox's Name]

On the AfterUpdate Events of the ComboBox and the Current Event of the Form. add this code me.[Listbox's Name].Requery


What this will do is:

On the Selection of a Film in the combobox, this willset the List Box to a list of the Shows from tblShow. Then when they click the Date/Time they want it will set the SHOW_ID field of the tblBooking.

 

[nebon]

thnx for all the help to begin with.

And actually the relationship between tblshow and tblbooking is
one to many (one show can have many bookings)

now then, you said :

An Unbound ComboBox with two columns (FILM_REF and FILM_TITLE). Set the First column to zero length. You can create a Query off of the tblFilm to just return these two fields with a sort on the Title.

I have not managed to create a Query off of the tblFilm to just return these two fields , I mean I tried to set form!booking!combo.column(0) as a criteria for film_ref in the query to only return the record in accordance to the selection of the film title in the combobox. is this the way to do it ?

nebon

 

[Travis]

First you are correct the tblShow is a one to many relationship to tblBookings (thinking to fast)

Create a query off of the tblFilm

Select [FILM_REF], [FILM_TITLE] From tblFILM Order By [FILM_TITLE]


Now on the Combobox, set this query as the RowSource. Set the Column count to 2. Set the Column Widths to 0";2" (First column is the FILM_REF field user does not need to see this as they only know the film's title)

Now how do you get it to show that film in the Combobox when you have left that record and then return?

Answer:
The record has a SHOW_ID. The SHOW_ID corresponds to a FILM_ID. Set the value of the combobox = to the FILM_ID returned using a DLOOKUP Function on the Form_Current Event.

 

[Pat Hartman]

Sorry, I didn't see the FILM_REF field in the tblShow.