アクセス小僧:主テーブルの1レコードを削除し、表示履歴テーブルの削除したレコード(主テーブル)のIDを変更 [コンピューター]
アクセスのフォームで、主テーブルの1レコードを削除し、表示履歴テーブルの、削除したレコード(主テーブル)のIDを変更するマクロの変更をしました。
変更部分:表示履歴テーブルから、削除した主テーブルのレコードのIDを検索し、IDを書き換える部分
変更前:FindFirstで、表示履歴テーブルから、削除した主テーブルのレコードのIDを検索
変更後:SQLのSELECT文で、表示履歴テーブルから、削除した主テーブルのレコードのIDを検索
※表示履歴テーブル上に、IDが複数あっても、変更可能とするため
変更前:削除する主テーブルのレコードが先頭レコードなら、次のレコード、先頭レコード以外なら、1つ前のレコードのIDで表示履歴テーブルを書き換え
変更後:主テーブルの中でIDが最小のもの(削除するレコードを除く)をSQLのSELECT文で検索し、そのIDで表示履歴テーブルを書き換え
※この変更はしなくても良かった。レコード削除後、表示履歴テーブル上に存在しないID(主テーブル)がなくなればよいので。
【マクロ】
Private Sub レコード削除_Click()
'【変数】
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim Ans1 As Long ' 答え
Dim Str1 As String
Dim myID1(2) As Long ' 主キー
Dim AbsPos1 As Long ' レコード番号
Dim RcdCnt1 As Long ' レコード数
Dim strSQL As String ' SQL文字列
Dim IDmin1 As Long
'【実行コード】
Debug.Print "--- レコード削除_Click(Start) ---"
myID1(0) = [ID]
Debug.Print "myID1(0)=" & myID1(0) & "[レコード削除_Click]"
Debug.Print "Me.NewRecord=" & Me.NewRecord & "[レコード削除_Click]"
If Me.NewRecord = True Then
Ans1 = MsgBox("新規レコード(ID=" & myID1(0) & ")は削除できません。[レコード削除_Click]", vbOKOnly)
Debug.Print "--- レコード削除_Click(Exit, 新規レコード) ---"
Exit Sub
End If
Ans1 = MsgBox("ID=" & myID1(0) & "を削除してもよろしいですか? [レコード削除_Click]", vbYesNo + vbInformation, "確認")
If Ans1 = vbNo Then
Debug.Print "--- レコード削除_Click(Exit, No) ---"
Exit Sub
End If
AbsPos1 = IDtoAbsPos1("T_医療費", "ID", myID1(0)) ' レコード番号
Debug.Print "AbsPos1=" & AbsPos1 & "[レコード削除_Click]"
Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("T_医療費", dbOpenDynaset)
rs1.MoveFirst ' 先頭レコードへ
rs1.MoveLast ' 最終レコードへ
rs1.MoveFirst ' 先頭レコードへ
rs1.Move AbsPos1 ' 削除しようとしているレコードに移動
' レコード削除後に履歴に残すIDを決める
RcdCnt1 = rs1.RecordCount ' レコード数
Debug.Print "RcdCnt1=" & RcdCnt1 & "[レコード削除_Click]"
If RcdCnt1 >= 2 Then
If AbsPos1 = 0 Then
rs1.Move 1 ' 先頭レコードなら、次のレコードへ
Else
rs1.Move -1 ' 先頭レコードでないなら、前のレコードへ
End If
End If
Debug.Print "rs1.AbsolutePosition=" & rs1.AbsolutePosition & "[レコード削除_Click]"
myID1(1) = rs1![ID]
Debug.Print "myID1(1)=" & myID1(1) & "[レコード削除_Click]"
rs1.Close
Set rs1 = Nothing ' 解放
' レコード削除
Str1 = "select * from T_医療費 where ID in (" & myID1(0) & ")"
Set rs1 = db1.OpenRecordset(Str1)
Debug.Print "rs1.RecordCount=" & rs1.RecordCount & "[レコード削除_Click]"
rs1.Edit
rs1.Delete
rs1.Close
Set rs1 = Nothing ' 解放
' 履歴変更
' 削除するレコード以外のIDの中で最小のものを探す。
strSQL = "SELECT MIN(ID) AS IDmin FROM T_医療費 WHERE ID <> " & myID1(0)
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount = 1 Then
IDmin1 = rs1![IDmin]
Debug.Print "IDmin1=" & IDmin1
Else
Exit Sub
End If
' 履歴の中の削除したレコードのIDを書き換え
strSQL = "SELECT * FROM T_CR医療費ID WHERE F_医療費CR_ID =" & myID1(0)
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount >= 1 Then
rs1.MoveFirst
Do Until rs1.EOF
rs1.Edit
rs1![F_医療費CR_ID] = IDmin1
rs1.Update
rs1.MoveNext
Loop
End If
rs1.Close
Set rs1 = Nothing ' 解放
'Set rs1 = db1.OpenRecordset("T_CR医療費ID", dbOpenDynaset)
'rs1.MoveFirst ' 先頭レコードへ
'rs1.MoveLast ' 最終レコードへ
'rs1.MoveFirst ' 先頭レコードへ
'rs1.FindFirst "F_医療費CR_ID=" & myID1(0) ' 履歴を削除するレコードのIDで探索
'Debug.Print "rs1.AbsolutePosition=" & rs1.AbsolutePosition & "[レコード削除_Click]"
'Debug.Print "rs1.NoMatch=" & rs1.NoMatch & "[レコード削除_Click]"
'rs1.Edit
'rs1![F_医療費CR_ID] = myID1(1)
'rs1.Update
'rs1.Close
db1.Close
Set db1 = Nothing ' 解放
Call 開き直し_Click
Debug.Print "--- レコード削除_Click(End) ---"
End Sub
なお、姉妹マクロとして、1レコード残して、主テーブルのレコード削除し、表示履歴テーブルのID(主テーブル)を
残した1レコードのIDで書き換え、主テーブルのIDを初期化し、1から始めるマクロも作りました。
Private Sub レコード全削除_Click()
'【変数】
Debug.Print "---レコード全削除_Click(start)---"
Dim strSQL As String ' SQL文字列
Dim db1 As DAO.Database
Dim rs1 As Recordset
Dim IDmax1 As Long
'【実行コード】
Set db1 = CurrentDb
' IDの最大値を求める
strSQL = "SELECT MAX(ID) AS IDmax FROM T_医療費"
Set rs1 = CurrentDb.OpenRecordset(strSQL)
IDmax1 = rs1![IDmax]
Debug.Print "IDmax1=" & IDmax1
' IDが最大値のレコードを除き、削除
strSQL = "SELECT * FROM T_医療費 WHERE ID<>" & IDmax1
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount >= 1 Then
MsgBox "T_医療費テーブルのID=" & IDmax1 & "以外の全レコードを削除します。"
rs1.Edit
rs1.Delete
End If
' 履歴テーブルのIDを全て、ID最大値にする
strSQL = "SELECT * FROM T_CR医療費ID"
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount >= 1 Then
MsgBox "履歴テーブル(T_CR医療費ID)のF_医療費CR_IDを、すべて「" & IDmax1 & "」にします。"
rs1.MoveFirst
Do Until rs1.EOF
rs1.Edit
rs1![F_医療費CR_ID] = IDmax1
rs1.Update
rs1.MoveNext
Loop
End If
rs1.Close
Set rs1 = Nothing ' 解放
' ID(オートナンバー)初期化し、1から始める
MsgBox "T_医療費テーブルのIDをリセットし、1から始めます。"
strSQL = "ALTER TABLE T_医療費 ALTER COLUMN ID COUNTER (1,1)"
db1.Execute strSQL
db1.Close
Set db1 = Nothing ' 解放
Debug.Print "---レコード全削除_Click(end)---"
End Sub
変更部分:表示履歴テーブルから、削除した主テーブルのレコードのIDを検索し、IDを書き換える部分
変更前:FindFirstで、表示履歴テーブルから、削除した主テーブルのレコードのIDを検索
変更後:SQLのSELECT文で、表示履歴テーブルから、削除した主テーブルのレコードのIDを検索
※表示履歴テーブル上に、IDが複数あっても、変更可能とするため
変更前:削除する主テーブルのレコードが先頭レコードなら、次のレコード、先頭レコード以外なら、1つ前のレコードのIDで表示履歴テーブルを書き換え
変更後:主テーブルの中でIDが最小のもの(削除するレコードを除く)をSQLのSELECT文で検索し、そのIDで表示履歴テーブルを書き換え
※この変更はしなくても良かった。レコード削除後、表示履歴テーブル上に存在しないID(主テーブル)がなくなればよいので。
【マクロ】
Private Sub レコード削除_Click()
'【変数】
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim Ans1 As Long ' 答え
Dim Str1 As String
Dim myID1(2) As Long ' 主キー
Dim AbsPos1 As Long ' レコード番号
Dim RcdCnt1 As Long ' レコード数
Dim strSQL As String ' SQL文字列
Dim IDmin1 As Long
'【実行コード】
Debug.Print "--- レコード削除_Click(Start) ---"
myID1(0) = [ID]
Debug.Print "myID1(0)=" & myID1(0) & "[レコード削除_Click]"
Debug.Print "Me.NewRecord=" & Me.NewRecord & "[レコード削除_Click]"
If Me.NewRecord = True Then
Ans1 = MsgBox("新規レコード(ID=" & myID1(0) & ")は削除できません。[レコード削除_Click]", vbOKOnly)
Debug.Print "--- レコード削除_Click(Exit, 新規レコード) ---"
Exit Sub
End If
Ans1 = MsgBox("ID=" & myID1(0) & "を削除してもよろしいですか? [レコード削除_Click]", vbYesNo + vbInformation, "確認")
If Ans1 = vbNo Then
Debug.Print "--- レコード削除_Click(Exit, No) ---"
Exit Sub
End If
AbsPos1 = IDtoAbsPos1("T_医療費", "ID", myID1(0)) ' レコード番号
Debug.Print "AbsPos1=" & AbsPos1 & "[レコード削除_Click]"
Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("T_医療費", dbOpenDynaset)
rs1.MoveFirst ' 先頭レコードへ
rs1.MoveLast ' 最終レコードへ
rs1.MoveFirst ' 先頭レコードへ
rs1.Move AbsPos1 ' 削除しようとしているレコードに移動
' レコード削除後に履歴に残すIDを決める
RcdCnt1 = rs1.RecordCount ' レコード数
Debug.Print "RcdCnt1=" & RcdCnt1 & "[レコード削除_Click]"
If RcdCnt1 >= 2 Then
If AbsPos1 = 0 Then
rs1.Move 1 ' 先頭レコードなら、次のレコードへ
Else
rs1.Move -1 ' 先頭レコードでないなら、前のレコードへ
End If
End If
Debug.Print "rs1.AbsolutePosition=" & rs1.AbsolutePosition & "[レコード削除_Click]"
myID1(1) = rs1![ID]
Debug.Print "myID1(1)=" & myID1(1) & "[レコード削除_Click]"
rs1.Close
Set rs1 = Nothing ' 解放
' レコード削除
Str1 = "select * from T_医療費 where ID in (" & myID1(0) & ")"
Set rs1 = db1.OpenRecordset(Str1)
Debug.Print "rs1.RecordCount=" & rs1.RecordCount & "[レコード削除_Click]"
rs1.Edit
rs1.Delete
rs1.Close
Set rs1 = Nothing ' 解放
' 履歴変更
' 削除するレコード以外のIDの中で最小のものを探す。
strSQL = "SELECT MIN(ID) AS IDmin FROM T_医療費 WHERE ID <> " & myID1(0)
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount = 1 Then
IDmin1 = rs1![IDmin]
Debug.Print "IDmin1=" & IDmin1
Else
Exit Sub
End If
' 履歴の中の削除したレコードのIDを書き換え
strSQL = "SELECT * FROM T_CR医療費ID WHERE F_医療費CR_ID =" & myID1(0)
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount >= 1 Then
rs1.MoveFirst
Do Until rs1.EOF
rs1.Edit
rs1![F_医療費CR_ID] = IDmin1
rs1.Update
rs1.MoveNext
Loop
End If
rs1.Close
Set rs1 = Nothing ' 解放
'Set rs1 = db1.OpenRecordset("T_CR医療費ID", dbOpenDynaset)
'rs1.MoveFirst ' 先頭レコードへ
'rs1.MoveLast ' 最終レコードへ
'rs1.MoveFirst ' 先頭レコードへ
'rs1.FindFirst "F_医療費CR_ID=" & myID1(0) ' 履歴を削除するレコードのIDで探索
'Debug.Print "rs1.AbsolutePosition=" & rs1.AbsolutePosition & "[レコード削除_Click]"
'Debug.Print "rs1.NoMatch=" & rs1.NoMatch & "[レコード削除_Click]"
'rs1.Edit
'rs1![F_医療費CR_ID] = myID1(1)
'rs1.Update
'rs1.Close
db1.Close
Set db1 = Nothing ' 解放
Call 開き直し_Click
Debug.Print "--- レコード削除_Click(End) ---"
End Sub
なお、姉妹マクロとして、1レコード残して、主テーブルのレコード削除し、表示履歴テーブルのID(主テーブル)を
残した1レコードのIDで書き換え、主テーブルのIDを初期化し、1から始めるマクロも作りました。
Private Sub レコード全削除_Click()
'【変数】
Debug.Print "---レコード全削除_Click(start)---"
Dim strSQL As String ' SQL文字列
Dim db1 As DAO.Database
Dim rs1 As Recordset
Dim IDmax1 As Long
'【実行コード】
Set db1 = CurrentDb
' IDの最大値を求める
strSQL = "SELECT MAX(ID) AS IDmax FROM T_医療費"
Set rs1 = CurrentDb.OpenRecordset(strSQL)
IDmax1 = rs1![IDmax]
Debug.Print "IDmax1=" & IDmax1
' IDが最大値のレコードを除き、削除
strSQL = "SELECT * FROM T_医療費 WHERE ID<>" & IDmax1
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount >= 1 Then
MsgBox "T_医療費テーブルのID=" & IDmax1 & "以外の全レコードを削除します。"
rs1.Edit
rs1.Delete
End If
' 履歴テーブルのIDを全て、ID最大値にする
strSQL = "SELECT * FROM T_CR医療費ID"
Set rs1 = CurrentDb.OpenRecordset(strSQL)
If rs1.RecordCount >= 1 Then
MsgBox "履歴テーブル(T_CR医療費ID)のF_医療費CR_IDを、すべて「" & IDmax1 & "」にします。"
rs1.MoveFirst
Do Until rs1.EOF
rs1.Edit
rs1![F_医療費CR_ID] = IDmax1
rs1.Update
rs1.MoveNext
Loop
End If
rs1.Close
Set rs1 = Nothing ' 解放
' ID(オートナンバー)初期化し、1から始める
MsgBox "T_医療費テーブルのIDをリセットし、1から始めます。"
strSQL = "ALTER TABLE T_医療費 ALTER COLUMN ID COUNTER (1,1)"
db1.Execute strSQL
db1.Close
Set db1 = Nothing ' 解放
Debug.Print "---レコード全削除_Click(end)---"
End Sub
ヤバイぜ! ありがとうございます(^_0)ノ
by cheese999 (2020-01-29 05:58)