Minggu, 28 Februari 2016

Re: [MS_AccessPros] Getting required values on Sales Form



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

    Set rst = Nothing
    Set db = Nothing
    Exit Function

    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:
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



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)



Tidak ada komentar:

Posting Komentar