Attempt to delete records causes errors 3086 or 3131

[Ann in CA]

Original code:

strCommand = "DELETE tblCMValues.* FROM qryCMValues WHERE [CMID] = " &
strZeroID & " AND [Type] = """ & strType & """"

However, Type is in a different table in the query, so I was getting error
3086 "Could not delete from specified tables." My attempt to fix, based on
responses found on this board:

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues (INNERJOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID) WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

This causes Error 3131. So I read a bit more and tried another approach,
which doesn't execute at all:

strCommand = "DELETE DistinctRow tblCMValues.* " FROM tblCMValues WHERE
[CMID] = " & strZeroID & " AND tblCMValues.measID IN (Select MeasAutoID FROM
tblMeasurements WHERE [Type] = """ & strType & """)"

If anyone knows how to fix this, they would have my utmost respect and
gratitude =)

 

[John W. Vinson]

Original code:

strCommand = "DELETE tblCMValues.* FROM qryCMValues WHERE [CMID] = " &
strZeroID & " AND [Type] = """ & strType & """"

However, Type is in a different table in the query, so I was getting error
3086 "Could not delete from specified tables." My attempt to fix, based on
responses found on this board:

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues (INNERJOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID) WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

This causes Error 3131. So I read a bit more and tried another approach,
which doesn't execute at all:

strCommand = "DELETE DistinctRow tblCMValues.* " FROM tblCMValues WHERE
[CMID] = " & strZeroID & " AND tblCMValues.measID IN (Select MeasAutoID FROM
tblMeasurements WHERE [Type] = """ & strType & """)"

If anyone knows how to fix this, they would have my utmost respect and
gratitude =)

What's the SQL of qryCMValues? How is tblCMValues related to the table which
contains Type? Are you perhaps another victim of the dreaded Lookup Field
(i.e. is Type a Lookup field in tblCMValues?

FWIW the correct syntax would be more like

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues INNER JOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

and this will only work if MeasAutoID is the Primary Key of tblMeasurements,
and you also have a relationship between MeasAutoID and MeasID, with
relational integrity enforced.

John W. Vinson [MVP]

 

[Ann in CA]

I believe my relationships are that way--but trying the line like that gives
a syntax error so I cannot verify it?

Original code:

strCommand = "DELETE tblCMValues.* FROM qryCMValues WHERE [CMID] = " &
strZeroID & " AND [Type] = """ & strType & """"

However, Type is in a different table in the query, so I was getting error
3086 "Could not delete from specified tables." My attempt to fix, based on
responses found on this board:

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues (INNERJOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID) WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

This causes Error 3131. So I read a bit more and tried another approach,
which doesn't execute at all:

strCommand = "DELETE DistinctRow tblCMValues.* " FROM tblCMValues WHERE
[CMID] = " & strZeroID & " AND tblCMValues.measID IN (Select MeasAutoID FROM
tblMeasurements WHERE [Type] = """ & strType & """)"

If anyone knows how to fix this, they would have my utmost respect and
gratitude =)

What's the SQL of qryCMValues? How is tblCMValues related to the table which
contains Type? Are you perhaps another victim of the dreaded Lookup Field
(i.e. is Type a Lookup field in tblCMValues?

FWIW the correct syntax would be more like

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues INNER JOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

and this will only work if MeasAutoID is the Primary Key of tblMeasurements,
and you also have a relationship between MeasAutoID and MeasID, with
relational integrity enforced.

John W. Vinson [MVP]

 

[John W. Vinson]

I believe my relationships are that way--but trying the line like that gives
a syntax error so I cannot verify it?

What are the datatypes of CMID and Type? I gather that CMID is Number and Type
is Text? If you use the debugger, what value is actually being assigned to
strCommand? What line is highlighted with the syntax error?

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues INNER JOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

John W. Vinson [MVP]

 

[Ann in CA]

Sorry there was an erroneous quote between inner join and tblMeasurements
causing syntax error. After remoing that, I get:

3075 Syntax error in string in query expressing '[CMID] = 2 And [Type] =
"Neutrals""".

CMID is a number (autoID from other table) and Type is text field.
(strZeroID represents the CMID.)

I believe my relationships are that way--but trying the line like that gives
a syntax error so I cannot verify it?

What are the datatypes of CMID and Type? I gather that CMID is Number and Type
is Text? If you use the debugger, what value is actually being assigned to
strCommand? What line is highlighted with the syntax error?

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues INNER JOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

John W. Vinson [MVP]

 

[John W. Vinson]

Sorry there was an erroneous quote between inner join and tblMeasurements
causing syntax error. After remoing that, I get:

3075 Syntax error in string in query expressing '[CMID] = 2 And [Type] =
"Neutrals""".

Looks like you have an extra closing " after the Type variable. Not seeing
your code makes it a bit hard to help debug!

John W. Vinson [MVP]

 

[Ann in CA]

You're awesome! For some reason, when I pasted the lines in from your last
post, it threw on 2 extra quotes. Removing those, it worked great!

The code was if the user confirmed via msgbox that they wanted to delete all
values, docmd.runsql strCommand where strCommand is as defined:

strCommand = "DELETE DistinctRow tblCMValues.* FROM tblCMValues INNER JOIN
tblMeasurements ON tblCMValues.MeasID = tblMeasurements.MeasAutoID WHERE
[CMID] = " & strZeroID & " AND [Type] = """ & strType & """"

Thanks soooo much!

Sorry there was an erroneous quote between inner join and tblMeasurements
causing syntax error. After remoing that, I get:

3075 Syntax error in string in query expressing '[CMID] = 2 And [Type] =
"Neutrals""".

Looks like you have an extra closing " after the Type variable. Not seeing
your code makes it a bit hard to help debug!

John W. Vinson [MVP]