Form - multiple tables/queries Help!

[Mycle]

I want to use a DB to manage Caravan Towing Courses.

Tables:

tbl_CourseParticipants (ParticipantID, CourseID, ParticipantName, ParticipantDetails)
tbl_Courses (CourseID,CourseBeginDate, CourseEndDate, CourseParticipantName)
tbl_TowVehicle (VehID, VehMake, VehModel, VehGVM, VehGCM)
tbl_Caravans (VanID, VanMake, VanModel, VanLength, VanAxles, VanATM)

I want to have a form with a list box populated using the CourseParticipantName and CourseID which then fills text boxes on the form (txt_CourseBeginDate), (txt_TowVehdetails), (txt_VanDetails).

I could do this when I used Access using an after-update event. How the heck can I achieve this using BASE??

Here’s hoping there is a simple enough way for me to understand.

Mike

[Ratslinger]

Hello,

Unfortunately, with the information provided, there is no way to answer this question. First, populating the list box with the information is not any problem. Here are the actual problems.

• You want to fill in a ‘CourseBeginDate’ but where/how is this date obtained?

• You want to fill in ‘TowVehdetails’ & ‘VanDetails’ (guessing all the the last two tables) but how is this info obtained?

• There seems to be no relation of the last two tables with any other table.

• Don’t understand why you have participant name in Courses table when the Participants table already has a link to the courses via the ID?

Please provide more details on the expected form processing.

[Mycle]

Thank you @Ratslinger. This was a quick outline of the tables and I omitted the fact that there are relationships between the tables. tbl_CourseParticipants also includes VehID and VanID (Integer) fields.

tbl_CourseParticipants (ParticipantID, CourseID, VehID, VanID, ParticipantName, ParticipantDetails)

Maybe I will need need a multi table query. I am hoping to use a form Listbox to select Participants which will then populate some textboxes in an outline named Vehicle Details. This should contain Three textboxes. One textbox with “VehMake” || ’ ’ || “VehModel” one for VehGVM and one for VehGCM.

And the same for the Caravan details. In an outline named Caravan Details A textbox for VanMake || ’ ’ || VanModel, another for VanLength, one for VanAxles and one for VanATM

I really hope this helps.

Mike

[Ratslinger]

@Mycle Please do not use an answer for a response - only to answer original question. Instead use add a comment or edit original question.

[Ratslinger]

@Mycle Please do not use an answer for a response - only to answer original question. Instead use add a comment or edit original question.

[Ratslinger]

Hello,

After reading & re-reading your question & addendum this is all about piecing records together to be able to display information about a participant in a course (training class may be better?). ‘Caravan’, I believe is a trailer.

It would appear you need to piece these records together using SQL. Doing this in SQL requires using JOIN in the statement. For information on what Join does in a statement see → SQL Joins.

Now in this statement you need to know what specific item you are accessing. This is obtained from the list box. The list box is used in conjunction with a filter table (one record table to hold temporary values). The SQL statement would then use this value in the ‘where’ portion of the statement to get the specific item wanted. The result can be then displayed on a form.

Here is a link to what table filtering is all about → Filter/Search with Forms (leveraging SubForms).

You can see a sample of table filtering in my answer on this post → Filtering data without an AutoValue in the table

For a sample of an SQL join see this post → HSQL code logic

[Mycle]

Yes Caravan in this country = Trailer

Thanks @Ratslinger, more reading and learning required. Had limited time today, and rethought the DB. I now have CourseID, VehID, and VanIS fields in the Participants table. Not knowing where to start resulted in a scattergun approach Thanks again, will let you know how I get on after studying Join statements etc.

Mike