Aplicatie de gestiune a stocurilor

Aplicatie de gestiune a stocurilor

• Emiterea de documente de intrare si iesire: receptii de marfa, facturi, avize de expeditie, note de transfer, bonuri de consum
• Formule  de calcul automat de preturi folosind CMP.
• Evidenta stocurilor
• Centralizatoare contabile pe tipuri de docmente
• Situatii cantitativ valorice de intrari si iesiri din gestiune
• Fise de material
• Balanta de materiale (format A3)

Schema bazei de date este :

Urmatorii operatori se pot conecteaza la baza de date:

Sunt folositi pentru a vedea cine si cand au introdus/scos in/ din magazie materiale.

Din forma principala se pot face actualizari,interoga datele din magazie  sau obtine rapoarte contabile

Calculul principal al costului mediu ponderat are loc in urmatoarele forme :

Se alege materialul existent in magazie daca se doreste introducerea unui material nou se va introduce un nou cod:

Si este permisa doar intrarii

Se pot adauga sau scoate materiale prin alegerea tipului (tipSIE)

1. S = stoc

2. I =Intrare

3. E = Iesire

Calculele si formulele folosite se vad in programul de mai jos:

“Costul mediu ponderat = / (cantit. inceputul lunii + cantit intrata in timpul lunii)”

Option Compare Database

Option Explicit

Public tip As Integer

Private Sub BON_MAT_GotFocus()

Me![BON_MAT].BackColor = 8454143

End Sub

Private Sub BON_MAT_LostFocus()

Me!

[BON_MAT].BackColor = -2147483643

End Sub

Private Sub CANTITATE1_AfterUpdate()

If (tip = 1) Or (tip = 3) Then

If (Not IsNull(Me![CANTITATE1])) And (Not IsNull(Me![PRET_UNIT])) Then

Me![V_INTRARE] = Me![CANTITATE1] * Me![PRET_UNIT]

End If

End If

End Sub

Private Sub CANTITATE1_BeforeUpdate(Cancel As Integer)

If IsNull(Me![CANTITATE1]) Then

Cancel = True

MsgBox 'Trebuie sa introduceti cantitate1'

End If

End Sub

Private Sub CANTITATE1_GotFocus()

Me![CANTITATE1].BackColor = 8454143

End Sub

Private Sub CANTITATE1_LostFocus()

Me![CANTITATE1].BackColor = -2147483643

End Sub

Private Sub CANTITATE2_AfterUpdate()

If tip = 2 Then

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query8')

qd.Parameters('Co') = Me![COD_MAT]

qd.Parameters('vr') = Me![VRECN] - 1

Set rs = qd.OpenRecordset

Me![PRET_CMP] = rs![PRET_CMP]

Me![V_IESIRE] = Me![CANTITATE2] * Me![PRET_CMP]

Me![CANTSTOC] = rs![CANTSTOC] - Me![CANTITATE2]

Me![PU] = Me![PRET_CMP]

Me![VALSTOCZI] = Me![PU] * Me![CANTSTOC]

rs.Close

End If

End Sub

Private Sub CANTITATE2_BeforeUpdate(Cancel As Integer)

If IsNull(Me![CANTITATE2]) Then

Cancel = True

MsgBox 'Trebuie sa introduceti cantitate2'

Exit Sub

End If

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query8')

qd.Parameters('Co') = Me![COD_MAT]

qd.Parameters('vr') = Me![VRECN] - 1

Set rs = qd.OpenRecordset

If (rs![CANTSTOC] - Me![CANTITATE2] < 0) Then

Cancel = True

MsgBox 'Cantitatea extrasa este mai mare decat stocul la zi : ' & CStr(rs![CANTSTOC])

End If

rs.Close

End Sub

Private Sub CANTITATE2_GotFocus()

Me![CANTITATE2].BackColor = 8454143

End Sub

Private Sub CANTITATE2_LostFocus()

Me![CANTITATE2].BackColor = -2147483643

End Sub

Private Sub COD_SECTIE_GotFocus()

Me![COD_SECTIE].BackColor = 8454143

End Sub

Private Sub COD_SECTIE_LostFocus()

Me![COD_SECTIE].BackColor = -2147483643

End Sub

Private Sub

Command58_Click()

DoCmd.OpenForm 'query3', acFormDS, '', '', acNormal

End Sub

Private Sub CONT_CHELT_GotFocus()

Me![CONT_CHELT].BackColor = 8454143

End Sub

Private Sub CONT_CHELT_LostFocus()

Me![CONT_CHELT].BackColor = -2147483643

End Sub

Private Sub DATA_IN_AfterUpdate()

Dim m As String

m = CStr(Month(Me![DATA_IN]))

If Month(Me![DATA_IN]) <= 9 Then

m = '0' & CStr(Month(Me![DATA_IN]))

End If

Me![DATAIN] = m & Year(Me![DATA_IN])

End Sub

Private Sub DATA_IN_BeforeUpdate(Cancel As Integer)

If IsNull(Me![DATA_IN]) Then

Cancel = True

MsgBox 'Trebuie sa introduceti o data'

End If

' verific ca data introdusa sa fie >= cu ultima data pt material

If Me![VRECN] > 1 Then ' nu e primul element

' caut 'data_in' pentru 'vrecn-1' si 'cod_mat' din magazie

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query6')

qd.Parameters('cm') = Me![COD_MAT]

qd.Parameters('VR') = Me![VRECN] - 1

Set rs = qd.OpenRecordset

Dim d As Date

d = rs![DATA_IN]

rs.Close

If Me![DATA_IN] < d Then

Cancel = True

MsgBox 'trebuie sa introduci o data >= ' & CStr(d)

End If

End If

End Sub

Private Sub DATA_IN_GotFocus()

Me![DATA_IN].BackColor = 8454143

End Sub

Private Sub DATA_IN_LostFocus()

Me![DATA_IN].BackColor = -2147483643

End Sub

Private Sub Form_AfterInsert()

Me![TIPSIE].Enabled = True

Me![TIPSIE].Locked = False

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query4')

qd.Parameters('n') = Forms![Falegematerialul]![Combo0]

Set rs = qd.OpenRecordset

'Me![VRECN] = Nz(rs![Max]) + 1

If rs.RecordCount = 0 Then

Me![VRECN] = 1

Me![TIPSIE] = 'I'

' ** ** ** ** *****

Me![TIPSIE].Locked = False

Me![TIPSIE].Enabled = True

' ** ** ** ** *****

Else

Me![VRECN] = rs![max] + 1

End If

rs.Close

Me![DATAOP] = data

Me![CODOP] = operator

Me![COD_MAT] = Forms![Falegematerialul]![Combo0]

Me![TIPSIE].SetFocus

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If (tip = 1) Or (tip = 3) Then

If IsNull(Me![CANTITATE1]) Then

Cancel = True

MsgBox 'Trebuie sa introduceti cantitate1'

Exit Sub

End If

If IsNull(Me![PRET_UNIT]) Then

Cancel = True

MsgBox 'Trebuie sa introduceti pret_unit'

Exit Sub

End If

Else

' tip=2

If IsNull(Me![CANTITATE2]) Then

Cancel = True

MsgBox 'Trebuie Introdusa cantitate2'

Exit Sub

End If

If IsNull(Me![COD_SECTIE]) Then

Cancel = True

MsgBox 'Trebuie Introdusa cod_sectie'

Exit Sub

End If

If IsNull(Me![BON_MAT]) Then

Cancel = True

MsgBox 'Trebuie Introdusa bon_mat'

Exit Sub

End If

End If

Dim rasp As Integer

rasp = MsgBox('Doriti Salvarea Inregistrarii ?', vbYesNo)

If rasp = vbNo Then

Cancel = True

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Me![TIPSIE].Enabled = True

Me![TIPSIE].Locked = False

End If

End Sub

Private Sub Command48_Click()

On Error GoTo Err_Command48_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command48_Click:

Exit Sub

Err_Command48_Click:

MsgBox Err.Description

Resume Exit_Command48_Click

End Sub

Private Sub Command49_Click()

On Error GoTo Err_Command49_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_Command49_Click:

Exit Sub

Err_Command49_Click:

MsgBox Err.Description

Resume Exit_Command49_Click

End Sub

Private Sub Command50_Click()

On Error GoTo Err_Command50_Click

DoCmd.Close

Exit_Command50_Click:

Exit Sub

Err_Command50_Click:

MsgBox Err.Description

Resume Exit_Command50_Click

End Sub

Private Sub Form_Load()

Me![TIPSIE].SetFocus

Me.Form.Caption = Forms![Falegematerialul]![Label3].Caption

End Sub

Private Sub NIR_GotFocus()

Me![NIR].BackColor = 8454143

End Sub

Private Sub NIR_LostFocus()

Me![NIR].BackColor = -2147483643

End Sub

Private Sub PRET_UNIT_AfterUpdate()

If (Not IsNull(Me![CANTITATE1])) And (Not IsNull(Me![PRET_UNIT])) Then

Me![V_INTRARE] = Me![CANTITATE1] * Me![PRET_UNIT]

End If

If (tip = 1) Then

Me![CANTSTOC] = Me![CANTITATE1]

Me![PU] = Me![PRET_UNIT]

Me![PRET_CMP] = Me![PU]

Me![VALSTOCZI] = Me![V_INTRARE]

End If

If (tip = 3) Then

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query7')

qd.Parameters('co') = Me![COD_MAT]

qd.Parameters('vr') = Me![VRECN] - 1

Set rs = qd.OpenRecordset

'MsgBox CStr(rs![CANTSTOC]) & CStr(rs![VALSTOCZI])

Me![CANTSTOC] = Nz(rs![CANTSTOC]) + Me![CANTITATE1]

If Me![CANTSTOC] <> 0 Then

Me![PU] = (Nz(rs![VALSTOCZI]) + Me![V_INTRARE]) / Me![CANTSTOC]

Else

Me![PU] = 0

End If

rs.Close

Me![PRET_CMP] = Me![PU]

Me![VALSTOCZI] = Me![PU] * Me![CANTSTOC]

End If

End Sub

Private Sub PRET_UNIT_BeforeUpdate(Cancel As Integer)

If (tip = 1) Or (tip = 3) Then

If IsNull(Me![PRET_UNIT]) Then

Cancel = True

MsgBox 'Trebuie sa introduceti pret_unit'

End If

End If

End Sub

Private Sub PRET_UNIT_GotFocus()

Me![PRET_UNIT].BackColor = 8454143

End Sub

Private Sub PRET_UNIT_LostFocus()

Me![PRET_UNIT].BackColor = -2147483643

End Sub

Private Sub TIPNIR_GotFocus()

Me![TIPNIR].BackColor = 8454143

End Sub

Private Sub TIPNIR_LostFocus()

Me![TIPNIR].BackColor = -2147483643

End Sub

Private Sub TIPSIE_AfterUpdate()

If (Me![VRECN] = 1) And (Me![TIPSIE] = 'E') Then

'MsgBox ' Nu puteti sa faceti o iesire doar intrarile sunt permise (material nou) se va pune automat o intrare '

Me![TIPSIE] = 'I'

End If

Dim c As Control

For Each c In Me.Controls

If TypeOf c Is TextBox Then

If Me![VRECN] = 1 Then

Select Case c.TabIndex

Case 2, 5, 6, 9, 10

c.Locked = False

c.Enabled = True

End Select

tip = 1

Else

If Me![TIPSIE] = 'E' Then

Select Case c.TabIndex

Case 2, 13, 14, 17

c.Locked = False

c.Enabled = True

End Select

tip = 2

Else

Select Case c.TabIndex

Case 2, 5, 6, 9, 10

c.Locked = False

c.Enabled = True

End Select

tip = 3

End If ''me![tipsie]='E'

End If 'Me![vrecn]=1

End If 'typeof c is textbox

Next c

'If tip = 1 Or tip = 3 Then

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query11')

qd.Parameters('co') = Me![COD_MAT]

Set rs = qd.OpenRecordset

Me![CONT] = rs![CONT]

rs.Close

'End If

Me![DATA_IN].SetFocus

If tip = 2 Then

Me![COD_SECTIE].Enabled = True

Me![COD_SECTIE].Locked = False

End If

Me![TIPSIE].Enabled = False

Me![TIPSIE].Locked = True

End Sub

Private Sub Command51_Click()

On Error GoTo Err_Command51_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command51_Click:

Exit Sub

Err_Command51_Click:

MsgBox Err.Description

Resume Exit_Command51_Click

End Sub

Private Sub TIPSIE_BeforeUpdate(Cancel As Integer)

Dim c As Control

For Each c In Me.Controls

If TypeOf c Is TextBox Then

Select Case c.TabIndex

Case 2, 5, 6, 9, 10, 11, 12, 13, 14, 17

c.Undo

c.Locked = True

c.Enabled = False

End Select

End If

Next c

Me![COD_SECTIE].Enabled = False

Me![COD_SECTIE].Locked = True

'If (Me![VRECN] = 1) And (Me![TIPSIE] = 'E') Then

'Cancel = True

'MsgBox ' Nu puteti sa faceti o iesire doar intrarile sunt permise (material nou) se va pune automat o intrare '

' End If

End Sub

Private Sub TIPSIE_GotFocus()

Me![TIPSIE].BackColor = 8454143

End Sub

Private Sub TIPSIE_LostFocus()

Me![TIPSIE].BackColor = -2147483643

End Sub

Private Sub Command61_Click()

On Error GoTo Err_Command61_Click

DoCmd.Close

Exit_Command61_Click:

Exit Sub

Err_Command61_Click:

MsgBox Err.Description

Resume Exit_Command61_Click

End Sub

Option Compare Database

Private Sub Combo0_Change()

'Mat = Me![Combo0].Value

Dim db As Database

Dim qd As QueryDef

Dim rs As Recordset

Set db = CurrentDb()

Set qd = db.QueryDefs('query12')

qd.Parameters('co') = Me.Combo0

Set rs = qd.OpenRecordset

If IsNull(rs![DENUMIRE_M]) Then

Exit Sub

Else

Me.Label3.Caption = CStr(rs![DENUMIRE_M])

End If

rs.Close

End Sub

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

Dim iAnswer As Integer

Mat = CDbl(Me![Combo0].Text)

iAnswer = MsgBox('Materialul nu exista. Doriti sa il adaugati(YES/NO)', vbYesNo + vbQuestion)

If iAnswer = vbYes Then

DoCmd.OpenForm 'Fmaterial', acNormal, , , acFormAdd, acDialog

Response = acDataErrAdded

Else

Response = acDataErrContinue

End If

End Sub

Private Sub Command2_Click()

DoCmd.Close acForm, 'Query3'

DoCmd.OpenForm 'Query3', acNormal

End Sub

Private Sub Command5_Click()

On Error GoTo Err_Command5_Click

DoCmd.Close

Exit_Command5_Click:

Exit Sub

Err_Command5_Click:

MsgBox Err.Description

Resume Exit_Command5_Click

End Sub

Restul codului se gaseste in evenimentele formelor, frazele SQL ale interogarilor, evenimentele rapoartelor si in macrouri.