Re: May be a little sophisticated for me, but should work!

#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
news:1184095291.537168.31070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.

My questions:

1. Why do I get #Error in all fields when the merchant doesn't have
percents?

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
needed).

********** User clicks "Distribution" for an item in the list box of
items: **********
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
GoTo Exit_cmdDistribution_Click
End If

If IsNull(Me.lstItemsOnPO) Then
MsgBox "Please select an item for distribution", , "PO Items -
Select for Distribution"
GoTo Exit_cmdDistribution_Click
End If

' 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)

strLinkCriteria = "POKey = " & lngPOKey & " AND " &
strLinkCriteria

' Open the form in ADD mode if there is no record, otherwise in Edit
mode
' Check to see if the record exists. If it does, open the form in
Edit mode
' if it does not, open the form in Add mode

If intCount = 0 Then
DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormAdd
Else
DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormEdit
End If

Exit_cmdDistribution_Click:
Exit Sub

Err_cmdDistribution_Click:
MsgBox Err.Number & " " & Err.Description, , "PO Items -
Distribution"
Resume Exit_cmdDistribution_Click

End Sub


****** 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
the box
' ???*** 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",
"tblDefaultDistribPcts", _
"MerchKey = " & lngMerchKey)

If intCount = 0 Then
Me.fraQuantity = 2
fraQuantity_AfterUpdate
GoTo Exit_Form_Open
End If

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


Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & " " & Err.Description & "Call Sara.", , "Open
Distib Form Error - " _
& Me.Name
Resume Exit_Form_Open


End Sub

***** Values and Default values in fields (Each field has its own
"store Letter")

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,
([txtYPct]*[txtNumPurchased]),0))

Can anyone help with this? It's a tough one to post!

Many thanks,
Sara



.

Relevant Pages