VERSION 5.00 Begin {78E93846-85FD-11D0-8487-00A0C90DC8A9} Repngsuebersicht Caption = "Abrechnung" ClientHeight = 8490 ClientLeft = 165 ClientTop = 450 ClientWidth = 10860 WindowState = 1 'Minimiert _ExtentX = 19156 _ExtentY = 14975 _Version = 393216 _DesignerVersion= 100684101 ReportWidth = 3615 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 8.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty GridX = 1 GridY = 1 LeftMargin = 200 RightMargin = 50 BottomMargin = 200 _Settings = 23 NumSections = 3 SectionCode0 = 1 BeginProperty Section0 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Bereich4" Object.Height = 2850 NumControls = 6 ItemType0 = 7 BeginProperty Item0 {1C13A8E5-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Symbol1" Object.Width = 3615 Object.Height = 495 SizeMode = 2 EndProperty ItemType1 = 3 BeginProperty Item1 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblAbrechnung" Object.Left = 30 Object.Top = 1155 Object.Width = 3390 Object.Height = 390 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 14.25 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Abrechnung" Alignment = 2 EndProperty ItemType2 = 3 BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblDatum" Object.Left = 15 Object.Top = 1695 Object.Width = 3390 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Datum" Alignment = 2 EndProperty ItemType3 = 3 BeginProperty Item3 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblStationUNDTisch" Object.Top = 1995 Object.Width = 3390 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 11.25 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "StationUNDTisch" Alignment = 2 EndProperty ItemType4 = 3 BeginProperty Item4 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblKellner" Object.Left = 15 Object.Top = 2295 Object.Width = 3390 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Kellner" Alignment = 2 EndProperty ItemType5 = 3 BeginProperty Item5 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblAdresse" Object.Top = 630 Object.Width = 3585 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 8.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Adresse" CanGrow = -1 'True Alignment = 2 EndProperty EndProperty SectionCode1 = 4 BeginProperty Section1 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Bereich1" Object.Height = 600 NumControls = 5 ItemType0 = 4 BeginProperty Item0 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtGericht" Object.Left = 36 Object.Width = 3180 Object.Height = 216 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty CanGrow = -1 'True BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 0 Format = "" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty ItemType1 = 4 BeginProperty Item1 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtSummeMenge" Object.Top = 240 Object.Width = 360 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 0 Format = "" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty ItemType2 = 4 BeginProperty Item2 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtEPREIS" Object.Left = 570 Object.Top = 240 Object.Width = 1680 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType3 = 3 BeginProperty Item3 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblProdukt" Object.Left = 360 Object.Top = 225 Object.Width = 150 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "x" EndProperty ItemType4 = 4 BeginProperty Item4 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtSummeEPREIS" Object.Left = 2505 Object.Top = 240 Object.Width = 870 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#####0.00" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty EndProperty SectionCode2 = 8 BeginProperty Section2 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Bereich5" Object.Height = 1815 NumControls = 8 ItemType0 = 13 BeginProperty Item0 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionSummeBrutto" Object.Left = 1725 Object.Top = 795 Object.Width = 1665 Object.Height = 360 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 12 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType1 = 3 BeginProperty Item1 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblBar" Object.Top = 810 Object.Width = 570 Object.Height = 330 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 12 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "BAR" EndProperty ItemType2 = 3 BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblBedanken" Object.Top = 1455 Object.Width = 3375 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Bedanken" Alignment = 2 EndProperty ItemType3 = 5 BeginProperty Item3 {1C13A8E3-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Linie1" Object.Left = 15 Object.Width = 3360 EndProperty ItemType4 = 3 BeginProperty Item4 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblNetto" Object.Left = 15 Object.Top = 75 Object.Width = 1065 Object.Height = 235 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Netto" EndProperty ItemType5 = 3 BeginProperty Item5 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblMwSt" Object.Top = 330 Object.Width = 2265 Object.Height = 235 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "MwSt" EndProperty ItemType6 = 13 BeginProperty Item6 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionSummeNetto" Object.Left = 2220 Object.Top = 90 Object.Width = 1155 Object.Height = 235 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#####0.00" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty ItemType7 = 13 BeginProperty Item7 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionSummeMwSt" Object.Left = 2235 Object.Top = 345 Object.Width = 1155 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Tahoma" Size = 11.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#####0.00" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty EndProperty End Attribute VB_Name = "Repngsuebersicht" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Dim VarSummeBrutto Function Abrechnung() Dim objConn As ADODB.Connection Dim rsOffeneTische As ADODB.Recordset Dim strPath As String Set objConn = New ADODB.Connection Set rsOffeneTische = New ADODB.Recordset On Error GoTo err_Handler strPath = App.Path If Right$(strPath, 1) <> "\" Then strPath = strPath & "\" With objConn .Provider = "Microsoft Jet 4.0 OLE DB Provider" .Properties("Jet OLEDB:Database Password") = VarPasswordDatenbank .ConnectionString = "Data Source=" & strPath & "asql.dat" .CursorLocation = adUseClient .Open End With 'Tabelle OffeneTische öffnen With rsOffeneTische .ActiveConnection = objConn .CursorLocation = adUseClient .LockType = adLockOptimistic '.Open "Select Gericht,EPREIS As Einzelpreis,Sum(Menge) AS SummeMenge,Sum(Menge)*EPREIS As SummeBrutto,Sum(Menge)*EPREIS/1.19 As SummeNetto,SummeBrutto-SummeNetto AS SummeMwSt from OffeneTische Where Station = '" & VarStation & "' and Kellner='" & VarKellner & "' and Tisch='" & VarTisch & "' Group by Gericht,EPREIS" .Open "Select Gericht,EPREIS As Einzelpreis,Menge AS SummeMenge,Menge*EPREIS As SummeBrutto,Menge*EPREIS/1.19 As SummeNetto,SummeBrutto-SummeNetto AS SummeMwSt from OffeneTische Where Station = '" & VarStation & "' and Kellner='" & VarKellner & "' and Tisch='" & VarTisch & "'" End With If rsOffeneTische.EOF = True Then Abrechnung = False GoTo 10 Else Abrechnung = True End If Set ReportAbrechnung.DataSource = rsOffeneTische Set Me.Sections("Bereich4").Controls("Symbol1").Picture = LoadPicture(App.Path & "\Logos\Logo_Lokal.emf") ReportAbrechnung.Sections("Bereich4").Controls("lblDatum").Caption = Now ReportAbrechnung.Sections("Bereich4").Controls("lblStationUNDTisch").Caption = VarStation & " - " & "Tisch: " & VarTisch ReportAbrechnung.Sections("Bereich4").Controls("lblKellner").Caption = "Es bediente Sie " & VarKellner ReportAbrechnung.Sections("Bereich4").Controls("lblAdresse").Caption = "Familie Thanos, Büchenauer Str. 7, 76646 Bruchsal-Untergrombach - Tel:07257 931469" ReportAbrechnung.Sections("Bereich1").Controls("txtGericht").DataField = rsOffeneTische.Fields("Gericht").Name ReportAbrechnung.Sections("Bereich1").Controls("txtSummeMenge").DataField = rsOffeneTische.Fields("SummeMenge").Name ReportAbrechnung.Sections("Bereich1").Controls("txtEPREIS").DataField = rsOffeneTische.Fields("Einzelpreis").Name ReportAbrechnung.Sections("Bereich1").Controls("txtSummeEPREIS").DataField = rsOffeneTische.Fields("SummeBrutto").Name ReportAbrechnung.Sections("Bereich5").Controls("lblNetto").Caption = "Netto" ReportAbrechnung.Sections("Bereich5").Controls("FunktionSummeNetto").DataField = rsOffeneTische.Fields("SummeNetto").Name ReportAbrechnung.Sections("Bereich5").Controls("lblMwSt").Caption = "Enthaltene MwSt. 19%" ReportAbrechnung.Sections("Bereich5").Controls("FunktionSummeMwSt").DataField = rsOffeneTische.Fields("SummeMwSt").Name ReportAbrechnung.Sections("Bereich5").Controls("FunktionSummeBrutto").DataField = rsOffeneTische.Fields("SummeBrutto").Name VarSummeBrutto = ReportAbrechnung.Sections("Bereich5").Controls("FunktionSummeBrutto").DataField ReportAbrechnung.Sections("Bereich5").Controls("lblBedanken").Caption = "Vielen Dank für Ihren Besuch." 'ReportAbrechnung.Show 1 Me.PrintReport 10 Set objConn = Nothing Set rsOffeneTische = Nothing exit_Sub: On Error GoTo 0 Set rsOffeneTische = Nothing Set objConn = Nothing Exit Function err_Handler: MsgBox "Fehlernummer " & Err.Number & Chr$(13) & Error$(Err), _ vbCritical, "Fehler" Resume exit_Sub Set rsOffeneTische = Nothing Set objConn = Nothing End Function Sub RechnungDetails() Dim objConn As ADODB.Connection Dim rsRegNr As ADODB.Recordset Dim rsRechnungsuebersicht As ADODB.Recordset Dim rsOffeneTische As ADODB.Recordset Dim rsRechnungDetails As ADODB.Recordset Dim strPath As String Set objConn = New ADODB.Connection Set rsRegNr = New ADODB.Recordset Set rsRechnungsuebersicht = New ADODB.Recordset Set rsOffeneTische = New ADODB.Recordset Set rsRechnungDetails = New ADODB.Recordset On Error GoTo err_Handler strPath = App.Path If Right$(strPath, 1) <> "\" Then strPath = strPath & "\" With objConn .Provider = "Microsoft Jet 4.0 OLE DB Provider" .Properties("Jet OLEDB:Database Password") = VarPasswordDatenbank .ConnectionString = "Data Source=" & strPath & "asql.dat" .CursorLocation = adUseClient .Open End With 'Tabelle Rechnungsuebersicht öffnen With rsRegNr .ActiveConnection = objConn .CursorLocation = adUseClient .LockType = adLockOptimistic .Open "Select max(RegNr) FROM Rechnungsuebersicht" End With Set frmSchnellwahltaste.MSHFlexGrid1.DataSource = rsRegNr MsgBox frmSchnellwahltaste.MSHFlexGrid1.Text 'Tabelle Rechnungsuebersicht öffnen With rsRechnungsuebersicht .ActiveConnection = objConn .CursorLocation = adUseClient .LockType = adLockOptimistic .Open "Select ID,RegNr,Datum,Zeit,Station,Kellner,Gesamt FROM Rechnungsuebersicht" End With If Not rsRechnungsuebersicht.EOF Then rsRechnungsuebersicht.AddNew rsRechnungsuebersicht.Fields("RegNr") = frmSchnellwahltaste.MSHFlexGrid1.Text + 1 rsRechnungsuebersicht.Fields("Datum") = Date rsRechnungsuebersicht.Fields("Zeit") = Time rsRechnungsuebersicht.Fields("Station") = VarStation rsRechnungsuebersicht.Fields("Mitarbeiter") = VarKellner rsRechnungsuebersicht.Fields("Gesamt") = VarSummeBrutto rsRechnungsuebersicht.Update End If 'Tabelle OffeneTische öffnen With rsOffeneTische .ActiveConnection = objConn .CursorLocation = adUseClient .LockType = adLockOptimistic .Open "Select *from OffeneTische" ' Where Station = '" & VarStation & "' and Kellner='" & VarKellner & "' and Tisch='" & VarTisch & "'" End With 'Tabelle RechnungsDetails öffnen With rsRechnungDetails .ActiveConnection = objConn .CursorLocation = adUseClient .LockType = adLockOptimistic .Open "Select *FROM RechnungDetails" End With While Not rsOffeneTische.EOF rsRechnungDetails.AddNew For I = 4 To rsOffeneTische.Fields.Count - 1 rsRechnungDetails.Fields(I).Value = rsOffeneTische.Fields(I).Value Next I rsRechnungDetails.Fields("RegNr").Value = frmSchnellwahltaste.MSHFlexGrid1.Text = "" frmSchnellwahltaste.MSHFlexGrid1.Text = "" rsOffeneTische.Delete rsOffeneTische.MoveNext Wend rsRechnungDetails.Update rsRechnungDetails.Requery Set objConn = Nothing Set rsOffeneTische = Nothing Set rsRechnungDetails = Nothing exit_Sub: On Error GoTo 0 Set rsOffeneTische = Nothing Set rsRechnungDetails = Nothing Set objConn = Nothing Exit Sub err_Handler: MsgBox "Fehlernummer " & Err.Number & Chr$(13) & Error$(Err), _ vbCritical, "Fehler" Resume exit_Sub Set rsOffeneTische = Nothing Set rsRechnungDetails = Nothing Set objConn = Nothing End Sub