Ooops,
with DLookups 2b) can't find a way to delete records only from DPH
belongs to
1) without DLookups
V.
"Vladimír Cvajniga" <(E-Mail Removed)> píše v diskusním pøíspìvku
news:(E-Mail Removed)...
> 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?
>
Similar Threads
