relationship between quantity and stock

[Tia]

Hi

I am reasonably new to database, I am setting up a database and I need a relationship between the quantity field on the customer order table, and the stock field on the stock table. I want stock for a particular item to be reduced auto to match a quantity ordered....simply when quantity record says 5 ordered I want the stock to go down by the same amount.

 

[pbaldy]

Generally speaking it's better to calculate stock on hand from transactions than to try to keep a field updated. To keep a field updated, you will need to handle every possible change. Handling a sale is the easy part. What do you do when the user goes back and edits a sale? Deletes a line item? It is a lot of work and hard to catch every possibility. An often posted link is:

http://allenbrowne.com/AppInventory.html

 

[Tia]

Hi

Yeah I didn't think about that, being new to database I have also never dealt with DSum, can you give me any hints on this please, and basically how it works.

Thanks so much

 

[pbaldy]

Here's a good link on the syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[gemma-the-husky]

Hi

I am reasonably new to database, I am setting up a database and I need a relationship between the quantity field on the customer order table, and the stock field on the stock table. I want stock for a particular item to be reduced auto to match a quantity ordered....simply when quantity record says 5 ordered I want the stock to go down by the same amount.

further to the above - you even have the potential problem of deciding what to do in the event of stock outs.

ie - you order 5, but you only have 3 in stock. so what are you going to do now, as there are several options.

 

[accessfleet]

Welcome TIA:
I have a program with a parts table, a units table and a workorder table.

the is a y/n field to mark a work order as closed.

the on click proceedure below indicates how my inventory is updated and a bunch of other stuff. a lot happens when the box is checked.

good luck with your project.




Private Sub CLOSED_WORKORDER_Click()
Dim pid, mid1, updated
pid = DLookup("[PETROVENDKEY]", "UNITS", "[UNIT_#]=" & [Unit])
mid1 = Val(DLookup("[odometer]", "card", "[CardNumber] = '" & pid & "'"))
updated = False

If Not (Me.CLOSED_WORKORDER) Then
MsgBox "Cannot re-open a work order"
Me.CLOSED_WORKORDER = True
Else
If MsgBox("Close Workorder? This action cannot be undone", vbCritical + vbYesNo) = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "q_UpdatePartsInventory"
DoCmd.OpenQuery "q_update_partshistory_from_workorder"
If [work type] = 4 Then
DoCmd.OpenQuery ("q_update_adl")
End If

If [work type] = 6 Then
DoCmd.OpenQuery ("q_update_cdl")
End If
If [work type] = 7 Then
DoCmd.OpenQuery ("q_update_ddl")
End If
If Not IsNull([miles]) Then
If [miles] > mid1 Then
updated = True
DoCmd.OpenQuery "q_UpdatemeterMiles"
End If
End If

If Not IsNull([hours]) Then

'If [hours] > mid1 Then
updated = True
DoCmd.OpenQuery "q_Updatemeterhour"

'End If
End If



If Not updated Then

MsgBox ("Check meters and manually adjust {UNITS} miles or hours fields - Work order # : " & [Workorder_number] & " Unit : " & [Unit])
End If
If MsgBox("Close Workorder and adjust units / parts totals?", vbCritical + vbYesNo) = vbYes Then
DoCmd.OpenQuery "q_UpdateUnitslaborandparts"
End If

INSERT INTO part_history ( PartID, [Workorder number], RadioUnit, CloseWorkOrderDate )
SELECT wo_parts.PartID, WORKORDERS.[Workorder number], WORKORDERS.RadioNo, Date() AS Expr1
FROM WORKORDERS INNER JOIN wo_parts ON WORKORDERS.[Workorder number] = wo_parts.WorkOrderNumber
WHERE (((WORKORDERS.[Workorder number])=[Forms]![WORKORDERS]![Workorder number]));


DoCmd.SetWarnings True
Else
Me.CLOSED_WORKORDER = False
End If
End If

End Sub
Private Sub Command241_Click()
On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acReport, "r_workorder"
DoCmd.OpenReport "r_workorder", acViewPreview
End Sub
Private Sub Command247_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "r_workorder old", acViewPreview
End Sub
Private Sub RadioUnit_AfterUpdate()
If IsNull([Unit]) Or [Unit] = 0 Then
'[Unit] = Val([RadioUnit].Column(0))
[Unit].SetFocus
SendKeys [RadioUnit].Column(0), True
'SendKeys "{TAB}"
'[Assigned by].SetFocus

End If
If IsNull([RADIO_#]) Then
[RADIO_#] = [RadioUnit].Column(1)
End If
'Refresh
End Sub
Private Sub Unit_BeforeUpdate(Cancel As Integer)
Me.RADIO__ = Me.Unit.Column(1)

End Sub
Private Sub unitbutton_Click()
DoCmd.OpenForm "unit master", acNormal, , "[unit_#] =" & [radiono]
End Sub

Good Luck with your program