Solved Update Vehicle Table from Form field... (1 Viewer)

[Garren.Shannon]

Hey all,

Again, relearning Access after many years away. I am building a ridership database. I have the following sort of working. Each record is associated to a specific vehicle so that's a combo box. Selecting the bus ID pulls the current_OD from the table and puts it into the "Odometer at Depart". The driver enters the "Odometer at Return", fills out the rest of the report and saves the record in the Trip table. When he/she hits save record, I want the Return Odometer data for that trip to replace/update the current_OD in the vehicles database for the currently selected bus.

I think this is done via an Update Query but what I have crafted so far is not getting the job done.

 

[theDBguy]

Can you show us the UPDATE query you created?

 

[Garren.Shannon]

UPDATE Vehicles INNER JOIN Trip ON Vehicles.VehicleID = Trip.VehicleName SET Vehicles.CurrentOD = [OD_Return];

 

[Garren.Shannon]

I set this to run when the Save Button is clicked.

 

[Pat Hartman]

When he/she hits save record, I want the Return Odometer data for that trip to replace/update the current_OD in the vehicles database for the currently selected bus.

In a relational database, data should not be stored in more than one place. Even storing both the start and end readings for the odometer can be problematic if you ever make a mistake and enter records out of order. Since it does make future calculations easier and you do have validation options as the StartOD reading is copied from the most recent record, it is less of a problem than many other situations so I won't harp on it at this time although, you will later understand the normalized method as long as your code prevents inserting old records. However, storing the EndOD reading in a different table is wrong since you can easily find it.

Also, consistency is your friend and will prevent many errors going forward. Start with naming standards. Object and variable names should be made from ONLY the letters a - z, and A - Z, and the numbers 0-9, and the underscore "_". NEVER use embedded spaces or other special characters like the dash. PERIOD.

Then, apply consistency to how you format the names. If you prefix objet names with tbl, frm, rpt, etc. - be consistent. If you use CamelCase (my personal preference) rather than the_underscore - be consistent.

When you add a bound control to a form, Access will automatically use the bound column name as the Name property of the control which is fine. But if you drag a control from the menu and then bind it manually or use it unbound as you would for a button, YOU MUST immediately assign a rational Name property because text222 is meaningless as is Command32, etc. Next week txtLastName and cmdOpenRpt will have much more meaning as you read the code.

 

[Garren.Shannon]

I think I get what you mean. I had not thought about record entry out of order. That would be a problem.

So instead of writing out the return OD... I should calculate the difference between the start and return OD and increment the bus current OD by that amount. That way, it would be irrelevant what the enter order was. Would that be done with an update query?

If so how?

 

[CarlettoFed]

If the relationship between Trip and Vehicles is correct the query is correct, perhaps if you attach the file it is easier to show you how you should change for what Pat Hartman rightly said.

 

[Garren.Shannon]

Here it is... it has some sample data.

 

[Pat Hartman]

What is the definition of a trip? Does it never span midnight? Can it be longer than one day?
Why is RI not enforced for ALL relationships? Remember, consistency is your friend.

You need certain types of validation. For example, when you start a new trip for a vehicle, you need to determine if there is an incomplete previous trip. What do you want to happen?

What are the "count" fields? They look like a repeating group.
Wouldn't it be better to make the defaults for the numeric fields null rather than 0? That makes it easier to determine missing data since 0 is a valid value for a numeric field.

Using table level lookups leads to many problems once you start using queries and VBA. I recomend removing any and all table level lookups. Just use combos on forms to pick values. you don't have to look at ID numbers, you always see the names on a form. Users NEVER see queries and tables. Only YOU ever see queries and tables and if working with numbers is too hard for you, then you use queries that join the related tables and pick up the text value from the lookup field.

In a properly normalized schema, the Trip record would have only an end odometer reading (more reliable than keeping only start). Then to calculate the length, you would find the max date/time for the vehicle prior to the current record to pick up what should be the start date for calculating.

 

[Garren.Shannon]

90% of the trips are short, 2 or 3, 5 to 20 mile trips per bus. On occasion, our "Day Tripper" buses will take kids on a long excursion and sometimes over night. Count is the head count per student. Code is the type of trip - To/From, Field, Activities and Misc (maintenance runs). I still need to put in some cross checking like if the code is M, the counts can be null but if the code is TF, F or A, Base and Max must be > 0.

Good point on Nulls... yes... I will make that happen.

I should be able to get my head around calculating total mileage rather than storing a continuous update... but it seem like its just out of reach. I mean, the bus has it simple. It's just an incrementor ticking away. But collecting and keeping that information updated so we can report to the state keeps baffling me. My kneejerk reaction is to constantly update a digital odometer per bus.. just like the physical ones do.

I swear, I was not this thick 20 years ago.

 

[Pat Hartman]

If the trips are overnight, you need date as well as time.

If you want to store the start and stop, that wouldn't be terrible but you will need to add validation to ensure that nothing can be added in between. In the RowSource of the combo for the bus, add a dMax to get the highest odometer reading for the specific bus. Then in the click event of the combo, you can copy the odometer reading to the Start field.

Me.txtStartOD = Me.cboBusID.Column(2)

The columns are a zero based array so the assumption here is that the first field is the ID, the second is the bus name/number, the third is the odometer reading.

 

[CarlettoFed]

In the attached file you can see an example.

 

[Garren.Shannon]

Thanks CarlettoFed...