basic query form, foreign key field

[tonyx666]

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?

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[JoanneM]

On the bookings form place a combo box to do a dropdown list of customers (use the wizard and source your data from the customer tbl)
Bind this to the customer id in your bookings table and you will have accomplished you objective of populating the bookings table. Additionally you could open the customer form by double clicking the combo box if a name is n't present and on the double click property place the Docmd.openform.frmcustomer ,,, addnew record - check help for correct syntax and this will open the customer form at a new record,

before you close the customer form don't forget to refresh the combo box on your bookings form
forms!frmbookings!cmbocustomer.refresh.

Hope this helps

jo

 

[tonyx666]

yes.. lol.. i am aware of both of these solutions.

however, is there not a way to simply include the 2 customer fields in the booking form.. without opening a new form.. and accomplishing the 2 tasks i outlined above?

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[TheAceMan1]

How are ya tonyx666 . . .

Your table setup is [blue]perfect for the form wizard[/blue]. Enter the data from both tables and in the next window make [blue]Customer[/blue] the [purple]mainform[/purple] with [blue]Booking[/blue] as the [purple]subform[/purple] . . .

See Ya! . . . . . .

 

[tonyx666]

im sorry, could you just repeat that..

are you saying create a query with the fields from both tables.. and then use the form wizard to create the subform..

or are you saying create two separate forms based on the tables themselves, and then create a sub form

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

OH, i see what you mean.. use the wizard, enter all the fields, and then do the subform. ok ok, let me try that

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

yeah, that is it.. im gonna start developing this form now, i might have 1 or 2 simple requests shortly, thank you aceman

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

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.

London Heathrow Cars

http://www.LondonHeathrowCars.com