パソコン(ソフト)

【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に変換しただけです。

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

 

 

 

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

Copyright© PCTips , 2020 All Rights Reserved.