Склад продуктов для столовой

База данных «Склад продуктов для столовой или кафе» - MS Access 2003

Один из вариантов реализации спроектированной в прошлом посте базы данных. Работа была сделана довольно давно, этим и объясняется немножко устаревший вид. Реализованы все функции по заданию (учет продуктов, блюд, калькуляция).

В соответствии с разработанной инфологической моделью создадим таблицы:

• Продукты (все сведения о продуктах, включая остаток);
• Блюда (сведения о блюдах);
• Рецептура (рецептура блюд);
• Единицы (единицы измерения);
• Приемка (данные о приходе продуктов на склад);
• Отпуск (документы отпуска продуктов со склада);
• Отпуск_прод (табличная часть документа Отпуск).

Схема базы данных на рисунке:

Формы базы данных
Главная экранная форма (открывается при запуске файла базы)

Форма справочников

Остальные не привожу, они однотипные все.
Форма справочника продуктов:

Форма ленточная, просто все поля таблицы. Количество продукта меняется с помощью документов приема и отпуска. Для поиска продукта по названию служит поле со списком. Источник строк поля – запрос

SELECT Продукты.ID_продукта, Продукты.Название_продукта FROM Продукты.

Далее создаем форму для единиц измерения (не привожу здесь).
Форма «Список блюд»

Форма позволяет добавлять и удалять блюда. Для поиска блюд по названию предназначено поле со списком (источник записей – запрос SELECT Блюда.ID_блюда, Блюда.Название_блюда FROM Блюда).
При нажатии кнопки кнРецептура (надпись «Рецептура») открывается форма «Блюда» для отобранной записи.

Форма «Блюда» для всех записей доступна из главной формы.
Она позволяет заносить затраты на изготовление блюда и накладные расходы, заполнять рецептуру блюд. Это составная форма. В качестве подчиненной используется форма «Рецептура» (поля ID_блюда, Название_продукта и Количество_на_порцию, режим – табличный). Связь с основной формой по полю ID_блюда. Из формы «Блюда» можно открыть справочник продуктов (форму «Продукты» для всех записей), а также распечатать калькуляцию выбранного блюда.
Переходим к документам.
Поступление продуктов. В этой форме

проводится добавление и удаление записей. Просто так вставить запись нельзя – нужно еще рассчитать количество на складе. Это можно сделать разными способами. Я приведу программный.
Источник данных формы - запрос SELECT Приемка.Номер_док, Приемка.Дата_док, Приемка.ID_продукта, Приемка.Кол_во, Продукты.ID_ед FROM Продукты INNER JOIN Приемка ON Продукты.ID_продукта=Приемка.ID_продукта. Запрос нужен для подстановки единицы измерения при выборе продукта.
Форма позволяет добавить запись, удалить запись, открыть форму Накладная, в которой можно изменить количество продукта.
При добавлении, изменении или удалении записи необходимо изменять количество выбранного продукта в таблице Продукты.
Процедура кнопки кнДобавить (надпись «Добавить запись»):

Private Sub кнДобавить_Click()
On Error GoTo Err_кнДобавить_Click
    DoCmd.GoToRecord , , acNewRec
    Дата_док.Value = Date      ‘**по умолчанию – текущая дата**
Exit_кнДобавить_Click:
    Exit Sub
Err_кнДобавить_Click:
    MsgBox Err.Description
    Resume Exit_кнДобавить_Click
End Sub

Для изменения количества продукта в таблице Продукты после добавления записи служит процедура ПослеВставки (AfterInsert):

Private Sub Form_AfterInsert()
Dim myDb As Database
Dim prod As Recordset

Set myDb = CurrentDb
Set prod = myDb.OpenRecordset("Продукты")
 ‘** находим в таблице продукт по индексу ID_продукта**
prod.Index = "PrimaryKey"
prod.Seek "=", ID_продукта.Value
‘** изменяем значение**
prod.Edit
prod.Fields("Количество").Value = prod.Fields("Количество").Value + Кол_во.Value
prod.Update
prod.Close
Set prod = Nothing
Set myDb = Nothing
End Sub

Процедура кнопки кнУдалить (надпись «Удалить запись»)

Private Sub кнУдалить_Click()
On Error GoTo Err_кнУдалить_Click

Dim response
response = MsgBox("Удалить запись?", vbExclamation + vbYesNo, "Подтверждение удаления записи")
If response = vbNo Then
    Exit Sub
End If

Dim myDb As Database
Dim prod As Recordset
Dim doc As Recordset

    Set myDb = CurrentDb
    Set prod = myDb.OpenRecordset("Продукты")
    Set doc = myDb.OpenRecordset("Приемка")
    ‘** находим в таблице продукт по индексу ID_продукта**
    prod.Index = "PrimaryKey"
    prod.Seek "=", ID_продукта.Value 
   ‘** изменяем значение**
    prod.Edit
    prod.Fields("Количество").Value = prod.Fields("Количество").Value - Кол_во.Value
    prod.Update
   prod.Close
    Set prod = Nothing
    ‘**находим документ по номеру и удаляем запись**
    doc.Index = "PrimaryKey"
    doc.Seek "=", Номер_док.Value
    doc.Delete
    doc.Close
   
    Set doc = Nothing
    Set myDb = Nothing

    DoCmd.Requery
Exit_кнУдалить_Click:
    Exit Sub
Err_кнУдалить_Click:
    MsgBox Err.Description
    Resume Exit_кнУдалить_Click
End Sub

Форма «Накладная»

Можно изменить название и количество продукта, распечатать накладную.
Источник – запрос SELECT Приемка.Номер_док, Приемка.Дата_док, Приемка.ID_продукта, Приемка.Кол_во, Продукты.ID_ед FROM Продукты INNER JOIN Приемка ON Продукты.ID_продукта=Приемка.ID_продукта (для подбора единицы измерения).
При открытии формы двум переменным id и Kolvo присваиваются значения ID_продукта и количество:

Private Sub Form_Open(Cancel As Integer)
If  ID_продукта.Value <> "" Then
    id = ID_продукта.Value
    Kolvo = Кол_во
End If
End Sub

При выборе другого продукта (процедура ВнесеныИзменения - ID_продукта_Dirty(Cancel As Integer)) обновляется единица измерения:
ID_ед.Requery
При закрытии формы, если были какие-то изменения в форме – изменяется запись в таблице Продукты.

If  ID_продукта.Value <> id Or Кол_во.Value <> Kolvo Then
    Dim myDb As Database
    Dim prod As Recordset

    Set myDb = CurrentDb
    Set prod = myDb.OpenRecordset("Продукты")
    prod.Index = "PrimaryKey"
'---------------------
 '-изменяем таблицу ПРОДУКТЫ-------
  '-удаляем старые значения---   
prod.Seek "=", id
    prod.Edit
    prod.Fields("Количество ").Value = prod.Fields("Количество ").Value - Kolvo
    prod.Update
 '--используем новые значения-
    prod.Seek "=", ID_продукта.Value
    prod.Edit
    prod.Fields("Количество").Value = prod.Fields("Количество").Value + Кол_во.Value
    prod.Update
    prod.Close
    Set prod = Nothing
    Set myDb = Nothing
End If

Форма «Отпуск» (разработка) содержит список документов на отпуск.

В форме заполняется дата документа, название блюда и количество порций. После вставки записи открывается форма «Накладная на отпуск продуктов».

После нажатия на кнопке «Заполнить» из таблицы «Рецептура» выбираются продукты и вычисляется количество.

Private Sub кнЗаполнить_Click()
On Error GoTo Err_кнЗаполнить_Click

If  ID_блюда.Value = "" Then
    MsgBox "Не выбрано блюдо"
    Exit Sub
End If

Dim myDb As Database
Dim prod As Recordset
Dim rec As Recordset
Dim otp As Recordset

Dim id As String
Dim SQLstr As String


Set myDb = CurrentDb
Set prod = myDb.OpenRecordset("Продукты")

id = ID_блюда.Value
‘**текст запроса**
SQLstr = "SELECT *FROM Рецептура WHERE ID_блюда=" + id

‘**открывается набор записей по запросу**
Set rec = myDb.OpenRecordset(SQLstr)
If rec.RecordCount = 0 Then
    MsgBox "Рецептура не заполнена"
   GoTo Exit_кнЗаполнить_Click
End If

rec.MoveFirst
prod.Index = "PrimaryKey"
While Not rec.EOF
    prod.Seek "=", rec.Fields("ID_продукта").Value
‘**если кол-во на складе меньше, чем нужно, то предупреждение**   
If prod.Fields("Количество").Value < rec.Fields("Количество_на_порцию").Value * Кол_порций.Value Then
        MsgBox "Указанное количество продукта на складе " & _
        "отсутствует. Продукт " & prod.Fields("Название_продукта").Value
        GoTo Exit_кнЗаполнить_Click
    End If
    rec.MoveNext
Wend

Set otp = myDb.OpenRecordset("Отпуск_прод")

otp.Index = "Номер_док"
otp.MoveFirst
‘**удаляются старые записи в таблице Отпуск_прод**
‘**восстанавливается количество продуктов**
While Not otp.EOF
    If otp.Fields("Номер_док").Value = Номер_док.Value Then
        prod.Seek "=", otp.Fields("ID_продукта").Value
        prod.Edit
        prod.Fields("Количество").Value = prod.Fields("Количество").Value + otp.Fields("Количество").Value
        prod.Update
        otp.Delete
    End If
    otp.MoveNext
Wend
‘**добавление новых записей в таблицу Отпуск_прод**
‘**вычисление количества продуктов**
rec.MoveFirst
While Not rec.EOF
    prod.Seek "=", rec.Fields("ID_продукта").Value
    prod.Edit
    prod.Fields("Количество").Value = prod.Fields("Количество").Value _
        - rec.Fields("Количество_на_порцию").Value * Кол_порций.Value
    prod.Update
    otp.AddNew
    otp!Номер_док = Номер_док.Value
    otp!ID_продукта = rec.Fields("ID_продукта").Value
    otp!Количество = rec.Fields("Количество_на_порцию").Value * _ Кол_порций.Value
    otp.Update
    rec.MoveNext
Wend

Me.Refresh ‘**Обновление формы**
otp.Close
Set otp = Nothing

Exit_кнЗаполнить_Click:
    prod.Close
    rec.Close
     Set rec = Nothing
    Set prod = Nothing
    Set myDb = Nothing
    Exit Sub

Err_кнЗаполнить_Click:
    MsgBox Err.Description
    Resume Exit_кнЗаполнить_Click
   
End Sub

Из этой формы можно распечатать накладную на отпуск продуктов.
Переходим к отчетам
Отчет «Калькуляция» основан на запросе:

SELECT Блюда.ID_блюда, Блюда.Название_блюда, Блюда.Затраты, Блюда.Расходы, Продукты.ID_продукта, Продукты.Название_продукта, Продукты.ID_ед, Продукты.Цена, Рецептура.Количество_на_порцию
FROM Продукты INNER JOIN (Блюда INNER JOIN Рецептура ON Блюда.ID_блюда = Рецептура.ID_блюда) ON Продукты.ID_продукта = Рецептура.ID_продукта
WHERE (((Блюда.ID_блюда)=[Forms]![Блюда]![ID_блюда])).

Отчет «Калькуляция» открывается из формы «Блюда».
Отчет «Накладная на приемку продукта» создан на запросе

SELECT Приемка.Номер_док, Приемка.Дата_док, Приемка.ID_продукта, Приемка.Кол_во, Продукты.ID_ед
FROM Продукты INNER JOIN Приемка ON Продукты.ID_продукта = Приемка.ID_продукта
WHERE (((Приемка.Номер_док)=[Forms]![Приемка]![Номер_док])).

Отчет «Накладная на отпуск продуктов» создан на запросе

SELECT Отпуск.Номер_док, Отпуск.Дата_док, Отпуск.ID_блюда, Отпуск.Кол_порций, Отпуск_прод.ID_продукта, Отпуск_прод.Количество, Продукты.ID_ед
FROM Отпуск INNER JOIN (Единицы INNER JOIN (Продукты INNER JOIN Отпуск_прод ON Продукты.ID_продукта = Отпуск_прод.ID_продукта) ON Единицы.ID_ед = Продукты.ID_ед) ON Отпуск.Номер_док = Отпуск_прод.Номер_док
WHERE (((Отпуск.Номер_док)=[Forms]![Отпуск2]![Номер_док]))

Вот такой вариант выполнения.