ray88’s diary

お仕事で困ったとき用の自分用の覚書

AccessVBA テキストボックスやコンボボックスを順番に飛ばさず入力しているかチェックする

■必須項目ではないが、順番に飛ばさず入力する必要のあるものの空欄チェック方法

Sub chkSkipBlank()
'-------------------------------------------------------------
'機能:必須項目ではないが、途中を飛ばさずに順番に入力が必要な
'      コンボボックスの空欄チェック
'-------------------------------------------------------------
    Dim arrayNum() As Integer       '動的配列を宣言
    Dim i As Integer                'コンボボックス名の番号部分
    Dim k As Integer                '配列の要素番号
    Dim LastItemNum As Integer      '配列の最後に格納されている番号
    Dim arrayMaxNum As Integer      '配列インデックスの最大値
    k = 0
    '「テスト用」フォーム内のコンボボックス「cmb_Test1」から「cmb_Test4」まで空欄チェック
    With Forms("テスト用")
        For i = 1 To 4
            'コンボボックスの値が空欄以外なら配列にコンボボックス名の数値部分を格納
            If .Controls("cmb_Test" & i) <> "" Then
                k = k + 1
                ReDim Preserve arrayNum(k)
                arrayNum(k) = i
            End If
        Next
    End With    
    '配列の最後の要素に格納された数値を取得
    LastItemNum = arrayNum(UBound(arrayNum))
    '配列のインデックスの最大値を取得
    arrayMaxNum = UBound(arrayNum)
    
    Debug.Print "配列の最後の要素:" & LastItemNum
    Debug.Print "配列インデックスの最大値:" & arrayMaxNum
    
    '配列の要素の最後に格納された数値と配列インデックスの最大値の数値が一致していない場合は
    '途中のコンボボックスを飛ばして入力していると判定
    If LastItemNum <> arrayMaxNum Then
        MsgBox "コンボボックスは途中を飛ばさずに順番に入力してください"
        Debug.Print "コンボボックスは途中を飛ばさずに順番に入力してください"
    Else
        MsgBox "入力OK"
        Debug.Print "入力OK"
    End If
End Sub

f:id:ray88:20201128113149p:plain
f:id:ray88:20201128113237p:plain
■部品化
呼び出し元プロシージャ

Sub テスト()
    Dim Result As Boolean
    Result = fncChkSkipBlank("テスト用", "cmb_Test", 4)
    MsgBox Result
End Sub

Functionプロシージャ

Function fncChkSkipBlank(strFormName As String, strControlName As String, intControlMaxNum As Integer) As Boolean
'---------------------------------------------------------------------------
'機能:必須項目ではないが、途中を飛ばさずに順番に入力が必要な
'      コンボボックスやテキストボックス等の空欄チェック
'引数1:対象フォーム名
'引数2:数値部分を除いたコントロール名 (例)「txb_Name1」~「txb_Name4」
'    を調べたい場合は「txb_Name」までを引数で渡す
'引数3:確認したいコントロールの個数(例)「txb_Name1」~「txb_Name4」
'    を調べたい場合は「4」を引数で渡す
'戻り値:飛ばして入力している場合はFalse,順番に入力している場合はTrue
'----------------------------------------------------------------------------
    Dim arrayNum() As Integer       '動的配列を宣言
    Dim i As Integer                'コントロール名の番号部分
    Dim k As Integer                '配列の要素番号
    Dim LastItemNum As Integer      '配列の最後に格納されている番号
    Dim arrayMaxNum As Integer      '配列のインデックスの最大値    
    k = 0
    '指定されたフォーム内の対象コントロールの個数分の空欄チェック
    With Forms(strFormName)
        For i = 1 To intControlMaxNum
            'コントロールの値が空欄以外なら配列にコントロール名の数値部分を格納
            If .Controls(strControlName & i) <> "" Then
                k = k + 1
                ReDim Preserve arrayNum(k)
                arrayNum(k) = i
            End If
        Next
    End With    
    '配列の最後の要素に格納された数値を取得
    LastItemNum = arrayNum(UBound(arrayNum))
    '配列のインデックスの最大値を取得
    arrayMaxNum = UBound(arrayNum)    
    Debug.Print "配列の最後の要素:" & LastItemNum
    Debug.Print "配列のインデックスの最大値:" & arrayMaxNum    
    '配列の要素の最後に格納された数値と配列のインデックスの最大値の数値が一致していない場合
    '途中のコントロールを飛ばして入力していると判定
    If LastItemNum <> arrayMaxNum Then
        fncChkSkipBlank = False
    Else
        fncChkSkipBlank = True
    End If
End Function

コンボボックスで選んだ値に紐づいてテキストボックスの別カラムの値を表示させる方法②

VBAで実装する方法

Private Sub Form_Load()
'-----------------------------------------------
'フォーム読み込時にコンボボックスのソースを設定
'-----------------------------------------------
    With Me.cmb_Test
        'ソースのタイプを設定
    .RowSourceType = "Table/Query"
        'ソースを設定
        .RowSource = "T部署マスタ"
        '列数設定
        .ColumnCount = 2
        '各列の幅を設定
        .ColumnWidths = "1cm;2cm"
        '連結列の設定
        .BoundColumn = 1
        'リストボックスの幅を設定
        '1cm=567twipなので設定したいcmの数に567を掛ける
        .ListWidth = 3 * 567
    End With
End Sub

f:id:ray88:20201126205447p:plain
※コンボボックスのソースがテーブルやクエリで、ソースとなるテーブルの別カラムを反映させる場合は以下のコードで表示させる方法が一番簡単

Private Sub cmb_Test_AfterUpdate()
'----------------------------------------------------------------
'コンボボックスの値に紐づいてテキストボックスに表示させる値を設定
'----------------------------------------------------------------
    Me.txb_DepartmentName = Me.cmb_Test.Column(1)
End Sub

※コンボボックスのコントロールソースがテーブルやクエリ以外、またはソースとなるテーブルやクエリ以外の値を関連付けて表示させる場合はDlookup関数を使用

Private Sub cmb_Test_AfterUpdate()
'----------------------------------------------------------------
'コンボボックスの値に紐づいてテキストボックスに表示させる値を設定
'----------------------------------------------------------------
    Me.txb_DepartmentName = DLookup("部署名", "T部署マスタ", "部署コード=" & "cmb_Test")
End Sub

f:id:ray88:20201126205517p:plain

Private Sub btn_Clear_Click()
'----------------------------------------------------------
'クリアボタンでテキストボックスとコンボボックスの値をクリア
'----------------------------------------------------------
    Dim c As Object
    For Each c In Me.Controls
        If c.ControlType = acTextBox Or c.ControlType = acComboBox Then
            c = Null
        End If
    Next
End Sub

f:id:ray88:20201126205550p:plain
■以下の様に参照先が同じコンボボックスが複数ある場合で
DlookUp関数使用する場合の部品化
※何度も言うが、コンボボックスと同じテーブル・クエリの別カラムを反映させる場合は「コンボボックス名.Column(カラム番号)」で転記するのが一番簡単。
 コントロールソース以外の値を引っ張ってくる際にDlookupを使用する

f:id:ray88:20201126234630p:plain
呼び出し元のイベントプロシージャ

Private Sub cmb_Test2_AfterUpdate()
    Me.txb_DepartmentName2 = fncGetDepartment("cmb_Test2")
End Sub
Private Sub cmb_Test3_AfterUpdate()
    Me.txb_DepartmentName3 = fncGetDepartment("cmb_Test3")
End Sub
Private Sub cmb_Test4_AfterUpdate()
    Me.txb_DepartmentName4 = fncGetDepartment("cmb_Test4")
End Sub

部品化したコード

Function fncGetDepartment(cmbName As String) As String
'----------------------------------------------------------
'コンボボックスの値を元にテキストボックスの値を反映させる
'引数1:対象コンボボックスの名前
'戻り値:テキストボックスに表示させる値
'----------------------------------------------------------
    'Null値チェック
    If IsNull(Forms("テスト用").Controls(cmbName)) Then
        fncGetDepartment = ""
        Exit Function
    End If
    'コンボボックスで選択された値を取得
    Dim strCode As String
    strCode = Forms("テスト用").Controls(cmbName)
    '取得した値を条件式に部署名を抽出
    fncGetDepartment = DLookup("部署名", "T部署マスタ", "部署コード=" & Chr(34) & strCode & Chr(34))
End Function

Access VBA コントロールの値をまとめてクリアする・値を入力する

Sub FormTest()
    Dim c As Object
    Forms("Fフォーム").txt3.Value = "標準モジュール"
    MsgBox "標準モジュールから参照"
    For Each c In Forms("Fフォーム").Controls
        If c.ControlType = acTextBox Then
            c.Value = ""
        End If
    Next
End Sub

f:id:ray88:20201126191035p:plain
f:id:ray88:20201126191110p:plain
■以下の様にプロパティーシートのコントロールソースに値が設定されている場合、
「このオブジェクトに値を代入できません」と表示され、エラーになります。
f:id:ray88:20201126192402p:plain
f:id:ray88:20201126192519p:plain

UiPath NULL値の代入

■Uipathのテーブル等で取得した値を保持する際、値がなかった場合の行に自動で空の文字列を挿入してしまうことがある。
 テーブルを最終的にExcelに貼り付ける等の処理を行う場合、セルに書式設定がされていると空の文字列が悪さをして意図しない値が反映されてしまう場合がある。
 テーブルをExcelに貼り付ける前等に空の文字列をNull値に変換する必要がある。

■NULL値を代入するにはassignアクティビティで「Nothing」を代入すればOK

■以下は値取得後の最後にまとめてからの文字列をNULL値に変換しているフロー
f:id:ray88:20201126121640p:plain

ADO レコードの追加 AddNewメソッド

【AddNewメソッドの引数として、フィールドリスト、値リストを渡す】

RS.AddNew フィールドリスト,値リスト

■フィールドリスト、値リストを作成する方法
・リストに使用する変数をVariant型で宣言する
・Array関数でリストになる配列を変数に格納する

Dim List1 As Variant
Dim List2 As Variant
List1 = Array("F1","F2","F3")
List2 = Array("値1","値2","値3")
RS.AddNew List1,List2

(例)

Sub Test3()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim FieldList As Variant
    Dim ValueList As Variant
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    RS.Open "T部署マスタ", CN, adOpenKeyset, adLockOptimistic
    FieldList = Array("部署コード", "部署名")
    ValueList = Array("B088", "電算部")
    RS.AddNew FieldList, ValueList
    RS.Close: CN.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

f:id:ray88:20201126000101p:plain
【AddNewメソッドを実行後、Fieldオブジェクトに値を代入し、Updateメソッドを実行する】

RS.AddNew
RS("フィールド1").Value =1
RS("フィールド2").Value =2
RS.Update

(例)

Sub Test4()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    RS.Open "T部署マスタ", CN, adOpenKeyset, adLockOptimistic
    RS.AddNew
    RS("部署コード") = "B099"
    RS("部署名") = "管理部"
    RS.Update
    RS.Close: CN.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

f:id:ray88:20201126001242p:plain

ADO レコードの更新・Updateメソッド

【Updateメソッドの引数としてフィールド名、値を渡す】

RS.Update フィールド1,1
RS.Update フィールド2,2

(例)

Sub Test1()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim SQL As String
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    SQL = "SELECT * FROM T社員名簿 WHERE 社員番号 = 1002;"
    RS.Open SQL, CN, adOpenKeyset, adLockOptimistic
    RS.Update "部署コード", "B099"
    RS.Update "給与", "500000"
    RS.Close: CN.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

f:id:ray88:20201125232939p:plain
【FieldオブジェクトのValueプロパティに値を代入し、Updateメソッドを実行する】

RS("フィールド1").Value =1
RS("フィールド2").Value =2
RS.Update

(例)

Sub Test2()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    RS.Open "T社員名簿", CN, adOpenKeyset, adLockOptimistic
    Do Until RS.EOF
        RS("年齢") = RS("年齢") + 1
        RS.Update
        RS.MoveNext
    Loop
    RS.Close: CN.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

f:id:ray88:20201125233428p:plain
【フィールドオブジェクトの参照方法】
以下の様に様々な記述方法がある。Valueプロパティは省略可。

RS![フィールド名].Value
RS.Fields("フィールド名").Value
RS("フィールド名").Value
RS.Fields(n).value (nは0から始まるインデックス番号)
RS(n).Value (nは0から始まるインデックス番号)

【レコード更新時のLockTypeプロパティの設定】
■レコードの更新・追加・削除操作を行う際はLockTypeプロパティに「adLockOptimistic」等の更新可能なロックタイプを指定する必要がある。
■LockTypeプロパティの設定方法
 ①Openメソッドの引数で設定する
 ②LockTypeプロパティにあらかじめ設定する
 ※メソッドの引数を省略した場合はあらかじめオブジェクトに設定されているプロパティの値が引き継がれる。

ADO Recordsetオブジェクトにレコードセットを取得する

■方法①:RecordsetオブジェクトのOpenメソッドで開く
【書式】

RS.Open ソース,CN,カーソルタイプ,ロックタイプ

※変数宣言の際に以下の様に記述するとインスタンス生成を同時に行える

Dim RS As New ADODB.Recordset
引数 説明
ソース テーブル名、クエリ名、SQLステートメントなどを指定
CN Connection,オブジェクトを指定
カーソルタイプ カーソルタイプを指定。CursorTypeプロパティの定数を指定する
ロックタイプ ロックタイプを指定。LockTypeプロパティの定数を指定する

(例)

Sub Test1()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set CN = CurrentProject.Connection
    Set RS = New ADODB.Recordset
    RS.Open "T社員名簿", CN, adOpenStatic
    RS.MoveLast
    Do Until RS.BOF
        Debug.Print RS.Fields(0), _
                    RS.Fields(1), _
                    RS.Fields(2)
        RS.MovePrevious
    Loop
    RS.Close: CN.Close
    Set RS = Nothing: Set CN = Nothing
End Sub

■方法②:ConnectionオブジェクトのExcuteメソッドを使用する

Dim RS As ADODB.Recordset
Set RS = CN.Execute(コマンド)
オブジェクト 説明
CN Connectionオブジェクトを指定
引数 説明
コマンド テーブル名、クエリ名、SQLステートメントなど指定

※ConnectionオブジェクトのExecuteメソッドの結果をRecordsetオブジェクトで取得した場合、レコードセットは常に前方スクロールカーソルの読み取り専用となる。

※Executeメソッドでアクションクエリを実行した際にレコードセットが返らない場合、指定したアクションが実行される。
その場合、レコードセットのオブジェクト変数は使用せず以下の様に記述する。

CN.Execute コマンド

(例)

Sub Test2()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set CN = CurrentProject.Connection
    Set RS = CN.Execute("T社員名簿")
    Do Until RS.EOF
        Debug.Print RS.Fields(0), _
                    RS.Fields(1), _
                    RS.Fields(2)
        RS.MoveNext
    Loop
    RS.Close: CN.Close
    Set RS = Nothing: Set CN = Nothing
End Sub