パソコン(ソフト)

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

 

やりたい事は、

ココがポイント

日付の古いレコードから順番に減算をさせたい

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

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

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

 

 

サンプルデータ準備

  • 商品_tbl
  • 消費_tbl

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

 

 

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

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

 

 

 

求めたい結果(やりたい事)

 

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

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

やりたいことは、

ココがポイント

商品_tblから、古い日付の順番に、その合計数だけ減算して値を求めたい

のです。

 

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

 

 

日付の古い方から、ameが15個、cockieが25個減算した結果
分かりやすくするために並び替えしました。






計算ボタンに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」をレコードセットにして、1レコードからループで回す事にしました。

trans_pno = rs!product_no

trans_kazu = rs!kazu

 

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

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

Call p_calc(trans_pno, trans_kazu)

 

参照渡し

参照渡し」という方法で、変数をサブルーチンの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」の1レコード目の、商品コードと合計数が変数として、サブルーチンの方にきましたので、「商品_tbl」からSQLを使って、1レコード目の商品コードだけピックアップしてレコードセットに設定してあります。

参考

SQLもコードにすると難しそうに見えますが、私の場合は、Accessのクエリで作成してから、SQLコードに変換したものを使っています。多少余計なカッコなどは省いています。

 

 

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

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

 

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

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

 

 

 

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

 

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

 

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

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

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

 

 

 

見直して疑問(解決)

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

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

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

 

昇順なら、

ORDER BY fildname


もしくは、

ORDER BY fildname ASC




降順なら、

ORDER BY fildname DESC





そうだったんですね。







-パソコン(ソフト)
-, , , ,

Translate »

© 2022 PCTips