Khalid-
It is difficult to understand from your code what is happening. I assume some of the variables are actually controls on the form. (Perhaps the ones where you append .Value?) You should always reference form controls and fields using Me:
Me.SaleCost6pc = …
The one problem I see with your code is you have coded all sorts of numeric constants to perform the calculations. It would be better to fetch these values from a table. If you need these percentages elsewhere in your application, you could consider making the values Public variables in a Standard module, then fetch the record containing the current percentages and set the Public variables when your app starts. Because Public variables will often reset if your code has an untrapped error, you could write a "wrapper" function that you call each time you want to make sure the variables are set. If any of the variables are not zero, then you don't have to fetch the record again. Here's just an example using a variable called gSixPercent:
Option Compare Database
Option Explicit
Public gSixPercent As Double, gGST17Pc As Double
Public Function GetVariables() As Integer
Dim db As DAO.Database, rst As DAO.Recordset
' Set an error trap
On Error GoTo ErrTrap
' If the variable isn't zero - nothing to do
If gSixPercent <> 0 Then Exit Sub
' Point to this database
Set db = CurrentDb
' Get the row containing the variable values
Set rst = db.OpenRecordset("SELECT * FROM ztblVariables")
gSixPercent = rst!SixPercent
gGST17Pc = rst!GST17Pc
' … etc for as many variables as you need
' Return all variables fetched OK
GetVariables = True
Done:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
ErrTrap:
MsgBox "Unexpected Error: " & Err & ", " & Error
' Return failure
GetVariables = False
Resume Done
End Function
As for whether your code should be used on the Purchases form, I have no clue. What is the layout of your tables?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 28, 2016, at 9:03 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John,
I am working on my new Database "QA"
There are lots of questions i have to ask and I hope as you helped me and guided in the past
till now, you will help me here too. The client for whom i am developing this database is
pressing me hard to complete it urgently.
This is basically for a whole sale dealer's shop for "Battries, UPS and related items to them".
I have made "ItemFile" for items
ItemCode----> PK
For Purchase i have imported Form "Orders" from Northwind database and its relevant queries etc;
and modified/renamed to my requirements, and named it Form "Purchases" which is working OK.
For Sale later on and this is little complicated. On the Form "Item File" on Text box "ItemCost"
After Update event i am calling Function "SaleRates" and inserting values from this function on
the form. (I will paste Function SaleRates() at the end).
Now i am not sure my this logic is appropriate to bring values on this form or not,
but any how the values are coming correct. The reason for which i am not sure is that further
i have to use these values on Form "Sales" and i am not getting any idea to use them there.
The requirement on Form "Sales" will be to get "Sales Date" as =Now(), Credit Note as (Autonumber)
entering "ItemCode" getting "ItemDescription" from combo box "ItemCode" then "SaleQty" then asking
user to select:
SaleCostRetail
Registered whole Seller
UnRegistered whole Seller
on selection i want to get value, which i have defined in my Function SaleRates()
This would be SaleCost of item and get TotalSaleCost of that item.
Should i Save as my Form "Purchases" to Form "Sales" and modify it, if so ? how can i get SaleCost
for the selection used.
Please guide and help. I am pasting below Function SaleRates(), also is it correct to use this Function
OR there is some other better way for this.
------------------
Function SaleRates()
'Calculating 'SaleCostRetail'
Dim VarItemCost6pc As Integer ' pc used to abbrv. % (percent)
VarItemCost6pc = ItemCost.Value * 0.06
SaleCost6pc.Value = ItemCost.Value - VarItemCost6pc
'--------------------
Dim VarGST17pc As Integer
VarGST17pc = SaleCost6pc.Value * 0.17
GST17pc.Value = VarGST17pc
'---------------------
Dim VarTax2pc As Integer
VarTax2pc = SaleCost6pc.Value * 0.02
Tax2pc.Value = VarTax2pc
'---------------------
SaleCostRetail.Value = ItemCost.Value + GST17pc.Value + Tax2pc.Value + Misc.Value
'====================================
'Calculating discounted rates for Registered whole Seller
'Calculating 'DiscountRegistered2pc'
Dim Var2pcItemCost As Integer
Var2pcItemCost = ItemCost.Value * 0.02
'---------------------
DiscountRegistered2pc.Value = ItemCost - Var2pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating 'DiscountRegistered3pc'
Dim Var3pcItemCost As Integer
Var3pcItemCost = ItemCost.Value * 0.03
'---------------------
DiscountRegistered3pc.Value = ItemCost - Var3pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating 'DiscountRegistered4pc'
Dim Var4pcItemCost As Integer
Var4pcItemCost = ItemCost.Value * 0.04
'---------------------
DiscountRegistered4pc.Value = ItemCost - Var4pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating 'DiscountRegistered5pc'
Dim Var5pcItemCost As Integer
Var5pcItemCost = ItemCost.Value * 0.05
'---------------------
DiscountRegistered5pc.Value = ItemCost - Var5pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating 'DiscountRegistered6pc'
Dim Var6pcItemCost As Integer
Var6pcItemCost = ItemCost.Value * 0.06
'---------------------
DiscountRegistered6pc.Value = ItemCost - Var6pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating 'DiscountRegistered7pc'
Dim Var7pcItemCost As Integer
Var7pcItemCost = ItemCost.Value * 0.07
'---------------------
DiscountRegistered7pc.Value = ItemCost - Var7pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating 'DiscountRegistered8pc'
Dim Var8pcItemCost As Integer
Var8pcItemCost = ItemCost.Value * 0.08
'---------------------
DiscountRegistered8pc.Value = ItemCost - Var8pcItemCost + GST17pc.Value + Tax2pc.Value
'====================================
'Calculating discounted rates for Un-Registered whole Seller
'Calculating 'Discount Un-Registered2pc'
Dim VarExtraTax2pc As Integer
VarExtraTax2pc = SaleCost6pc.Value * 0.02
ExtraTax2pc.Value = VarExtraTax2pc
'---------------------
DiscountUnRegistered2pc.Value = ItemCost - Var2pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
'Calculating 'Discount Un-Registered3pc'
DiscountUnRegistered3pc.Value = ItemCost - Var3pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
'Calculating 'Discount Un-Registered4pc'
DiscountUnRegistered4pc.Value = ItemCost - Var4pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
'Calculating 'Discount Un-Registered5pc'
DiscountUnRegistered5pc.Value = ItemCost - Var5pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
'Calculating 'Discount Un-Registered6pc'
DiscountUnRegistered6pc.Value = ItemCost - Var6pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
'Calculating 'Discount Un-Registered7pc'
DiscountUnRegistered7pc.Value = ItemCost - Var7pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
'Calculating 'Discount Un-Registered8pc'
DiscountUnRegistered8pc.Value = ItemCost - Var8pcItemCost + GST17pc.Value + Tax2pc.Value + ExtraTax2pc.Value
'====================================
End Function
Regards,
Khalid
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
