S
SuzyQ
I'm getting the error message 3086 when I try to run the following code -
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"
DoCmd.RunSQL (strSQL)
The code above results in the following value for strSQL when the error
occurs...
DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null
When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.
The entire routine is below
Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table
'delete the records added during this run of report
Dim strSQL As String
'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If
'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If
'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If
DoCmd.RunSQL (strSQL)
If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"
DoCmd.RunSQL (strSQL)
End If
End If
If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"
DoCmd.RunSQL (strSQL)
The code above results in the following value for strSQL when the error
occurs...
DELETE [tblActivitiesForSchedule].* FROM tblActivitiesForSchedule left join
tblProjectSchedule on tblProjectSchedule.ActivityID =
tblActivitiesForSchedule.ActivityID Where tblProjectSchedule.ActivityID is
null
When I put these results in the query builder, I get the same error, but
when I change the query builder from a delete query to a select query, there
are records that come up and need to be deleted. How can I delete the
records from the parent table after the child table's records have been
deleted? - and avoid the error message that Access cannot delete the records.
Thanks.
The entire routine is below
Sub deleteOld(t1 As String, f1 As String, dType As String, Optional t2 As
String, Optional isNumber As Boolean, _
Optional t1field As String, Optional t2field As String)
'Parameter description
't1 is the name of the table with a date to delete
'f1 is the field name to look for
'dType is the value type - "DT" = date information; "FY" = fiscal Year
information; and "FM" = fiscal Month information
't2 is an parent table if the date value came from a child table
'isNumber identifies whether the FY and FM types are number or text true
indicates number
't1field is the field from t1 that is a foreign key field that relates
to t2 table
't2field is the field from t2 that is a primary key field that relates
to t1 table
'delete the records added during this run of report
Dim strSQL As String
'the field value of the "date" field is a date type
If dType = "DT" Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= #" & freezeDate & "#"
End If
'the field value of the date field is for (fiscal year or fiscal month)
and is a number type
If (dType = "FY" Or dType = "FM") And isNumber = True Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & f1 & " <= " & Val(Me.txtFY)
End If
'the field value of the date field is for (fiscal year or fiscal month)
and is a text type
If (dType = "FY" Or dType = "FM") And isNumber = False Then
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t1 & " "
strSQL = strSQL & "WHERE " & Val(f1) & " <= " & Val(Me.txtFY)
End If
DoCmd.RunSQL (strSQL)
If Len(t2) <> 0 Then 'there is a parent table for this child table and
parent records may need to be deleted also
If IsNull(t1field) Or IsNull(t2field) Then
MsgBox "Missing Parameter t1field or t2field"
Else
'has all the information to relate the tables and delete from
the parent table
strSQL = "DELETE * "
strSQL = strSQL & "FROM " & t2 & " left join " & t1 & " on " &
t1 & "." & t1field & " = " & t2 & "." & t2field & " "
strSQL = strSQL & "Where " & t1 & "." & t1field & " is null"
DoCmd.RunSQL (strSQL)
End If
End If
If dType <> "FM" And dType <> "DT" And dType <> "FY" Then
MsgBox "Scenario not accounted for, check code for " & t1
End If
End Sub