Help Needed Please

[Blue 08 R6]

Form not saving all data to Table.

Hi Everyone,

I am fairly new to Access and have a couple of questions. If you could help me solve the issues I am having it would be greatly appreciated.

Currently I am developing a Database that keeps track of the Employee flight & accommodation bookings.

I have a form for accommodation and depending on what site is selected in the combo box the transaction amount auto fills with the correct cost, (this is referenced from a column in the combo box itself).

The formula I am using is inside the Transaction Amount Text Box on the form is =[Camp Location].[column](1)

The total days are calulated using =DateDiff("d",[Date In],[Date Out]) in the total days text box.

And the Actual amount is calculated using =[Camp Location].[column](1)*[Total Days]



Now the issue i am having is the amount that is auto filled in the total days, transaction amount and actual amount is not being saved in the table. (it shows on the table at the bottom of my form ok though)

I have added a picture so you can see what I mean.


Any help would be greatly appreciated.

Thanks in advance.

Adam

 

[Galaxiom]

The calculated fields are not bound to the table so do not save. Set their control source as the appropriate field from the Record Source of the form.

Use the combobox After Update Event procedure to load a value into the transaction amount textbox. Similarly with the days textbox based on the in and out dates.

You should not save the total amount in the table, only the days and cost per day. These two figures are multiplied when the total is required. Otherwise you are storing derived information which is a breach of normalization.

 

[Blue 08 R6]

Thank you for taking the time to reply.

Unfortunately I am a little confused with regards to "Set their control source as the appropriate field from the Record Source of the form"

Could you explain this for me please.

I already have an after update event procedure in place.

Thank you

Adam

 

[Galaxiom]

Control Source property is the top one under the Data tab of the properties window of the control. (Right click on the form and select properties to show it if you have not already found the properties window.)

The Control Source is actually what you see typed in the box in design mode so you can also type it straight in there as you have done. A formula entered here "binds" the control to values to a calculation on the fields and controls named in the expression.

Instead of the formula enter the field name from the Record Source. This 'binds" the control to the field in the Record Source.
(The Record Source is the table or query used to build the form. It is the top property in the data tab when the focus is on the form.)

It is easier to set a control source in the property window as this includes a dropdown to select the field. It also has query and expression builder facilities.