basic query form, foreign key field

[antonyx]

hello.. i have 2 tables

CUSTOMER TABLE
customerid
customername
customerphone


BOOKING TABLE
bookingid
bookingdate
customerid


At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.

at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..

they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..


what i want to do is allow the user to enter the customer details and the booking details in one form..

i assume i would have to create a third form based on a query..

if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?

1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..

2. this id will then need to be auto placed into the foreign key customer id field in the booking table.


do you get me?

 

[Joebax]

I only know access 2003 and not much at that, but this is what I would do.

Make sure you have a relationship between your customer and booking table, based on Customer_ID. (You can right click on the tables area and choose relationships.)

Then open your customer form and make it larger. Then Choose the Bookings form from the database window and drop the form on the customer form. It should automatically create the Parent/Child properties. You can check this by choosing the subform (Bookings) and checking the properties under the data tab. The fields are called Link Child Fields and Link Master Fields. They should both be Customer_ID.

This should automatically control filtering on your child form to only show records related to the cutomer record that is selected in the parent form. If you enter a new customer you should show no records in the bookings subform but you can add one or more after saving the customer record.

I would have my all my ID fields set as AutoNumber and Primary Key. You can hide those fields and they will allways make sure the tables have a unique relationship. I sometimes keep them on the form but locked just to make sure everything is working and then remove the controls before going into production.

Hope this works for you.

 

[antonyx]

ok, i have posted many times on this issue and im slowly trying to create the most efficient way of doing this..

i described my table layout as above:

CUSTOMER TABLE
customerid
customername
customerphone

BOOKING TABLE
bookingid
bookingdate
customerid (this is meant to be the passenger)

basically every booking will have 1 booker and 1 passenger.. this may or may not be the same person..

so a more accurate table layout will be something like this..

CUSTOMER TABLE
customerid
customername
customerphone

BOOKING TABLE
bookingid
bookingdate
booker (customerid foreign key)
passenger (customerid foreign key)

eg.
john can book a car for himself- booker=id1 passenger=id1

john can book a car for his wife- booker=id1 passenger=id2

(both john and his wife will need to be stored in the customer table)

first problem..
naming and structuring the fields like this i am already having problems creating a subform because the fields are not named the same.. and im using the customer id twice in the booking table as 2 different field names..

second problem..
to create this subform, i assume the customer table will need to have a direct link to the booking table, and these fields can be linked on the subform and only display the linked-by-customerid records.

if john has booked 5 jobs for his wife..

and john has also been a passenger in 3 jobs which were booked by tim..

i dont know how i should layout the form.. i dont want to create 2 separate form-subform combinations.. one that shows the bookings where the customer id was the booker, and one that shows the bookings where the customer id was the passenger..

is this making any sense.. i can provide more information if needed, i just want to resolve this problem once and for all, seeing as i have avoided the best solution for far too long.

 

[Joebax]

Hmm thats a bit tricky. More so than I thought. I have never needed to do something quite like this.

Sounds like you have a many to many relationship rather than one to many.

One Customer can have many bookings and one booking can have many customers. But in this case you only want two, a booker and a passenger.

Someone else can likey give you a much better option, but my thoughts on table design would be to make three tables one being an associative entity.

Customer_ID
Booking_ID
Type

Scenerio 1
john can book a car for himself- booker=id1 passenger=id1

Booking_ID = 1 | Customer_ID = 1 | Type = "Passenger"
Booking_ID = 1 | Customer_ID = 1 | Type = "Booker"

Scenerio 1
john can book a car for his wife- booker=id1 passenger=id2
Booking_ID = 2 | Customer_ID = 2 | Type = "Passenger"
Booking_ID = 2 | Customer_ID = 1 | Type = "Booker"

I am learning now to build forms to handle many to many relationships. The book says its relatively simple, but to do exactly what you want may be more dificult and require more coding. You want to limit the associations to two and one has to be type Passenger and one type Booker.

When its all said and done you may just want to keep it to two tables like you defined above and do alot of custom coding.

Maybe someone else can help. If you find an answer somewhere else please post a link here so I can check it out.