V
Vladimír Cvajniga
For one of form's subforms I have a query with 3 tables:
SELECT Kniha.DATUZP, DPH.*, Val(Left([DPH].[TypDA],1))<5 AS Vstup, Abs(Not
(((Left([KNIHA].[TypDokl],1))="Z") And ((Left([DPH].[TYPDA],1)) Between "5"
And "8"))) AS bSoucet, [DPH].[ZAKLAD]*[bSoucet] AS sumZAKLAD,
[DPH].[DAN]*[bSoucet] AS sumDAN, [DPH].[SDANI]*[bSoucet] AS sumSDANI,
Year([DATUZP]) AS rokDoklad, Par3Uc02.RokUc AS rokParam,
DateSerial(Year([Kniha].[DATUZP]),Month([Kniha].[DATUZP]),1) AS zacDoklad,
DateSerial([Par3Uc02].[RokUC],[Par3Uc02].[MesUc],1) AS zacParam,
([Vstup]=True) And (([rokDoklad]<>[rokParam]) Or ([zacDoklad]>[zacParam]))
AS b1, ([Vstup]=False) And ([zacDoklad]<>[zacParam]) AS b2
FROM Par3Uc02, DPH INNER JOIN Kniha ON DPH.ID_Kniha = Kniha.ID_Kniha
ORDER BY DPH.SAZBA;
Kniha: lookup table
Par3Uc02: parameter table (1 record)
DPH: main subform's data table
Calculated fields sumZaklad, sumDAN, sumZaklad are summed in form's header
text-boxes:
Text32: =Sum([sumZAKLAD])
Text33: =Sum([sumDAN])
txtSumSdani: =Sum([sumSDANI])
Calculated fields b1 and b2 affect conditional formatting of some
text-boxes. The formatting is based on an expression:
[b1] Or [b2]
I need b1 and b2 for validation rules as well.
Subform's RecordsetType is set to 1, ie. Dynaset (Inconsistent Updates).
————————————————————————————————————
What I need:
1) have the sums in subform's header
2) when record(s) is (are) deleted: delete only record(s) in main data table
(DPH) and leave lookup table (Kniha) and parameter table (Par3Uc02)
untouched
3) avoid DLookups in query because they significantly slow down perfomance:
on my slow machine it takes <2.5 seconds to open (very complex) form
without DLookups, >5 seconds with DLookups; this is a "DLookups" query:
SELECT DLookUp("DATUZP","Kniha","ID_Kniha=" & CStr([DPH].[ID_Kniha])) AS
DATUZP, Left(DLookUp("TypDokl","Kniha","ID_Kniha=" &
CStr([DPH].[ID_Kniha])),1) AS TypDokl1, DPH.*, Val(Left([DPH].[TypDA],1))<5
AS Vstup, Abs(Not ((([TypDokl1])="Z") And ((Left([DPH].[TYPDA],1)) Between
"5" And "8"))) AS bSoucet, [DPH].[ZAKLAD]*[bSoucet] AS sumZAKLAD,
[DPH].[DAN]*[bSoucet] AS sumDAN, [DPH].[SDANI]*[bSoucet] AS sumSDANI,
Year([DATUZP]) AS rokDoklad, DLookUp("RokUc","Par3Uc02") AS rokParam,
DLookUp("MesUc","Par3Uc02") AS mesParam,
DateSerial(Year([DATUZP]),Month([DATUZP]),1) AS zacDoklad,
DateSerial([rokParam],[mesParam],1) AS zacParam, ([Vstup]=True) And
(([rokDoklad]<>[rokParam]) Or ([zacDoklad]>[zacParam])) AS b1,
([Vstup]=False) And ([zacDoklad]<>[zacParam]) AS b2
FROM DPH
ORDER BY DPH.SAZBA;
————————————————————————————————————
The problems I have:
1) without DLookups: DELETE deletes records in all three tables
2) with DLookups:
a) slow performance
b) can't find a way to delete records only from DPH and leave Kniha &
Par3Uc02 untouched (see "My scenario" below); I have a function selSQL (see
below) to create a filter for DELETE query (only from selected record(s)),
ie. "DELETE * from DPH WHERE " & strFilter, but I'm not sure which Before*
and After* event procedures should I use and how
3) there might be a problem with Par3Uc02 with only one record;
when I test deletion: first deletion seems to perform OK, next
deletion(s) give(s) me an error message while creating filter - debug stops
at .MoveFirst in selSQL (variable not set)
Public Function selSQL(frm As Variant, fldName As String, Optional apostrof
As Boolean) As String
Dim a As String, sq As String, rs As DAO.Recordset, i As Integer
a = IIf(apostrof, "'", "")
'jestliže je oznaèené pouze nová vìta, tak EXIT
If (frm.SelHeight < 2 And frm.NewRecord) Then Exit Function
If frm.SelHeight = 0 Then
selSQL = "(" & fldName & "=" & a & CStr(frm.Controls(fldName).Value) &
a & ")"
Exit Function
End If
sq = ""
Set rs = frm.RecordsetClone
With rs
.MoveFirst
.Move frm.SelTop - 1
For i = 1 To frm.SelHeight
If Not .EOF Then
sq = sq & a & CStr(rs(fldName).Value) & a & ","
.MoveNext
End If
Next i
.Close
End With
Set rs = Nothing
sq = Left(sq, Len(sq) - 1)
sq = fldName & " in (" & sq & ")"
selSQL = sq
End Function
————————————————————————————————————
My scenario:
1) create filter sqDelete:
Private Sub Form_Delete(Cancel As Integer)
sqDelete = selSQL(Me, "ID_DPH")
End Sub
----------------
2a)
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
Cancel = True
If MsgBox("Do you want to delete record(s)?", vbYesNo + vbQuestion) =
vbYes Then
'create DELETE query with sqDelete as filter
'DoCmd.RunSQL strDeleteQuery
End If
End Sub
or
2b)
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
'create DELETE query with sqDelete as filter
'DoCmd.RunSQL strDeleteQuery
Else
Status = acDeleteCancel
End If
End Sub
I think that 1) & 2b) should do what I want but I'm quite unsure... :-/
————————————————————————————————————
TYVM in advance.
Vlado
P.S. Is there any way to create similar query, ie. with more than one table,
which (on deletion) doesn't delete record(s) in all tables?
SELECT Kniha.DATUZP, DPH.*, Val(Left([DPH].[TypDA],1))<5 AS Vstup, Abs(Not
(((Left([KNIHA].[TypDokl],1))="Z") And ((Left([DPH].[TYPDA],1)) Between "5"
And "8"))) AS bSoucet, [DPH].[ZAKLAD]*[bSoucet] AS sumZAKLAD,
[DPH].[DAN]*[bSoucet] AS sumDAN, [DPH].[SDANI]*[bSoucet] AS sumSDANI,
Year([DATUZP]) AS rokDoklad, Par3Uc02.RokUc AS rokParam,
DateSerial(Year([Kniha].[DATUZP]),Month([Kniha].[DATUZP]),1) AS zacDoklad,
DateSerial([Par3Uc02].[RokUC],[Par3Uc02].[MesUc],1) AS zacParam,
([Vstup]=True) And (([rokDoklad]<>[rokParam]) Or ([zacDoklad]>[zacParam]))
AS b1, ([Vstup]=False) And ([zacDoklad]<>[zacParam]) AS b2
FROM Par3Uc02, DPH INNER JOIN Kniha ON DPH.ID_Kniha = Kniha.ID_Kniha
ORDER BY DPH.SAZBA;
Kniha: lookup table
Par3Uc02: parameter table (1 record)
DPH: main subform's data table
Calculated fields sumZaklad, sumDAN, sumZaklad are summed in form's header
text-boxes:
Text32: =Sum([sumZAKLAD])
Text33: =Sum([sumDAN])
txtSumSdani: =Sum([sumSDANI])
Calculated fields b1 and b2 affect conditional formatting of some
text-boxes. The formatting is based on an expression:
[b1] Or [b2]
I need b1 and b2 for validation rules as well.
Subform's RecordsetType is set to 1, ie. Dynaset (Inconsistent Updates).
————————————————————————————————————
What I need:
1) have the sums in subform's header
2) when record(s) is (are) deleted: delete only record(s) in main data table
(DPH) and leave lookup table (Kniha) and parameter table (Par3Uc02)
untouched
3) avoid DLookups in query because they significantly slow down perfomance:
on my slow machine it takes <2.5 seconds to open (very complex) form
without DLookups, >5 seconds with DLookups; this is a "DLookups" query:
SELECT DLookUp("DATUZP","Kniha","ID_Kniha=" & CStr([DPH].[ID_Kniha])) AS
DATUZP, Left(DLookUp("TypDokl","Kniha","ID_Kniha=" &
CStr([DPH].[ID_Kniha])),1) AS TypDokl1, DPH.*, Val(Left([DPH].[TypDA],1))<5
AS Vstup, Abs(Not ((([TypDokl1])="Z") And ((Left([DPH].[TYPDA],1)) Between
"5" And "8"))) AS bSoucet, [DPH].[ZAKLAD]*[bSoucet] AS sumZAKLAD,
[DPH].[DAN]*[bSoucet] AS sumDAN, [DPH].[SDANI]*[bSoucet] AS sumSDANI,
Year([DATUZP]) AS rokDoklad, DLookUp("RokUc","Par3Uc02") AS rokParam,
DLookUp("MesUc","Par3Uc02") AS mesParam,
DateSerial(Year([DATUZP]),Month([DATUZP]),1) AS zacDoklad,
DateSerial([rokParam],[mesParam],1) AS zacParam, ([Vstup]=True) And
(([rokDoklad]<>[rokParam]) Or ([zacDoklad]>[zacParam])) AS b1,
([Vstup]=False) And ([zacDoklad]<>[zacParam]) AS b2
FROM DPH
ORDER BY DPH.SAZBA;
————————————————————————————————————
The problems I have:
1) without DLookups: DELETE deletes records in all three tables
2) with DLookups:
a) slow performance
b) can't find a way to delete records only from DPH and leave Kniha &
Par3Uc02 untouched (see "My scenario" below); I have a function selSQL (see
below) to create a filter for DELETE query (only from selected record(s)),
ie. "DELETE * from DPH WHERE " & strFilter, but I'm not sure which Before*
and After* event procedures should I use and how
3) there might be a problem with Par3Uc02 with only one record;
when I test deletion: first deletion seems to perform OK, next
deletion(s) give(s) me an error message while creating filter - debug stops
at .MoveFirst in selSQL (variable not set)
Public Function selSQL(frm As Variant, fldName As String, Optional apostrof
As Boolean) As String
Dim a As String, sq As String, rs As DAO.Recordset, i As Integer
a = IIf(apostrof, "'", "")
'jestliže je oznaèené pouze nová vìta, tak EXIT
If (frm.SelHeight < 2 And frm.NewRecord) Then Exit Function
If frm.SelHeight = 0 Then
selSQL = "(" & fldName & "=" & a & CStr(frm.Controls(fldName).Value) &
a & ")"
Exit Function
End If
sq = ""
Set rs = frm.RecordsetClone
With rs
.MoveFirst
.Move frm.SelTop - 1
For i = 1 To frm.SelHeight
If Not .EOF Then
sq = sq & a & CStr(rs(fldName).Value) & a & ","
.MoveNext
End If
Next i
.Close
End With
Set rs = Nothing
sq = Left(sq, Len(sq) - 1)
sq = fldName & " in (" & sq & ")"
selSQL = sq
End Function
————————————————————————————————————
My scenario:
1) create filter sqDelete:
Private Sub Form_Delete(Cancel As Integer)
sqDelete = selSQL(Me, "ID_DPH")
End Sub
----------------
2a)
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
Cancel = True
If MsgBox("Do you want to delete record(s)?", vbYesNo + vbQuestion) =
vbYes Then
'create DELETE query with sqDelete as filter
'DoCmd.RunSQL strDeleteQuery
End If
End Sub
or
2b)
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
'create DELETE query with sqDelete as filter
'DoCmd.RunSQL strDeleteQuery
Else
Status = acDeleteCancel
End If
End Sub
I think that 1) & 2b) should do what I want but I'm quite unsure... :-/
————————————————————————————————————
TYVM in advance.
Vlado
P.S. Is there any way to create similar query, ie. with more than one table,
which (on deletion) doesn't delete record(s) in all tables?