Applying Discount based on membership level - performing calculations in textbox.

[tim_tims33]

Hi,


I have an invoice form, where the data source for the invoice is:

MAIN FORM tblInvoice (InvoiceID, CustomerID,Date)
tblCustomer (Membership_Level)

Sub-FORM tblInvoice_Details (InvoiceID, StockID, Qty_Ordered)

If you look at the attached image, you will see taht i've created an Unbound Textbox (text34) in which i would like to show the discount rate as follows:

Bronze Member - 15%
Silver Member - 20%
Gold Member - 25%

The membership type is already present (as you can see in the textbox below text34).

THe issue is this: If a customer is selected in the main form, i would like text34 to be populated with the value 0.15... so then i can use it in the Invoice Total caclculation for applying discount.

I've tried the following under different events to try and get the value to appear, but it doesn't work.

[CODE][If Me.txtMember_.Value = "Bronze" Then Me.Text34.Value = 0.15/CODE]

I've tried BeforeUpdate and AfterUpdate....but i get no value appearing in text34.

[pbaldy]

Does this work for you?

http://www.baldyweb.com/Autofill.htm

[tim_tims33]

Unfortunately not. The discount rates are not stored anywhere, except as literals / constants in VBA... i hope.

[pbaldy]

Not dynamic, but your code should work. Can you post the db here?

[Demerit]

[CODE][If Me.txtMember_.Value = "Bronze" Then Me.Text34.Value = 0.15/CODE]

The code you posted above has an under score that should prevent it from working

Try this

 
If Me.txtMember.value = "Bronze" then
     Me.Text34.value = 0.15
Elseif Me.txtMember.value = "Silver" then
     Me.Text34.value = 0.2
Elseif Me.txtMember.value = "Gold" then
     Me.Text34.value = 0.25
End if

[June7]

VBA is required only if you want to save the percentage or the calculated value into table. Whether or not this should be done depends on your data structure.

[tim_tims33]

Still not working. I tried the VB code. no need to store data June... just want to show result of calculation in textbox (textbox34).

I've attached the DB. The form is frmInvoice. You will see under the calculations for the totals...what i'm trying to do.

BTW, the code doesn't throw up any runtime errors. Rather, i think, the issue is with the event... which event do i use to run the code.

I've tried InvoiceID_Change(), BeforeUpdate(), AfterUpdate().

Any help would be much appreciated. Thanks,tim

[pbaldy]

Can't look at the sample right now, but I don't think that's the right control. I'd use the after update event of the customer ID, the membership level, and/or perhaps the current event of the form.

[June7]

If you don't want to save the calculated value then why use code? Try IIf() or Switch() expression in textbox:

=Switch([txtMember]="Bronze", 0.15, [txtMember]="Silver", 0.2, [txtMember]="Gold", 0.25)

Or in query:

Switch([Membership_Level]="Bronze", 0.15, [Membership_Level]="Silver", 0.2, [Membership_Level="Gold", 0.25) AS Rate

Then refer to the constructed field in form or report.

[Demerit]

I am unable to open your database
Gives me a msg unrecognised database...