Solved Sub form only shows ID's not names...

[Garren.Shannon]

Hey all,

I thought I posted this last night but I can't find it here so my apologies up front if this is a dupe.

My first foray into Sub-forms so bear with me. I have a form (Frm_BusSelection_Review) tied to the Vehicle table. the subform is Trip Subform (Image attached along with the relationships) crafted by the wizard. Trip Subform is tied to the select command noted below. The form works but I need it to do several things for me.

1. I would prefer to select the vehicle (combo box?) rather than have to scroll through using nav buttons.
2. The sub form does not pull the driver name (from the drivers table) or the code (from the Trans-type table.

What's the magic to making a dynamic form and subform?

On a side-note - I am a member of many other forums (not related to programming) and of the dozens or so, I can count 2 that are great resources with helpful, ethical members... this forum is one of the two. Really nice to hear from consummate professionals.

---------------

SELECT [Trip].[Trip Number], [Trip].[VehicleID], [Trip].[T-Date], [Trip].[DriverID], [Trip].[CodeID], [Trip].[Pre-Insp], [Trip].[Post-Insp], [Trip].[DepartTime], [Trip].[ReturnTime], [Trip].[OD_Depart], [Trip].[OD_Return], [Trip].[Basic-Count], [Trip].[Sped-Count], [Trip].[HS-Count], [Trip].[Walk-Count], [Trip].[MaxCount], [Trip].[Desc] FROM Trip;

 

[Gasman]

Are you using table lookups?, sounds like you might well be?

 

[Garren.Shannon]

I have the bus select form doing a simple view of the Vehicle table. If I remove the sub form, you just see the vehicle records and use the nav buttons to move through each record to no lookups. I initially tried to do a combo box lookup where I select the bus I want to review but then the sub-form would not refresh for the selected bus.

I know I am missing something here. On the control form, I should be able to chose a bus (combo box) and set a date range (like the past two weeks), then review the trip data for that bus in the subform. I just don't know how to build that query or subform to act correctly.

 

[The_Doc_Man]

Just to save you some time and some fat fingers, you can reduce your typing a bit in the SQL you showed us.

SELECT [Trip Number], VehicleID, [T-Date], DriverID, CodeID, [Pre-Insp], [Post-Insp], DepartTime, ReturnTime, OD_Depart, OD_Return, [Basic-Count], [Sped-Count], [HS-Count], [Walk-Count], MaxCount, Desc FROM Trip;

First, all of those [Trip] qualifiers are not needed because you have only one listed table in the FROM clause. Access will automatically presume that all named fields come from table Trip. This shortcut is not available when you have multi-table JOINs in play, however.

Second, for fields that do not contain spaces or dashes in the name, you can omit the brackets. In specific, OD_Depart and OD_Return, with underscores, don't need the bracket because (believe it or not), in Access the underscore is treated more like an alphabetic than a special character.

Third, "2. The sub form does not pull the driver name (from the drivers table) or the code (from the Trans-type table."

If you have controls that are NOT in a continuous form, you can use DLookup to extract these values OR you can build a JOIN that does the lookup for you. For a continuous form, you will probably need to go the JOIN way.

 

[arnelgp]

1. I would prefer to select the vehicle (combo box?) rather than have to scroll through using nav buttons.
2. The sub form does not pull the driver name (from the drivers table) or the code (from the Trans-type table.

you need to modify your Subform.
on design view of your subform, right-click on DriverID, on shortcutmenu, select Change To->Combobox.
on the Property Sheet->Data->Row Source:

select DriverID, FName & " " & LName As Name From Drivers;

still on Property->Format:

Column Count: 2
Column Widths: 0";1"
List Width: 1"

// do the same to CodeID textbox, only this time use:

Select VehicleID, VehicleName From Vehicles; (Row Source)
and same (Property->Format) as with DriverID textbox.

 

[Garren.Shannon]

Thanks all... that did the trick.