A2002: need help with form scenario

  • Thread starter =?windows-1250?Q?Vladim=EDr_Cvajniga?=
  • Start date
?

=?windows-1250?Q?Vladim=EDr_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?
 
?

=?windows-1250?Q?Vladim=EDr_Cvajniga?=

P.P.P.S.
The error I'm getting, when I try to delete record(s), is 3420 - Object
invalid or no longer set.
 
?

=?windows-1250?Q?Vladim=EDr_Cvajniga?=

Ooops,
with DLookups 2b) can't find a way to delete records only from DPH

belongs to
1) without DLookups

V.

Vladimír Cvajniga said:
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?
 
?

=?windows-1250?Q?Vladim=EDr_Cvajniga?=

Sometimes, when I try to delete a record, I get a message: Record is
deleted.
 
Top