Create a form has header information and then data lines to be captured

[Peter Simpson]

Hi Guys



I need to create a form and a table that captures logbook data. I need the form to have header information such as Vehicle registration, Travel date, opening Km, closing Km ect that represent a reps details for the day. Below that I need to be able to have the days activity captured by line....something like this:

(Header of the form)
VehicleID
TravelDate
OpeningMilage
ClosingMilage

(Body of the form)
StartMilage
FinishMilage
MilageType (Business/Private)

I need to repeat each line in the body of the form for as many times as the logbook needs in other words if the rep made 5 business calls and 1 private call, I need to be able to capture 6 lines in the body of the form without changing the header information.

The second question is do I set up my table with all the above fields and then the form will add the multiple body lines?

[ArviLaanemets]

You need 2 tables (at least, but it's enough for start).

tblVehicles: VehicleID, OpeningMileage, ...
There you have all vehicle-bound info which doesn't change over time (when purchased, registry plate number, Mark, etc). NB. ClosingMileage doesn't qualify, and it will be better to calculate it whenever you need it.

tblTravels: TravelID, VehicleID, TravelDate, TravelType, TravelMileage, ...
NB! StartMileage can be calculated as SUM of mileages of all previous (TarvelDate less than current travel's TravelDate) travels + Opening Mileage. ClosingMileage can be calculated as StartingMileage + TravelMileage. So you don't need them in table.
You can consider correction entries into tblTravels (as fictitious travels of certain type), when calculated ClosingMileage doesn't match for some reason with real one by spidometer.

Then you need a single form based on tblVehicles (fVehicles), where you can register a vehicle, select a vehicle, edit it's data, etc.

Then you must create a continuous form based on tblTravels (fTravels). Hide fields TravelID and VehicleID on this form.

As last, you open the form fVehicles in edit mode, and drag the form fTravels into it. My advice is you rename the subform container created automatically and having same name as form you dragged into fVehicles p.e. as sfTravels.

When all went OK, the created subform displays all travels for vehicle selected in form fVehicles, and any new travels inserted into subform are automatically linked to this vehicle. When not, then you have to set subform's LinkMasterFields and LinkChildFields properties to VehicleID manually.

[Peter Simpson]

Hi Avril

I have been battling with this for a while now before coming back to you.

I think that I have created my tables correctly however when I test it and capture dummy data the two tables DO POPULATE however when I click on the small "plus" signs there is no data relating to the records.

The Form


Vehicle Table


LogBook Table

[aytee111]

Make sure you have no lookups on the table itself, the table must be clean and devoid of anything that can be done in queries. Otherwise you will have issues down the road.

Why are there two records on the VehicleDetail table for the same vehicle?

I am struggling to see the details of your form. The main form with record source VehicleDetail is one record at a time. There needs to be a combobox to select the vehicle and then filter to that record as well as a way of entering a new vehicle (usually a NEW button).

The subform with record source LogBook table needs to be a continuous form and linked to the main form with the VehicleID. Not sure if this is how you have it.

[ArviLaanemets]

Post your app packed as Zip in attachment to posting (click GoAdvanced below QuickReply to upload file)

[Missinglinq]

I think that I have created my tables correctly however when I test it and capture dummy data the two tables DO POPULATE however when I click on the small "plus" signs there is no data relating to the records.

Somewhat confused by your narrative, but if you're trying to view Records in the underlying Table, from your Form, and they're not visible, then in Design View, with the Form itself Properties - Data and see if the Data Entry Property is marked as Yes...if so, change this to No.

Despite its name, the Data Entry Property does not have to be set to Yes in order to enter data! In this mode you can only enter New Records...you cannot view existing Records.

Linq ;0)>