T_AptagSeikyuMeisai テーブルに T_ShohiRireki テーブルの部署名を追加
Private Sub btn_部署付_Click()
Dim db As DAO.Database
Dim vnsRs1 As DAO.Recordset
Dim vnsRs2 As DAO.Recordset
Dim aptRs1 As DAO.Recordset
Dim aptRs2 As DAO.Recordset
Dim aptcdRs2 As DAO.Recordset
Dim strSQL As String
Dim i As Integer, j As Integer
Dim lngRecMax As Long, ilngLoop As Long
Set db = CurrentDb
'T_AptageCode2 テーブルの削除
If IsTable("T_AptageCode2") Then
DoCmd.DeleteObject acTable, "T_AptageCode2"
MsgBox "T_AptageCode2 を削除しました。"
End If
strSQL = "SELECT DISTINCT 商品コード INTO T_AptageCode FROM T_AptageSeikyuMeisai;"
DoCmd.RunSQL strSQL
strSQL = "SELECT DISTINCT 商品コード, 単位コード, 単位名, 売上数量 INTO T_AptageCode2 FROM T_AptageSeikyuMeisai;"
DoCmd.RunSQL strSQL
Set vnsRs1 = db.OpenRecordset("T_ShohiRireki", dbOpenDynaset)
Set aptRs1 = db.OpenRecordset("T_AptageSeikyuMeisai", dbOpenDynaset)
Set aptcdRs2 = db.OpenRecordset("T_AptageCode2", dbOpenDynaset)
aptcdRs2.MoveFirst
Do Until aptcdRs2.EOF
vnsRs1.Filter = "アプテージコード = " & aptcdRs2.Fields("商品コード").Value & " AND 入数 = " & aptcdRs2.Fields("売上数量").Value
Set vnsRs2 = vnsRs1.OpenRecordset
If vnsRs2.EOF Or BOF Then
MsgBox aptcdRs2.Fields("商品コード").Value & " は該当するデータがありません。次のコードを検索します。", vbOKOnly + vbInformation
aptcdRs2.MoveNext
Else
aptRs1.Filter = "商品コード = " & aptcdRs2.Fields("商品コード").Value & " AND 売上数量 = " & aptcdRs2.Fields("売上数量").Value
Set aptRs2 = aptRs1.OpenRecordset
Debug.Print aptcdRs2.Fields("商品コード").Value
Debug.Print vnsRs2.RecordCount
Debug.Print aptRs2.RecordCount
'最終行まで移動してレコード数を変数に入れる。MoveLastしなければ正しくカウントされない為。
vnsRs2.MoveLast
aptRs2.MoveLast
i = vnsRs2.RecordCount
j = aptRs2.RecordCount
vnsRs2.MoveFirst
aptRs2.MoveFirst
If j >= i Then
Do
Debug.Print vnsRs1.RecordCount
Debug.Print aptRs1.RecordCount
Debug.Print vnsRs2.RecordCount
Debug.Print aptRs2.RecordCount
aptRs2.Edit
aptRs2.Fields("部署名").Value = vnsRs2.Fields("部署名").Value
Debug.Print aptcdRs2.Fields("商品コード").Value
Debug.Print vnsRs2.Fields("アプテージコード").Value
Debug.Print vnsRs2.Fields("部署名").Value
Debug.Print vnsRs1.RecordCount
Debug.Print aptRs1.RecordCount
Debug.Print vnsRs2.RecordCount
Debug.Print aptRs2.RecordCount
aptRs2.Update
aptRs2.MoveNext
vnsRs2.MoveNext
Loop Until vnsRs2.EOF Or BOF
Else
Do
aptRs2.Edit
aptRs2.Fields("部署名").Value = vnsRs2.Fields("部署名").Value
Debug.Print aptcdRs2.Fields("商品コード").Value
Debug.Print vnsRs2.Fields("アプテージコード").Value
Debug.Print vnsRs2.Fields("部署名").Value
aptRs2.Update
vnsRs2.MoveNext
aptRs2.MoveNext
Loop Until aptRs2.EOF
End If
aptcdRs2.MoveNext
End If
Loop
vnsRs1.Close: Set vnsRs1 = Nothing
vnsRs2.Close: Set vnsRs2 = Nothing
aptRs1.Close: Set aptRs1 = Nothing
aptRs2.Close: Set aptRs2 = Nothing
aptcdRs2.Close: Set aptcdRs2 = Nothing
MsgBox "部署を付けました"
End Sub