Populate Form From Other Tables

[swedish_mania]

I have 5 tables (Client, Driver, Appointment, LessonType & Booking). The Booking table is empty as it is made up from the other 4 tables. I want to have a form (Booking) which collects data from the 4 tables to create a record.

I want this to be automated as much as possible rather than typing the data into a textbox, because this would be difficult for the user and there would be conflict.

The Booking form will be split into 4 sections relevant to the above tables. For example, select ClientID from a combo and the other fields populate, such as forename, surname, address1, address2. And then select DriverID and this populates the relevant fields and so on, until a booking is created.

Ive tried After_update and couldnt get that to work. Any suggestions or help will be much appreciated as this has been a headache for a few weeks now. Thanks

 

[Pat Hartman]

Create a query that joins the booking table to the other four tables.
Use left joins.
Select whatever fields you want from each table.
Use the query as the recordsource for your form.
Set the Locked property to Yes for EVERY field EXCEPT the the four combos.

 

[swedish_mania]

Create a query that joins the booking table to the other four tables.
QUOTE]

Played around with the query, getting error messages because of inner joins.

Ive attached the DB, can someone have a look and see whats wrong, or advise me on how to do it. Thanks

 

[Pat Hartman]

You had the joins backwards. You need to select all rows from Bookings and any matching rows from the other tables.

The Booking table should only contain the four foreign keys. Remove all the duplicate fields. Then change the query to select the descriptive fields from the proper tables.

SELECT Booking.BookingID, Booking.ClientID, Client.Forename, Client.Surname, Client.Address1, Client.Address2, Booking.DriverID, Driver.Forename AS DForename, Driver.Surname AS DSurname, Booking.SlotID, Appointment.LessonDate, Appointment.StartTime, Appointment.EndTime, Booking.TypeID, LessonType.Description
FROM LessonType RIGHT JOIN (Driver RIGHT JOIN (Client RIGHT JOIN (Appointment RIGHT JOIN Booking ON Appointment.SlotID = Booking.SlotID) ON Client.ClientID = Booking.ClientID) ON Driver.DriverID = Booking.DriverID) ON LessonType.TypeID = Booking.TypeID;

 

[swedish_mania]

Done It!!

Thanks Pat for your help and suggestions, i finally got it working.

I did it a different way..

I changed the cboClientID rowsource to: SELECT Forename, Surname, Address1, Address2, ClientID FROM Client;

And added this after_update procedure and it worked.

Private Sub cboClientID_AfterUpdate()
If cboClientID.Value > 0 Then
Me.ClientID = cboClientID.Column(4)
Me.Forename = cboClientID.Column(0)
Me.Surname = cboClientID.Column(1)
Me.Address1 = cboClientID.Column(2)
Me.Address2 = cboClientID.Column(3)
End If
End Sub

I then did the same thing again for the other combos.

Hope this post comes of some help for someone. Thanks again for your help..

 

[Pat Hartman]

Hope this post comes of some help for someone

I hope it doesn't Storing duplicate data is poor practice since it violates second normal form.

I also forgot to tell you that the combos were using the wrong table as their RowSource. They were all using the booking table. Each combo should have been referring to its lookup table. I also think you're going to have trouble with the Appointment table. You presently have to add an entry for every slot of every day. This will get quite tedious. You should be able to create a generic slot table for a single day. Then as long as you know which days of the week you can make bookings for, you can generate the slot as necessary. You will also need a table of exceptions to handle holiday closings.

 

[swedish_mania]

It does store duplicate data, but i couldnt find a away around it to create a booking and have all the information i wanted to create a report. Im sure there is an easier and non-violation way to do this.

The Appointment table will be quite tedious (10 slots a day * 31 days in a month = 310 records per month approx*). I did have a piece of code to automate this but couldnt get it to work.

I also think you're going to have trouble with the Appointment table. You presently have to add an entry for every slot of every day. This will get quite tedious. You should be able to create a generic slot table for a single day. Then as long as you know which days of the week you can make bookings for, you can generate the slot as necessary. You will also need a table of exceptions to handle holiday closings.

Can you explain more about 'generic slot table' & 'table of exceptions'. Thanks Pat

 

[Pat Hartman]

To create a report you would create a query that joins the tables, just like I posted for the form. Then use the query as the RecordSource for the report. Having duplicated data leads to data integrity problems. It is better to fix the bookings table now rather than later.

Can you explain more about 'generic slot table' & 'table of exceptions'.

Rather than pre-creating slot records for a date, just have a table that slot records for a single non-date related day. That gives you the pattern you need. You can have a field that holds the date and a field that allows you to choose the slot for that day. You would need to change the bookings table because it would need to have a date column because that would no longer be coming from the appointment table.

 

[swedish_mania]

To create a report you would create a query that joins the tables, just like I posted for the form.

Cant the report be based on the same query used for the booking form? Its basically the same thing, i think.

I decided to take your advice and scrap the massive booking table it was in violation of normalisation and very bad practice. I have now got that working using the above code, i think i was using INNER when i should of been using RIGHT.

Rather than pre-creating slot records for a date, just have a table that slot records for a single non-date related day. That gives you the pattern you need.

Can you elaborate on this? Im not getting what you are saying.

Thanks for your help/advice, very much appreciated..

 

[Pat Hartman]

Cant the report be based on the same query used for the booking form? Its basically the same thing, i think.

If the requirements are the same, the form and report can use the same query. I find in my databases that I need separate queries because usually the form and the report queries will be getting their criteria from different places. Also, the query for the form should contain an order by clause if it selects more than one record but the query for the report should be unsorted. The report itself controls record order with the sorting and grouping properties. Having a sort in the query in the case of a report is redundant since the report will just resort the data based on its own criteria.

Can you elaborate on this? Im not getting what you are saying.

Not without spending way too much time on it. Think of the slot table as a template that shows you how each day's time needs to be allocated. You will end up with a booking table that incorporates the data currently contained in the Appointment table. You will use the slot table as the RowSource for a combo that you choose a slot from. The rowSource query will probably need criteria to exclude already populated slots for the day.

I really can't go into a lot of detail without studing your requirements. I don't know any of your business rules and I would just be guessing.