Delete record !

  • Thread starter Thread starter luanhoxung
  • Start date Start date
L

luanhoxung

Hi, All !
I have a trouble with delete some record though VBA,
I need delete some record in "many" table with relationship 'one-
many'.
The Error coming is: "Cannot Update. Database or Object is read-ony"
Can anybody help me here! I know less VBA !
Thanks in advance.

Here is the code:

Dim Delrs as DAO.Recordset
Dim StrDel as string

StrDel = "SELECT DISTINCTROW Tremaishipment.SOHD, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN (Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH) AND (Tchitiethdx.SOHD =
Tremainshipment.SOHD)) ON Thopdongxuat.SOHD = Tchitiethdx.SOHD" _
& " GROUP BY Tremainshipment.SOHD, Tremainshipment.MAHH,
Tchitiethdx.SLUONG, Thopdongxuat.SAISO" _
& " having ((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].
[sluong]*[thopdongxuat].[saiso]);"

Set Delrs = CurrentDb.OpenRecordset(StrDel)
If Delrs.RecordCount > 0 Then
Delrs.Delete
Delrs.Update
End If
Set rsT = Nothing
 
SELECT DISTINCTROW Tremaishipment.SOHD
, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG, Thopdongxuat.SAISO
HAVING ((Sum(Tremainshipment.SLUONG))<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])

You have grouped records together. So DELETE cannot identify a single
record to delete.
You need to change your query and the recordset it generates. I can't tell
how to do that from your post, but as a guess you will need a subquery in a
where clause. Also, I would probably just use a delete query instead of
stepping through a group of records.

An example M I G H T be as follows. If you wanted to delete records from
the table Tremaishipment based on the field SOHD meeting the requirements of
your query.
DELETE
FROM Tremaishipment
WHERE Tremaishipment.SOHD IN
(
SELECT Tremaishipment.SOHD
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG
, Thopdongxuat.SAISO
HAVING Sum(Tremainshipment.SLUONG<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])

WARNING: BACK UP your data first before testing the query. The example will
probably delete more records than you want deleted.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT DISTINCTROW Tremaishipment.SOHD
, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG, Thopdongxuat.SAISO
HAVING ((Sum(Tremainshipment.SLUONG))<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])

You have grouped records together. So DELETE cannot identify a single
record to delete.
You need to change your query and the recordset it generates. I can't tell
how to do that from your post, but as a guess you will need a subquery in a
where clause. Also, I would probably just use a delete query instead of
stepping through a group of records.

An example M I G H T be as follows. If you wanted to delete records from
the table Tremaishipment based on the field SOHD meeting the requirements of
your query.
DELETE
FROM Tremaishipment
WHERE Tremaishipment.SOHD IN
(
SELECT Tremaishipment.SOHD
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG
, Thopdongxuat.SAISO
HAVING Sum(Tremainshipment.SLUONG<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])

WARNING: BACK UP your data first before testing the query. The example will
probably delete more records than you want deleted.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




Hi, All !
I have a trouble with delete some record though VBA,
I need delete some record in "many" table with relationship 'one-
many'.
The Error coming is: "Cannot Update. Database or Object is read-ony"
Can anybody help me here! I know less VBA !
Thanks in advance.
Click to expand...
Here is the code:
Click to expand...
Dim Delrs as DAO.Recordset
Dim StrDel as string
Click to expand...
StrDel = "SELECT DISTINCTROW Tremaishipment.SOHD, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN (Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH) AND (Tchitiethdx.SOHD =
Tremainshipment.SOHD)) ON Thopdongxuat.SOHD = Tchitiethdx.SOHD" _
& " GROUP BY Tremainshipment.SOHD, Tremainshipment.MAHH,
Tchitiethdx.SLUONG, Thopdongxuat.SAISO" _
& " having ((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].
[sluong]*[thopdongxuat].[saiso]);"
Click to expand...
Set Delrs = CurrentDb.OpenRecordset(StrDel)
If Delrs.RecordCount > 0 Then
Delrs.Delete
Delrs.Update
End If
Set rsT = Nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi, John.
Thanks ur response.
You're right, It deletes more records than I expected.
But I think we can approach this matter by other way.
Can u teach me how to check the ONLY ONE value of SOHD exist in table
Tremainshippment in VBA?
Then basing on this result, I will delete them with Criteria " WHERE
((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].[sluong]*[thopdongxuat].
[saiso]);"

Thanks all ur helps.
Luan from VietNam.
 
If I understand you, the following should give you all the SOHD values that
exist only one time in the Tremainshipment table

SELECT SOHD
FROM Tremainshipment
WHERE SOHD IN
(SELECT SOHD
FROM Tremainshipment
GROUP BY SOHD
HAVING Count(SOHD) = 1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

luan said:
SELECT DISTINCTROW Tremaishipment.SOHD
, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG, Thopdongxuat.SAISO
HAVING ((Sum(Tremainshipment.SLUONG))<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])

You have grouped records together. So DELETE cannot identify a single
record to delete.
You need to change your query and the recordset it generates. I can't
tell
how to do that from your post, but as a guess you will need a subquery in
a
where clause. Also, I would probably just use a delete query instead of
stepping through a group of records.

An example M I G H T be as follows. If you wanted to delete records from
the table Tremaishipment based on the field SOHD meeting the requirements
of
your query.
DELETE
FROM Tremaishipment
WHERE Tremaishipment.SOHD IN
(
SELECT Tremaishipment.SOHD
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG
, Thopdongxuat.SAISO
HAVING Sum(Tremainshipment.SLUONG<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])

WARNING: BACK UP your data first before testing the query. The example
will
probably delete more records than you want deleted.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




Hi, All !
I have a trouble with delete some record though VBA,
I need delete some record in "many" table with relationship 'one-
many'.
The Error coming is: "Cannot Update. Database or Object is read-ony"
Can anybody help me here! I know less VBA !
Thanks in advance.
Click to expand...
Here is the code:
Click to expand...
Dim Delrs as DAO.Recordset
Dim StrDel as string
Click to expand...
StrDel = "SELECT DISTINCTROW Tremaishipment.SOHD, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN (Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH) AND (Tchitiethdx.SOHD =
Tremainshipment.SOHD)) ON Thopdongxuat.SOHD = Tchitiethdx.SOHD" _
& " GROUP BY Tremainshipment.SOHD, Tremainshipment.MAHH,
Tchitiethdx.SLUONG, Thopdongxuat.SAISO" _
& " having ((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].
[sluong]*[thopdongxuat].[saiso]);"
Click to expand...
Set Delrs = CurrentDb.OpenRecordset(StrDel)
If Delrs.RecordCount > 0 Then
Delrs.Delete
Delrs.Update
End If
Set rsT = Nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi, John.
Thanks ur response.
You're right, It deletes more records than I expected.
But I think we can approach this matter by other way.
Can u teach me how to check the ONLY ONE value of SOHD exist in table
Tremainshippment in VBA?
Then basing on this result, I will delete them with Criteria " WHERE
((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].[sluong]*[thopdongxuat].
[saiso]);"

Thanks all ur helps.
Luan from VietNam.
Click to expand...
 
If I understand you, the following should give you all the SOHD values that
exist only one time in the Tremainshipment table

SELECT SOHD
FROM Tremainshipment
WHERE SOHD IN
(SELECT SOHD
FROM Tremainshipment
GROUP BY SOHD
HAVING Count(SOHD) = 1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




SELECT DISTINCTROW Tremaishipment.SOHD
, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG, Thopdongxuat.SAISO
HAVING ((Sum(Tremainshipment.SLUONG))<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])
You have grouped records together. So DELETE cannot identify a single
record to delete.
You need to change your query and the recordset it generates. I can't
tell
how to do that from your post, but as a guess you will need a subquery in
a
where clause. Also, I would probably just use a delete query instead of
stepping through a group of records.
An example M I G H T be as follows. If you wanted to delete records from
the table Tremaishipment based on the field SOHD meeting the requirements
of
your query.
DELETE
FROM Tremaishipment
WHERE Tremaishipment.SOHD IN
(
SELECT Tremaishipment.SOHD
FROM Thopdongxuat INNER JOIN
(Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH)
AND (Tchitiethdx.SOHD =Tremainshipment.SOHD))
ON Thopdongxuat.SOHD = Tchitiethdx.SOHD
GROUP BY Tremainshipment.SOHD
, Tremainshipment.MAHH
, Tchitiethdx.SLUONG
, Thopdongxuat.SAISO
HAVING Sum(Tremainshipment.SLUONG<=
[tchitiethdx].[sluong]*[thopdongxuat].[saiso])
WARNING: BACK UP your data first before testing the query. The example
will
probably delete more records than you want deleted.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi, All !
I have a trouble with delete some record though VBA,
I need delete some record in "many" table with relationship 'one-
many'.
The Error coming is: "Cannot Update. Database or Object is read-ony"
Can anybody help me here! I know less VBA !
Thanks in advance.
Here is the code:
Dim Delrs as DAO.Recordset
Dim StrDel as string
StrDel = "SELECT DISTINCTROW Tremaishipment.SOHD, Tremaishipment.MAHH
FROM Thopdongxuat INNER JOIN (Tchitiethdx INNER JOIN Tremainshipment
ON (Tchitiethdx.MAHH = Tremainshipment.MAHH) AND (Tchitiethdx.SOHD =
Tremainshipment.SOHD)) ON Thopdongxuat.SOHD = Tchitiethdx.SOHD" _
& " GROUP BY Tremainshipment.SOHD, Tremainshipment.MAHH,
Tchitiethdx.SLUONG, Thopdongxuat.SAISO" _
& " having ((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].
[sluong]*[thopdongxuat].[saiso]);"
Set Delrs = CurrentDb.OpenRecordset(StrDel)
If Delrs.RecordCount > 0 Then
Delrs.Delete
Delrs.Update
End If
Set rsT = Nothing- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Hi, John.
Thanks ur response.
You're right, It deletes more records than I expected.
But I think we can approach this matter by other way.
Can u teach me how to check the ONLY ONE value of SOHD exist in table
Tremainshippment in VBA?
Then basing on this result, I will delete them with Criteria " WHERE
((Sum(Tremainshipment.SLUONG))<=[tchitiethdx].[sluong]*[thopdongxuat].
[saiso]);"
Click to expand...
Thanks all ur helps.
Luan from VietNam.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi, John. Excellence.
It work like a charms, thank. thank. thank.
Base on ur guidance, I find the solution.

Thanks with appreciatedly.
Luan
 
Back
Top