A2K3- quick SQL help needed

[Deano]

This is the raw SQL from a saved delete query - I need to assign this to a
string so I can execute it using DoCmd.RunSQL. The value of 1 needs to be
replaced with a reference to Me.txtPupilID. Other than that I can't get the
syntax correct. Can anyone correct it for me, it's a bit trial and error
here....

DELETE tblPupilIncidentLink.*, tblPupilIncidentLink.PupilID,
tblPupilIncidentLink.IncidentID
FROM tblPupilIncidentLink
WHERE (((tblPupilIncidentLink.PupilID)=1) AND
((tblPupilIncidentLink.IncidentID)=[forms]![frmIncidentsListBox_version].[In
cidentID]));

[Linda Burnside]

Try this:

Dim strSQL

strSQL = "DELETE tblPupilIncidentLink.PupilID " & _
" FROM tblPupilIncidentLink " & _
"WHERE (((tblPupilIncidentLink.PupilID)=1) " & _
"((tblPupilIncidentLink.IncidentID)=" Me.txtPupilID

DoCmd.RunSQL strSQL

Linda

[Deano]

Linda Burnside wrote:

Try this:

Dim strSQL

strSQL = "DELETE tblPupilIncidentLink.PupilID " & _
" FROM tblPupilIncidentLink " & _
"WHERE (((tblPupilIncidentLink.PupilID)=1) " & _
"((tblPupilIncidentLink.IncidentID)=" Me.txtPupilID

DoCmd.RunSQL strSQL

Linda

Don't think that will work as Me.txtPupilID goes where the 1 is, and I need
to compare the IncidentID with that of the current record, i.e
[forms]![frmIncidentsListBox_version].[IncidentID].

[Linda Burnside]

Sorry ... I misunderstood.

In that case, try something like:

strSQL = "DELETE tblPupilIncidentLink.PupilID " & _
" FROM tblPupilIncidentLink " & _
"WHERE (((tblPupilIncidentLink.PupilID)=" & Me.txtPupilID & ") AND " & _
"((tblPupilIncidentLink.IncidentID)=" &
[forms]![frmIncidentsListBox_version].[IncidentID]

If the IncentID is contained in a subform, you may want use
Me![frmIncidentsListBox_version].Form![IncidentID] (reference the control as
part of a subform) instead of
[forms]![frmIncidentsListBox_version].[IncidentID]

Linda

"Deano" <de***@mailinator.com> wrote in message
news:43***********************@ptn-nntp-reader04.plus.net... Linda Burnside wrote:

Try this:

Dim strSQL

strSQL = "DELETE tblPupilIncidentLink.PupilID " & _
" FROM tblPupilIncidentLink " & _
"WHERE (((tblPupilIncidentLink.PupilID)=1) " & _
"((tblPupilIncidentLink.IncidentID)=" Me.txtPupilID

DoCmd.RunSQL strSQL

Linda

Don't think that will work as Me.txtPupilID goes where the 1 is, and I
need
to compare the IncidentID with that of the current record, i.e
[forms]![frmIncidentsListBox_version].[IncidentID].

[Deano]

Linda Burnside wrote:

Sorry ... I misunderstood.

In that case, try something like:

strSQL = "DELETE tblPupilIncidentLink.PupilID " & _
" FROM tblPupilIncidentLink " & _
"WHERE (((tblPupilIncidentLink.PupilID)=" & Me.txtPupilID & ") AND
" & _ "((tblPupilIncidentLink.IncidentID)=" &
[forms]![frmIncidentsListBox_version].[IncidentID]

If the IncentID is contained in a subform, you may want use
Me![frmIncidentsListBox_version].Form![IncidentID] (reference the
control as part of a subform) instead of
[forms]![frmIncidentsListBox_version].[IncidentID]

Thanks Linda, I tweaked that slightly to give me this and it worked;

strSQL = "DELETE tblPupilIncidentLink.*, tblPupilIncidentLink.PupilID,
tblPupilIncidentLink.IncidentID" & " FROM tblPupilIncidentLink " & "WHERE
(((tblPupilIncidentLink.PupilID)=" & Me.txtPupilID & ") AND " &
"((tblPupilIncidentLink.IncidentID)=" &
[Forms]![frmIncidentsListBox_version].[IncidentID] & "));"