Append Unbound Form data and calculated

[optionone]

Hi,

Sorry still rather new to access and hitting brick walls searching for answers - created a form that has some calculated fields (from a combo box choice) and some unbound fields - how do i select some of these fields and append them to a table?

Many thanks

 

[vbaInet]

Calculated values should not be stored in tables:

http://allenbrowne.com/casu-14.html

 

[optionone]

Calculated values should not be stored in tables:

http://allenbrowne.com/casu-14.html

That link has confused me - I dont understand what is wrong with using the calculated fields and then storing them in to a table. I dont want to store the calculation with the append - just the value it produces - so it would just be a snapshot. The source table only contains part of the info needed for the calculation as well - the other part (the 'amount') coming from the data entry on the form

Essentially I am just using the form to allow the end user to enter a number in abox and show them what it is producing as a end total before its added to the table

Even if i create queries instead how would i best go about storing the unbound fields?

Thanks again

 

[vbaInet]

My comment and the link didn't mention you shouldn't store the calculations, it talks about not storing the value derived from the calculation. For everytime you want a value, let the calculation produce the result for you. For everytime you need to see the calculation on a different form, copy the calculation into that form and let it perform the calculation for you. Just don't store the values.

Unless your situation is different. Explain a bit more. Your form allows your users to perform some calculations based on what they enter, they could enter many calculations and then they choose one value that they like which should then be stored?

 

[optionone]

My comment and the link didn't mention you shouldn't store the calculations, it talks about not storing the value derived from the calculation. For everytime you want a value, let the calculation produce the result for you. For everytime you need to see the calculation on a different form, copy the calculation into that form and let it perform the calculation for you. Just don't store the values.

Unless your situation is different. Explain a bit more. Your form allows your users to perform some calculations based on what they enter, they could enter many calculations and then they choose one value that they like which should then be stored?

There is a refrence table that has product description, packsize and cost per unit - this is linked in on the form through a combo box. At the moment some other fields on the form are populated through that combo box using columns and control source.

The 'amount' entered in to the form by the end user will be the amount ordered that month so will be fixed - the calculation is just to show the end cost - the form to display all the info that will be appended so that there is confidence the right info is being appended

Am i going about this the wrong way? Found a link that shows how to use vb code with strSQL to insert or could use an external append query?

Thanks

 

[vbaInet]

You mentioned that "the amount entered into the form will be the amount ordered" for that month. Shouldn't that figure be calculated (using Sum) in a query based on that month as criteria? Don't you think there will be discrepancies in the amounts?

Yes you could use vba to Update a record but I don't think you're doing things properly.

 

[optionone]

You mentioned that "the amount entered into the form will be the amount ordered" for that month. Shouldn't that figure be calculated (using Sum) in a query based on that month as criteria? Don't you think there will be discrepancies in the amounts?

Yes you could use vba to Update a record but I don't think you're doing things properly.

Sorry I am even more lost now - I dont get how there can be discrepancies if the end-user is the only one who knows the 'amount' ordered and this is multiplied by the fixed cost per unit amount in the refrence table. The cost per unit will stay fixed for the financial year too

I guess there could only be discrepancies if the end-user wasnt trusted but what they input goes as no one else knows if its right or wrong

 

[vbaInet]

A discrepancy can be caused by human error - duplicate entries, spelling mistakes the same item could be entered twice (or more) thereby increasing the total for that month. However, in your case it might be a good way to check the total figure inputted against the total generated by the query to ensure they tally. Think about it.

To update a record using vba:

Currentdb.Execute "UPDATE tableName SET NameOfField = " & txtbox1.Value & " WHERE ID = " & Me![ID] & ";",dbfailonerror

 

[optionone]

Thanks vbaInet - appreciate the help

 

[vbaInet]

Have you got it working?