マイクロソフトアクセス(以下Access)を使い始めて、やっと値渡しなどを覚えてちょっと複雑な処理をすることが可能になりましたが、
ココがポイント
サブルーチンにサブルーチンが必要な場面
が出てきてシンプルにできないかと調べていたら、
ココがポイント
戻り値が使えるFunctionプロシージャ
と言うのを知り備忘録にしました。
何をやりたいのか!?実験サンプルにて作成
やりたい事は、2つのテーブル、order_testとarrivalとで、計算したり、計算したレコードの日付を戻して記載するものです。
テーブル order_testと、arrivalは下記のようになります。赤丸の部分は、処理した結果を現わしています。
具体的処理は、
メモ
- order_testの1レコードづつを読み込み
- arrivalテーブルの日付が古いレコードのstockからnumberの数だけ減算
- arrivalの現在する最初のレコードの日付を、order_testのdayに記載
文章に書くと簡単そうですが、実際にVBAコードにしようとすると途方にくれます。
実際は、数時間かかりましたが、出来上がったコードを元に解説をしていきたいと思います。
フォームから実行ボタンでVBA実行
Private Sub enter_btn_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim SQL As String Dim trans_code As String Dim trans_number As Integer Dim ans As Date SQL = "SELECT * FROM order_test WHERE [no] = '111'" Set cn = CurrentProject.Connection rs.Open SQL, cn, adOpenKeyset, adLockOptimistic Do Until rs.EOF trans_code = rs!code trans_number = rs!Number ans = shipp_calc(trans_code, trans_number) rs!vew_day = ans rs.Update rs.MoveNext Loop rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub
上のコードでは、order_testテーブルを読み込んで、1レコードずつ回している段階です。
値渡しを使って最初はサブルーチンで減算まではできるようになりました。
【Access】古い日付のレコードから減算処理をするVBAコード(値渡し、参照渡し)
しかし、減算した時の最初のレコードの日付をどのようにして、order_testに戻して書き込むかに悩んで、サブルーチンに、サブルーチンで処理するかと考えましたが、偶然にも値戻しという方法を知りました。
今更、しらなかったのかよ!って突っ込みは無しでお願いしますm(__)m
値戻し、Functionプロシージャ
知らなかったので難しく考えて使ったことがありませんでしたが、実際に使ってみると、これは使えます。
いきなり、コードを載せるよりかは解説サイトなどを見て頂いた方が理解しやすいかと思います。
簡単に考えれば、自分で作った関数のようなものですかね。
実際は、関数と言うより、処理そのものだと思いますが、答えが返せるという面で、サブルーチンとは違いますね。
Functionプロシージャで処理
ボタンを押したコードから、
ans = shipp_calc(trans_code, trans_number)
この、shipp_calcという部分でFunctionプロシージャを呼び出している事になります。
サブルーチンのように、Callは使わなくていいようですね。
今回は、arrivalテーブルから日付を答えとして値を戻したいので、型は、Date型を使っています。
そして、日付の答えを、ansという変数にして、order_testテーブルのdayフィールドを更新かけています。
Function shipp_calc(trans_code As String, trans_number As Integer) As Date Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim SQL As String Dim i As Integer SQL = "SELECT arrival.[day], arrival.[code], arrival.[stock] " SQL = SQL & "FROM arrival " SQL = SQL & "WHERE arrival.[code]= '" & trans_code & "' and 0<arrival.[stock] " SQL = SQL & "ORDER BY arrival.[day]" Set cn = CurrentProject.Connection rs.Open SQL, cn, adOpenKeyset, adLockOptimistic i = 0 shipp_calc = rs!Day Do Until rs.EOF Do While i < trans_number If rs!stock = 0 Then Exit Do End If i = i + 1 rs!stock = rs!stock - 1 rs.Update Loop rs.MoveNext Loop rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Function
Functionプロシージャなんていうのを知らなかったこと自体、いままで余計な複雑なコードを作成して処理が遅くなっているような状況もあったかもしれません。
また一つ便利な方法を覚えました。