Автоматизация учета основных средств на предприятии (стр. 22 из 29)

Exit Sub

End If

Set db = CurrentDb

Set Rec = db. OpenRecordset ("select * from Формы where НомерФорма = " & NomerForm, dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrFormName = Rec. Fields ("Наименование"). Value

StrFile = Rec. Fields ("Файл"). Value

Else

Set Rec = Nothing

MsgBox "Нет информации о форме №" & NomerForm & "!", vbCritical + vbOKOnly

Exit Sub

End If

Set Rec = Nothing

StrPath = s_folder + StrFile

If Len (Dir$ (StrPath)) = 0 Then

MsgBox "Файл бланка формы '" & StrFormName & "' " & StrPath & " не обнаружен!", vbCritical + vbOKOnly

Exit Sub

End If

Set Rec = db. OpenRecordset ("SELECT Параметры. *, Сотрудники. Сотрудник FROM Сотрудники INNER JOIN Параметры ON Сотрудники. НомерСотр = Параметры. ГлБухгалтер", dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrFirmName = Nz (Rec. Fields ("НаименованиеФирмы"). Value, "")

StrFirmOKPO = Nz (Rec. Fields ("ОКПО"). Value, "")

StrGlBuch = Nz (Rec. Fields ("Сотрудник"). Value, "")

StrFirmAddr = Nz (Rec. Fields ("ЮрАдрес"). Value, "")

StrFirmReq = Nz (Rec. Fields ("БанкРеквизиты"). Value, "")

Else

MsgBox "Общие параметры фирмы не занесены!", vbCritical + vbOKOnly

Exit Sub

End If

Set Rec = Nothing

Set Rec = db. OpenRecordset ("select * from запрос_АктыПриемки where НомерАкт = " & nomer, dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrRukName = Nz (Rec. Fields ("s_ruk"). Value, "")

StrRukDolzh = Nz (Rec. Fields ("d_ruk"). Value, "")

StrDatePodp = Nz (Rec. Fields ("ДатаПодписи"). Value, Date)

StrOsn = Nz (Rec. Fields ("Основание"). Value, "")

StrDatePriem = Nz (Rec. Fields ("ДатаПриемки"). Value, Date)

StrSchet = Nz (Rec. Fields ("Счет"). Value, "")

StrAmort = Nz (Rec. Fields ("НомерАмортГруппы"). Value, "")

NomerVnutr = Nz (Rec. Fields ("НомерВнутр"). Value, nomer)

StrDate = Nz (Rec. Fields ("ДатаАкта"). Value, Date)

StrTovar = Nz (Rec. Fields ("Товар"). Value, "")

StrInv = Nz (Rec. Fields ("ИнвКод"). Value, "")

StrStoim = Nz (Rec. Fields ("ПервСтоииость"). Value, 0)

StrSroki = Nz (Rec. Fields ("СрокИспользования"). Value, 0)

StrMethod = Nz (Rec. Fields ("СпособАморт"). Value, "")

StrMest = Nz (Rec. Fields ("Местонахождение"). Value, "")

StrKol = Nz (Rec. Fields ("Количество"). Value,

1)

StrDateIsp = Nz (Rec. Fields ("ДатаИспытания"). Value, Date)

vbSootv = Nz (Rec. Fields ("Соотвествие"). Value, True)

vbDorab = Nz (Rec. Fields ("Доработка"). Value, False)

StrSootv = Nz (Rec. Fields ("ЧтоСоотв"). Value, "")

StrDorab = Nz (Rec. Fields ("ЧтоДораб"). Value, "")

StrZakl = Nz (Rec. Fields ("Заключение"). Value, "")

StrTechDoc = Nz (Rec. Fields ("ТехДок"). Value, "")

StrPredsName = Nz (Rec. Fields ("s_preds"). Value, "")

StrPredsDolzh = Nz (Rec. Fields ("d_preds"). Value, "")

StrChl1Name = Nz (Rec. Fields ("s_4l1"). Value, "")

StrChl1Dolzh = Nz (Rec. Fields ("d_4l1"). Value, "")

StrChl2Name = Nz (Rec. Fields ("s_4l2"). Value, "")

StrChl2Dolzh = Nz (Rec. Fields ("d_4l2"). Value, "")

StrPrinName = Nz (Rec. Fields ("s_prin"). Value, "")

StrPrinDolzh = Nz (Rec. Fields ("d_prin"). Value, "")

StrPrinDate = Nz (Rec. Fields ("ДатаПринятия"). Value, Date)

StrDovFor = Nz (Rec. Fields ("ВыданаДов"). Value, "")

StrDovDate = Nz (Rec. Fields ("ДатаДов"). Value, Date)

StrDovNom = Nz (Rec. Fields ("НомерДов"). Value, "1")

StrXranName = Nz (Rec. Fields ("s_xran"). Value, "")

StrXranDolzh = Nz (Rec. Fields ("d_xran"). Value, "")

StrXranNomer = Nz (Rec. Fields ("s_nomer"). Value, "")

StrXranDate = Nz (Rec. Fields ("ДатаХранения"). Value, Date)

Else

MsgBox "Акт приемки №" & nomer & " не найден!", vbCritical + vbOKOnly

Exit Sub

End If

Set Rec = Nothing

Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrDatePodp), dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrMonthPodp = Nz (Rec. Fields ("НазвМес"). Value, "")

Else

StrMonthPodp = "нет названия"

End If

Set Rec = Nothing

Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrDateIsp), dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrMonthIsp = Nz (Rec. Fields ("НазвМес"). Value, "")

Else

StrMonthIsp = "нет названия"

End If

Set Rec = Nothing

Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrPrinDate), dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrMonthPrin = Nz (Rec. Fields ("НазвМес"). Value, "")

Else

StrMonthPrin = "нет названия"

End If

Set Rec = Nothing

Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrDovDate), dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrMonthDov = Nz (Rec. Fields ("НазвМес"). Value, "")

Else

StrMonthDov = "нет названия"

End If

Set Rec = Nothing

Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrXranDate), dbOpenSnapshot)

If Rec. RecordCount > 0 Then

StrMonthXran = Nz (Rec. Fields ("НазвМес"). Value, "")

Else

StrMonthXran = "нет названия"

End If

Set Rec = Nothing

Set oApp = CreateObject ("Excel. Application")

oApp. Workbooks. Open FileName: =StrPath, ReadOnly: =True

oApp. ActiveWorkbook. Sheets (1). Select

oApp. Cells (rRukName, cRukName). Value = StrRukName

oApp. Cells (rRukDolzh, cRukDolzh). Value = StrRukDolzh

oApp. Cells (rDatRukDay, cDatRukDay). Value = Format$ (StrDatePodp, "dd")

oApp. Cells (rDatRukMon, cDatRukMon). Value = StrMonthPodp

oApp. Cells (rDatRukYear, cDatRukYear). Value = Right$ (Format$ (StrDatePodp, "yyyy"),

1)

oApp. Cells (rFirmName, cFirmName). Value = StrFirmName

oApp. Cells (rFirmOKPO, cFirmOKPO). Value = StrFirmOKPO

oApp. Cells (rFirmAddr, cFirmAddr). Value = StrFirmAddr

oApp. Cells (rFirmReq, cFirmReq). Value = StrFirmReq

oApp. Cells (rOsn, cOsn). Value = StrOsn

oApp. Cells (rDatPriem, cDatPriem). Value = Format$ (StrDatePriem, "dd. mm. yyyy")

oApp. Cells (rSchet, cSchet). Value = StrSchet

oApp. Cells (rAmort, cAmort). Value = StrAmort

oApp. Cells (rInv, cInv). Value = StrInv

oApp. Cells (rNomer, cNomer). Value = NomerVnutr

oApp. Cells (rDat, cDat). Value = Format$ (StrDate, "dd. mm. yyyy")

oApp. Cells (rTovar, cTovar). Value = StrTovar

oApp. Cells (rMest, cMest). Value = StrMest

oApp. ActiveWorkbook. Sheets (2). Select

oApp. Cells (rPerv, cPerv). Value = StrStoim

oApp. Cells (rSrok, cSrok). Value = StrSroki

oApp. Cells (rType, cType). Value = StrMethod

oApp. Cells (rName2, cName2). Value = StrTovar

oApp. Cells (rKol, cKol). Value = StrKol & " шт."

oApp. ActiveWorkbook. Sheets (3). Select

oApp. Cells (rDatIspDay, cDatIspDay). Value = Format$ (StrDateIsp, "dd")

oApp. Cells (rDatIspMon, cDatIspMon). Value = StrMonthIsp

oApp. Cells (rDatIspYear, cDatIspYear). Value = Right$ (Format$ (StrDateIsp, "yyyy"),

1)

If vbSootv = True Then

oApp. Cells (rSootv1, cSootv1). Font. Bold = True

oApp. Cells (rSootv2, cSootv2). Font. Bold = False

oApp. Cells (rSootvInf, cSootvInf). Value = ""

Else

oApp. Cells (rSootv1, cSootv1). Font. Bold = False

oApp. Cells (rSootv2, cSootv2). Font. Bold = True

oApp. Cells (rSootvInf, cSootvInf). Value = StrSootv

End If

If vbDorab = True Then

oApp. Cells (rDorab1, cDorab1). Font. Bold = True

oApp. Cells (rDorab2, cDorab2). Font. Bold = False

oApp. Cells (rDorabInf, cDorabInf). Value = StrDorab

Else

oApp. Cells (rDorab1, cDorab1). Font. Bold = False

oApp. Cells (rDorab2, cDorab2). Font. Bold = True

oApp. Cells (rDorabInf, cDorabInf). Value = ""

End If

oApp. Cells (rResult, cResult). Value = StrZakl

oApp. Cells (rTDoc, cTDoc). Value = StrTechDoc

oApp. Cells (rPredsName, cPredsName). Value = StrPredsName

oApp. Cells (rPredsDolzh, cPredsDolzh). Value = StrPredsDolzh

oApp. Cells (rChl1Name, cChl1Name). Value = StrChl1Name

oApp. Cells (rChl1Dolzh, cChl1Dolzh). Value = StrChl1Dolzh

oApp. Cells (rChl2Name, cChl2Name). Value = StrChl2Name

oApp. Cells (rChl2Dolzh, cChl2Dolzh). Value = StrChl2Dolzh

oApp. Cells (rPrinName, cPrinName). Value = StrPrinName

oApp. Cells (rPrinDolzh, cPrinDolzh). Value = StrPrinDolzh

oApp. Cells (rDatPrinDay, cDatPrinDay). Value = Format$ (StrPrinDate, "dd")

oApp. Cells (rDatPrinMon, cDatPrinMon). Value = StrMonthPrin

oApp. Cells (rDatPrinYear, cDatPrinYear). Value = Right$ (Format$ (StrPrinDate, "yyyy"),

1)

oApp. Cells (rDatDovDay, cDatDovDay). Value = Format$ (StrDovDate, "dd")

oApp. Cells (rDatDovMon, cDatDovMon). Value = StrMonthDov

oApp. Cells (rDatDovYear, cDatDovYear). Value = Right$ (Format$ (StrDovDate, "yyyy"),

1)

oApp. Cells (rDatDovOsn, cDatDovOsn). Value = StrDovFor

oApp. Cells (rDatDovNomer, cDatDovNomer). Value = StrDovNom

oApp. Cells (rXranName, cXranName). Value = StrXranName

oApp. Cells (rXranDolzh, cXranDolzh). Value = StrXranDolzh

oApp. Cells (rXranNomer, cXranNomer). Value = StrXranNomer

oApp. Cells (rDatXranDay, cDatXranDay). Value = Format$ (StrXranDate, "dd")

oApp. Cells (rDatXranMon, cDatXranMon). Value = StrMonthXran

oApp. Cells (rDatXranYear, cDatXranYear). Value = Right$ (Format$ (StrXranDate, "yyyy"),

1)

oApp. Cells (rNomer2, cNomer2). Value = NomerVnutr

oApp. Cells (rDatSost, cDatSost). Value = Format$ (StrDate, "dd. mm. yyyy")

oApp. Cells (rBuchName, cBuchName). Value = StrGlBuch

ex:

Application. SysCmd acSysCmdRemoveMeter

If Not (oApp Is Nothing) Then oApp. Visible = True

Set Rec = Nothing