【Access】戻り値が使えるFunctionプロシージャを使うと複雑なVBAコードがシンプルになる

マイクロソフトアクセス（以下Access）を使い始めて、やっと値渡しなどを覚えてちょっと複雑な処理をすることが可能になりましたが、

が出てきてシンプルにできないかと調べていたら、

と言うのを知り備忘録にしました。

何をやりたいのか！？実験サンプルにて作成

やりたい事は、２つのテーブル、order_testとarrivalとで、計算したり、計算したレコードの日付を戻して記載するものです。

テーブル order_testと、arrivalは下記のようになります。赤丸の部分は、処理した結果を現わしています。

具体的処理は、

文章に書くと簡単そうですが、実際に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テーブルを読み込んで、１レコードずつ回している段階です。

値渡しを使って最初はサブルーチンで減算まではできるようになりました。

https://pctips.jp/pc-soft/access-vba-pull202003/

しかし、減算した時の最初のレコードの日付をどのようにして、order_testに戻して書き込むかに悩んで、サブルーチンに、サブルーチンで処理するかと考えましたが、偶然にも値戻しという方法を知りました。

今更、しらなかったのかよ！って突っ込みは無しでお願いしますm(__)m

値戻し、Functionプロシージャ

知らなかったので難しく考えて使ったことがありませんでしたが、実際に使ってみると、これは使えます。

いきなり、コードを載せるよりかは解説サイトなどを見て頂いた方が理解しやすいかと思います。

簡単に考えれば、自分で作った関数のようなものですかね。
実際は、関数と言うより、処理そのものだと思いますが、答えが返せるという面で、サブルーチンとは違いますね。

Functionプロシージャで処理

ボタンを押したコードから、

この、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プロシージャなんていうのを知らなかったこと自体、いままで余計な複雑なコードを作成して処理が遅くなっているような状況もあったかもしれません。

また一つ便利な方法を覚えました。