Re: May be a little sophisticated for me, but should work!
- From: "paii, Ron" <paii@xxxxxxxx>
- Date: Tue, 10 Jul 2007 14:51:26 -0500
#Error is likely caused by NULL in the percentage field, use NZ() to convert
to 0. The #Error could also be caused by division by 0 or some other error
in your function.
"sara" <saraqpost@xxxxxxxxx> wrote in message
I have a system where the Merchants (employees) buy goods and when
they enter the item into the database, they have to tell us how to
distribute the goods amongst our 14 stores.
A few of the more sophisticated merchants have a default distribution,
by percentage to each store (Store Y = 10%; Store P = 4% - that sort).
which I allow them to enter and I store it and allow them to modify
the percents - as long as it always adds up to 100.
They select an item from the list box and click "Distribution" and the
Item Distribution form opens. IF they have percentages on file, I
show them the percents and calculate the distribution. This works.
IF they don't have percents, I want to just show the Quantity boxes
(no percents) and they enter the quantities. This doesn't work. All
the fields show as #Error.
If they have percents, but want to modify the distribution with a
manual entry (override), I allow that. This doesn't really work.
Sort of works.
1. Why do I get #Error in all fields when the merchant doesn't have
2. Do I need a "Distribution Type" identifier on the record? If they
distribute by percent, it would be "P" and if it's overridden, or
they don't have percents, it's "M" (or something)? I'm thinking that
part of my problem might be the sophistication of switching between
manual and percents - maybe I am not advanced enough to do it; maybe I
need the identifier to tell me what to show/calc in the code?
Code below (though I can post more if needed, or email the DB if
********** User clicks "Distribution" for an item in the list box of
Private Sub cmdDistribution_Click()
On Error GoTo Err_cmdDistribution_Click
Dim strDocName As String
Dim strLinkCriteria As String
Dim lngPOItemsKey As Long
Dim lngPOKey As Long
Dim intCount As Integer
' First check and save the record (same code as adding another)
If fcncmdAddAnother() = False Then
If IsNull(Me.lstItemsOnPO) Then
MsgBox "Please select an item for distribution", , "PO Items -
Select for Distribution"
' Get data and open form
strDocName = "frmItemDistribution"
lngPOItemsKey = Me.lstItemsOnPO.Column(1)
lngPOKey = Me.txtPOKey
strLinkCriteria = "POItemsKey = " & lngPOItemsKey
' Look up to see if the distribution record exists to figure out how
to open the form
' Look up now, since link Criteria is set as needed for Dcount
intCount = DCount("PODistributionKey", "tblPODistribution",
strLinkCriteria = "POKey = " & lngPOKey & " AND " &
' Open the form in ADD mode if there is no record, otherwise in Edit
' Check to see if the record exists. If it does, open the form in
' if it does not, open the form in Add mode
If intCount = 0 Then
DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormAdd
DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormEdit
MsgBox Err.Number & " " & Err.Description, , "PO Items -
****** Distribution form Open: ***********
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
' If the merchant has default percentages, put the percentages in
' ???*** Otherwise?? leave all blank?
Dim intCount As Integer
Dim lngMerchKey As Long
Dim txtStore As String
Dim dblYPct As Double
Dim dblBPct As Double
Dim dblNPct As Double
Dim dblLPct As Double
Dim dblKPct As Double
Dim dblVPct As Double
Dim dblDPct As Double
Dim dblPPct As Double
Dim dblRPct As Double
Dim dblAPct As Double
Dim dblOPct As Double
Dim dblTPct As Double
Dim dblGPct As Double
Dim dblMPct As Double
Dim lngNumPurchased As Long
' If merchant has no default pcts, open with quantity showing;
' otherwise calc quantity with the percents
lngMerchKey = Me.txtMerchKey
intCount = DCount("DefaultDistribPctsKey",
"MerchKey = " & lngMerchKey)
If intCount = 0 Then
Me.fraQuantity = 2
dblYPct = Me.txtYPct
dblBPct = Me.txtBPct
dblNPct = Me.txtNPct
dblLPct = Me.txtLPct
dblKPct = Me.txtKPct
dblVPct = Me.txtVPct
dblDPct = Me.txtDPct
dblPPct = Me.txtPPct
dblRPct = Me.txtRPct
dblAPct = Me.txtAPct
dblOPct = Me.txtOPct
dblTPct = Me.txtTPct
dblGPct = Me.txtGPct
dblMPct = Me.txtYPct
lngNumPurchased = Me.txtNumPurchased
MsgBox Err.Number & " " & Err.Description & "Call Sara.", , "Open
Distib Form Error - " _
***** Values and Default values in fields (Each field has its own
Y (label) source: Y, default: =Nz([txtYQty],0)
txtYPct: Unbound, default: =fcnGetDefaultPct("Y",[txtMerchKey])
FcnGetDefaultPct: fcnGetDefaultPct = Nz(DLookup (txtStore,
"tblDefaultDistribPcts", "MerchKey = " _
& lngMerchKey), 0)
txtYQty: Unbound, default: =IIf([txtY]>0,[txtY],IIf([txtYPct]>0,
Can anyone help with this? It's a tough one to post!
- Prev by Date: Re: Unzip a file
- Next by Date: Re: May be a little sophisticated for me, but should work!
- Previous by thread: May be a little sophisticated for me, but should work!
- Next by thread: Re: May be a little sophisticated for me, but should work!