form layout requirement

[antonyx]

ok, i have a few design requirements and am unsure as the best way to approach the situation..

here is the basic layout of my tables..

http://www.londonheathrowcars.com/bait1.jpg


i will basically need a form with the following layout

http://www.londonheathrowcars.com/bait.jpg


you enter the date at the top of the form.. and then all the records you enter will use that value as the job date..

also.. the customer name and number will need to be entered in this form.. and the customer id will have to be registered to the job in the job table..


can anyone suggest where i should start with regards to creating a subform.. a query.. etc??

 

[antonyx]

ok.. this is what i have done to try and get the ball rolling, but i do need advice/help here..

ive made a query.. nothing too complicated.. i have just included the fields from the three tables that i want to see on the form..

here is a screenshot.

http://www.londonheathrowcars.com/bait3.jpg

now.. i tried using the form wizard to create a form.. and i hoped this would just magically fix everything..

well.. obviously its not this simple..

it asked me how i wanted to group the form

http://www.londonheathrowcars.com/bait4.jpg

when i chose job as the main form view.. and made the form..

1st problem
in the form i wasnt able to add any details to fields that belonged to the job table.. i was only able to add driver and passenger details..

2nd problem
this is clearly not the right way to do it.. when i entered driver and passenger details.. it added a job to the job table, id 1, but the ids of the passenger and drivers were not added to the job table..

i know this is the wrong approach.. but i also know there is a way to do this.. so again.. if anyone can suggest something im more than happy to try it vigorously till it works..

thank you

 

[Leopardfist]

OK, I will give this a try.

After your 3 tables are completed, set the relationships, and make sure to use referential integrity and cascade the update. I doubt you want to cascade the deletes.

After that use the wizard to create a form. Add ALL fields from ALL 3 tables to the form.

On the next step, choose how you want to view it, choose job.

On the 3rd step choose Justify.

On the 4th step choose what ever style looks good to you.

On the laststep choose the forms name, and select "Modify the form in design view".

Once you click finish it will open the form in design view. This is where you move your controls around to match how you want it to look, like in your screenshot above. Once done save it and open it. As you use it it should store data in all relevant tables/fields, but be warned. If you use the IDs of each table as the PK's, you have to be careful that you dont accidentally get duplicate fields for the same driver/passenger/job due to mistyped text.

I suggest making the Passenger Name, Driver Name fields set as a combo box, so the usercan just use a drop down control to choose which they want, or they can enter a new one by manually typing it in. I would also set the ID's for passenger and driver to autonumbers, so when you enter a new name, it automatically creates a new ID and lists it, so you do not have to manually enter the ID.

I hope this is what you had in mind, I am far from good on access, and there is probably a lot of better ways to do this, but I hope it helps.

 

[antonyx]

After your 3 tables are completed, set the relationships, and make sure to use referential integrity and cascade the update. I doubt you want to cascade the deletes.

After that use the wizard to create a form. Add ALL fields from ALL 3 tables to the form.

On the next step, choose how you want to view it, choose job.

On the 3rd step choose Justify.

On the 4th step choose what ever style looks good to you.

On the laststep choose the forms name, and select "Modify the form in design view".

Once you click finish it will open the form in design view. This is where you move your controls around to match how you want it to look, like in your screenshot above.

ok.. up that point i have done as you said..

here is the screen showing the form at that point..
http://www.londonheathrowcars.com/bait6.jpg

i think that mite have done the trick..

i enter the data and it seems to correlate.. thank you.. im going to start another post now because i need to add something more advanced to this form and it needs a new post title.. thanks for this though..

if anyone can see any major problems with this solution, speak now...