【Access】古い日付のレコードから減算処理をするVBAコード（値渡し、参照渡し）

やりたい事は、

という事なのですが、ネット検索してもなかなかスバリのコードを見つけることができませんでした。

かといって、なんちゃってAccessマスターの私にはコードなど書ける自信がありません。

しかし、どうしてもやらざるを得なくなり頑張った結果が今回の記事です。

サンプルデータ準備

• 商品_tbl
• 消費_tbl

と２つのテーブルを準備しました。各テーブル内にはサンプルデータが入力してあります。

フォーム１には、ボタンを設置して処理のスイッチとしてコマンドボタンを設置してあります。

• 「計算」ボタンは、クリックすることで求めたい処理をさせます。
• 「商品_tblを開く」ボタンは、ただ単に「商品_tbl」を開いて処理した内容を確認する為だけのボタンです。
• 「商品_tblリセット」は、クエリで最初の初期値に戻すためのボタンです。

求めたい結果（やりたい事）

商品_tblの日付が違う２種類の商品があります。

そして、消費_tblでは、２種類の消費した合計数が入力されています。

やりたいことは、

のです。

処理した結果がうまく行けば下記の様な、商品_tblの結果になるはずです。

計算ボタンにVBA作成

Private Sub calc_btn_Click()
Dim cn As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim SQL As String 
Dim pid As String 
Dim i As Integer 
Dim trans_pno As String 
Dim trans_kazu As String

Me!view.Value = ""

Set cn = CurrentProject.Connection 
rs.Open "消費_tbl", cn, adOpenKeyset, adLockOptimistic 

    Do While Not rs.EOF 
        With Me trans_pno = rs!product_no
        trans_kazu = rs!kazu 
    End With

    Call p_calc(trans_pno, trans_kazu) 
    rs.MoveNext

    Loop 

rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
MsgBox "完了しました。"
End Sub

まず、「消費_tbl」をレコードセットにして、１レコードからループで回す事にしました。

このコードの部分で、変数に、「消費_tbl」の商品名（検索するコード）と減算する数値をインプットしています。

次のレコードに行く前に、サブルーチンで別のVBAコードに飛ばせています。

参照渡し

「参照渡し」という方法で、変数をサブルーチンのVBA内でも使えるようにしています。

恥ずかしながら、

ところが、この「参照渡し」にて変数を渡す方法を使ったら問題なく動きました。

参考にさせていただいたサイトがあるのでよかったらどうぞ。

サブルーチンのVBAコード

Sub p_calc(trans_pno As String, trans_kazu As String)
Dim cn As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim SQL As String 
Dim pid As String 
Dim i As Integer

    SQL = "SELECT 商品_tbl.[day], 商品_tbl.[product_no], 商品_tbl.[number] "
    SQL = SQL & "FROM 商品_tbl " 
    SQL = SQL & "WHERE 商品_tbl.[product_no]= '" & trans_pno & "' "
    SQL = SQL & "ORDER BY 商品_tbl.[day]"

Set cn = CurrentProject.Connection
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic 

    i = 0

    Do Until rs.EOF

 		Do While i < trans_kazu
            If rs(2) = 0 Then
                 Exit Do
            End If

            i = i + 1

            rs(2) = rs(2) - 1

            rs.Update
        Loop

    rs.MoveNext
    Loop

rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

End Sub

「消費_tbl」の１レコード目の、商品コードと合計数が変数として、サブルーチンの方にきましたので、「商品_tbl」からSQLを使って、１レコード目の商品コードだけピックアップしてレコードセットに設定してあります。

あとは、レコードセットされた特定商品コードの全てのレコードの数値（numberフィールド）に対して変数として持ってきたkazuの数値をゼロになるまで減算していけばいい訳です。

※rs(2)というコードがありますが、これは、フィールドの番号を入れているだけです。直接フィールド名を入れても処理できるかもしれません。

以上で、自分が欲しかった求める処理が完成しました。

計算ボタンをクリックすると、見事に結果がでました。

コードなど書けないと思っていましたが・・・

私は、なんちゃってAccessマスターなので、自分でコードなど作成することは無いと思っていまいたが、必要に迫られれば冷静に考えてコードが書けるという事が分かりました。

誰かのサンプルコードを見ると、訳の分からない呪文のような文字列に拒否反応を示していましたが、自分のコードも知らない人から見れば呪文に見えるかもしれません。

ただ、冷静に見て頂ければ、たいした処理はしていませんし、SQLの部分もAccessのクエリをSQLに変換しただけです。

自分もこのような事を言えるような余裕が出てきたので良かったです。

見直して疑問（解決）

日付の古い順から減算という事で、自分で書いたコードですが、どの部分で日付の古い順から減算するような事をやっているのでしょうか！？

求める数値は出ていますが、レコードが多くなったりすると、古い順から減算できなくなるんじゃないかと言う疑問が残っています。

ORDER BY　で並び替えやっていました！

昇順なら、

ORDER BY fildname

ORDER BY fildname ASC

ORDER BY fildname DESC