Form Calculation Input Question

[PhilG]

Hello,

I made a form which is called 'Flight_Log_Update'. The objective here is to be able to input new flight data to update the associated aircraft records.

Is it possible to be able to have some sort of input field in the form that allows you to type in, for example, '3.9' which would then take this value and append the [ACTimeCurrent] field in the 'Aircraft_Table' via a calculation?

I.e. [AcTimeCurrent] = 1,000 hrs
Input from form to update = 3.9 hrs
Output calculation of [ACTimeCurrent] = 1,003.9

I created a ComboBox in the Form Header of 'Flight_Log_Update' that allows you to pull down, by [NNumber], the recordset of the aircraft you wish to update. Now I need a way to enter '3.9' and have it calculate and append automatically.

The same would be needed for cycles; which will always be added as a whole number.

Thanks in advance!

 

[plog]

You could create a button that says 'Update', then you would code some VBA using the Do.Cmd RunSQL method to run an UPDATE query. The SQL for that query would look like this:

"UPDATE YourTableNameHere SET ACTimeCurrent=(ACTimeCurrent + " & Me!ControlNameOfValueToAdd & ");"

 

[PhilG]

And what sort of control would be used for the user to enter the value to be added? A TextBox? Thanks btw for the quick reply! Also, I have very limited VBA coding experience

 

[missinglinq]

Assuming that by pulling down 'the recordset of the aircraft' you mean that this is a Bound Form that uses 'Aircraft_Table' as its Record Source, and you're using the Combobox to retrieve the Record for the aircraft in question, you'll need an Unbound Textbox to enter the new hours (call it txtEnterHours) and place this code behind its AfterUpdate event:

Private Sub txtEnterHours_AfterUpdate()
  Me.ACTimeCurrent = Nz(Me.ACTimeCurrent, 0) + Me.txtEnterHours
End Sub

This is also assuming that you do not need to keep a separate record of each time the ACTimeCurrent is incremented. That would involve a completely separate Table and more complicated calculations, but you don't mention this, so I assume you only need to save the running total for ACTimeCurrent .

Linq ;0)>

 

[PhilG]

Well, that functionality would be great. Because, ideally the customers want to have an ActionLog which would show 'which user entered what and when to where'. So, yes, you are right! We are under pressure from the FAA right now and we need something that is going to track and update flight records and eventually parts as well. I was thinking I could add this incremental log of each record later. But, this maybe the wrong approach. Thanks again!

 

[missinglinq]

If you're going to want this functionality, now is the time to do it, not down the road. Doing it later would mean re-entering all of your flight hours data. And as I said, this will require a separate Table for the individual flight hours Records.

What you're describing here is the classic one-to-many scenario, with the 'one' side being the Aircraft and the 'many' side being the Flight Hours. This is usually handled with a Main Form/Subform set up.

In general terms the Main Form would be a Single View Form and based on a Aircraft_Table. The Subform would usually be a Datasheet View Form (to display all the 'flight hours' data) based on a ActionLogTable .

The Fields would typically be something like this:

Aircraft_Table
NNumber 'Primary Key
AircraftManufacturer
AircraftModel
ACTimeCurrent
...and so forth

ActionLogTable
NNumber 'Foreign Key
ActionLogID 'Primary Key
LoggerName
ActionDate
FlightHours
...and so forth

Once you've designed your Main Form and Action Log Form, based on Aircraft_Table and ActionLogTable, respectively, open the Main Form in Form Design View and add a Subform Control to it. The Subform Wizard will walk you through the process. When asked, base your Subform on the Action Log Form. Seeing that both Tables the Forms are based on have a Field named NNumber , the Wizard should ask if you want to link the Main Form /Subform using these Fields. Answer OK and Bob's your uncle!

As you move through the Aircraft Records in the Main Form, Access will automatically update the Action Log Records in the Subform and only show those Action Logs related to that Aircraft.

In Design View for the ActionLog Form, in the Footer, you would place an Unbound Textbox and set its Control Source to use the Sum() function to sum up the individual hours that have been entered.

On the Main Form you would then have an Unbound Textbox whose Control Source is set to equal the calculated total from your Sum() of the individual hours from the Subform.

You can keep your Combobox for pulling up the correct Aircraft Record in the Main Form. So, to enter new hours you would

• Go to the Main Form Record for the particular aircraft
• Enter a New Record in the Subform.

Everything else would be done automatically. The Subform Records will automatically have the NNumber from the Main Form Record entered, and this will always 'connect' the Records.

You'll notice that the 'totals' Textboxes on both the Main Form and Subform are Unbound. Calculated Values, rather than being Bound, which is to say stored in the Field of a Table, are simply automatically re-calculated, as needed. There are a few, specialized cases in which storing Calculated Values is acceptable, but this type of situation is not one of those. These kind of Calculations are always done quicker than the stored results can be retrieved.

As I said, if you're going to need this functionality, now is the time to do it! 'Retrofitting' is always way, way more complicated and time consuming than planning and implementing what you need from early days.

Good luck with your project, and remember, if you need help, that someone is almost always here!

Linq ;0)>

 

[PhilG]

Linq, you are > than great!

This is working great so far!

Now I can use this same setup to start tracking associated parts which have a consumption and life limit. The part has a life-span (either in hours, days, or cycles). The part can already have consumption on it when it is associated with the aircraft (again in either hours, days, or cycles). The part is associated to the calculated field we set up earlier in the header of the Aircraft Main Form (which used the =Sum() to add the incremental action log entries and provide a current running total of Aircraft Flight Time).

Any tips would be appreciated!

Part Life in Hours , Cycles, and Days
Part Consumption Before Install On AC In H, C, D (I.e. Consumption already on Part)

My thinking now is that I will create another Form and Table for parts. In the parts table I will have 'HoursBeforeInstall' , 'CyclesBeforeInstall', 'DaysBeforeInstall' , 'LifeLimitInHours' , 'LifeLimitInDays' , 'LifeLimitInCycles' , Foreign Key 'NNumber' , Primary Key 'PartActionLogID'. I will create a separate Part_Action_Log for this. Is this the right idea? Any ideas regarding the calculated fields I need to associate. I am using a =Sum() function calculated unbound text box on a Main-form/Sub-form setup to keep the total running aircraft time and cycles. Will I be able to use this in the Part Form I will create? Thanks!

 

[PhilG]

***********UPDATE***********

Okay, I am using this syntax as the control source for the unbound textbox which is supposed to display the calculated unbound textbox in the footer of the sub-form.

=Sum([ACTimeInitial]+[txtHourSum])

and

=Sum([ACCyclesInitial]+[txtCycleSum])

txtCycleSum and txtHourSum are the names of the of the unbound textboxes in the footer of the sub-form which calculates the current hours of the ac. You can see above what I am using for that control source. It is: =Sum([FlightHours]) and =Sum([FlightCycles]) , respectively.

Thanks for any help coming my way!