Newbie help with calculation of refund (1 Viewer)

[helper11]

Hi all,

I have the following code:

If refund Then
[refund_less_20percent] = ([refund_amount] - ([refund_amount] * 0.2))
Else
refund_less_20percent = 0
End If
If partial_refund Then
[price] = [price] - [refund_amount]
End If

The refund and partial refund are buttons on the form, and the result is save to the database for future reports and the price field is the price amount in the database. I am trying to select a partial button and entering a dollar amount it is then to remove that dollar amount from the price field and write that to the database over the old price amount.

I would also like to update the record on the screen with the new price, is that possible if so any ideas on that would help.

Thank you for any help on this..

 

[John Big Booty]

Why not simply record The item purchased the number of items purchased and the discount (when applicable) percentage, and then do your calculation on your Form, and Report/Invoice?

From here;

Calculated Fields

How do you get Access to store the result of a calculation?

For example, if you have fields named Quantity and UnitPrice, how do you get Access to write Quantity * UnitPrice to another field called Amount?

The best answer is, "Don't!"
Calculated fields belong in queries, not tables.

Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.

Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.

So, how do you get the calculated field if you do not store it in a table? Use a query:

1. Create a query based on your table.
2. Type your expression into the Field row of the query design grid:
Amount: [Quantity] * [UnitPrice]

This creates a field named Amount. Any form or report based on this query treats the calculated field like any other, so you can easily sum the results. It is simple, efficient, and foolproof.

....Continues in link above

 

[helper11]

Hello,

The reason is the items are never really entered, the way the database is setup is it reads in a text file and adds the items to a table. Then I want to be able to enter partial and full discounts as needed, that is what is being done on the form, as it finds the record with a search and then I chose full or partial refund and the enter a refund amount. I then what it to do the code I entered.

Thank you for helping...

 

[John Big Booty]

On your form you could have an option group with a set of radio buttons that indicate the type of discount you wish to apply and an unbound field to input the level of discount to apply.

Then in the On Click event of a button put your code.

If Me.FrameName = (result indicating Refund) Then
     [refund_less_20percent] = ([refund_amount] - ([refund_amount] * 0.2))
Else If Me.FrameName = (result indicating Partial Refund) then
     [price] = [price] - [refund_amount]
Else
     refund_less_20percent = 0
End If

However I'm still not convinced this is the way to go.

 

[helper11]

Hello,

The refund_amount field is the one that is holding the value. The way it works is in the header area of the form I have the user enters the sku for the search this will bring up the record in a detail area.

Also, in the header area I have 2 buttons, the refund and partial_refund and the a field to enter an amount. The refund for the full amount works as all this does is write mark the refund button as a yes and stores the full refund amount in the table. The problem is I can not get the partial refund to reduce the price in stored amount in the table, that is the ultimate goal of this.

Thank you for the ideas...

 

[John Big Booty]

[price] = [price] - [refund_amount]

Should do what you want.

You don't have a Sub Form involved do you?

 

[helper11]

Hello,

I am not sure on the subform. I do the code as you see but it will not write the new price amount to the main table over the old price amount. It might be a subform, as I am not sure of the terms.

Thanks again for the info...

[price] = [price] - [refund_amount]

Should do what you want.

You don't have a Sub Form involved do you?

 

[John Big Booty]

If one of those controls in on a main form and the other on a Sub form, how you reference the the controls will be dependent upon which of the forms the Command button is on.

 

[helper11]

Hello,

I am getting confused. I am sending the code that is used of a top form that uses an unbound combo box to look up the field. I found this on another site and can not take credit for it as I do not understand what it is fully doing:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[sku] = '" & Me![Combo22] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I hope this can help you better understand what I am doing or trying to do... If you want I can try and send a image of the form.

I really do appreciate the help. I have seen things using sql and other coding like the one above but no idea what it is doing, do you know of a site that explains some of this?

Thanks again..

 

[helper11]

Thanks a ton for all the help, you wont believe what the problem was, I had the partial_refund in the table as the last item, once I moved this up in the location in the table it worked.

 

[helper11]

How would I update the view on the form after it does the calculation for a partial refund?