Accessで試算表を作る3~VBAコード

Pocket


コマンドボタンを押したときのコードです。(注意 長いコードです。)

Private Sub コマンド4_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset

Dim a As Long

rs.Open “T試算表”, cnn, adOpenKeyset, adLockOptimistic

rs.MoveFirst

Do Until rs.EOF

If rs!属性 = “貸” Then

rs!前月まで = Null

rs!前月まで = Nz(DLookup(“繰越残高”, “T科目名”, “科目CD=” & rs!科目CD & “”)) + Nz(DSum(“金額”, “T会計データ”, “借方CD=” & rs!科目CD & “” & ” and ” & “会計日付<#” & Me!テキスト0 & “#”)) – Nz(DSum(“金額”, “T会計データ”, “貸方CD=” & rs!科目CD & “” & ” and ” & “会計日付<#” & Me!テキスト0 & “#”))

Else

rs!前月まで = Null

rs!前月まで = Nz(DLookup(“繰越残高”, “T科目名”, “科目CD=” & rs!科目CD & “”)) + Nz(DSum(“金額”, “T会計データ”, “貸方CD=” & rs!科目CD & “” & ” and ” & “会計日付<#” & Me!テキスト0 & “#”)) – Nz(DSum(“金額”, “T会計データ”, “借方CD=” & rs!科目CD & “” & ” and ” & “会計日付<#” & Me!テキスト0 & “#”))

End If

rs!借方発生 = DSum(“金額”, “T会計データ”, “借方CD=” & rs!科目CD & “” & ” and ” & “会計日付>=#” & Me!テキスト0 & “#” & ” and ” & “会計日付<=#” & Me!テキスト2 & “#”)

rs!貸方発生 = DSum(“金額”, “T会計データ”, “貸方CD=” & rs!科目CD & “” & ” and ” & “会計日付>=#” & Me!テキスト0 & “#” & ” and ” & “会計日付<=#” & Me!テキスト2 & “#”)

rs.Update

If rs!属性 = “借” Then

rs!当月残高 = Null rs!当月残高 = Nz(rs!前月まで) + Nz(rs!借方発生) – Nz(rs!貸方発生)

rs.Update

End If

If rs!属性 = “貸” Then

rs!当月残高 = Nz(rs!前月まで) – Nz(rs!借方発生) + Nz(rs!貸方発生) Else rs!当月残高 = Null rs!当月残高 = Nz(rs!前月まで) – Nz(rs!借方発生) + Nz(rs!貸方発生)

rs.Update

End If

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

cnn.Close

Set cnn = Nothing

Dim cnn1 As ADODB.Connection

Dim rs1 As ADODB.Recordset

Set cnn1 = CurrentProject.Connection

Set rs1 = New ADODB.Recordset

rs1.Open “T試算表”, cnn1, adOpenKeyset, adLockOptimistic

rs1.Find “科目CD=522”

rs1!前月まで = DSum(“前月まで”, “T試算表”, “科目CD>=511″ & ” and ” & “科目CD<=521”)

rs1!借方発生 = DSum(“借方発生”, “T試算表”, “科目CD>=511″ & ” and ” & “科目CD<=521”)

rs1!貸方発生 = DSum(“貸方発生”, “T試算表”, “科目CD>=511″ & ” and ” & “科目CD<=521”)

rs1!当月残高 = DSum(“当月残高”, “T試算表”, “科目CD>=511″ & ” and ” & “科目CD<=521”)

rs1.Update

rs1.Find “科目CD=684”

rs1!前月まで = DSum(“前月まで”, “T試算表”, “科目CD>=550″ & ” and ” & “科目CD<=683”)

rs1!借方発生 = DSum(“借方発生”, “T試算表”, “科目CD>=550″ & ” and ” & “科目CD<=683”)

rs1!貸方発生 = DSum(“貸方発生”, “T試算表”, “科目CD>=550″ & ” and ” & “科目CD<=683”)

rs1!当月残高 = DSum(“当月残高”, “T試算表”, “科目CD>=550″ & ” and ” & “科目CD<=683”)

rs1.Update

rs1.Find “科目CD=754”

rs1!前月まで = DSum(“前月まで”, “T試算表”, “科目CD>=711″ & ” and ” & “科目CD<=753”)

rs1!借方発生 = DSum(“借方発生”, “T試算表”, “科目CD>=711″ & ” and ” & “科目CD<=753”)

rs1!貸方発生 = DSum(“貸方発生”, “T試算表”, “科目CD>=711″ & ” and ” & “科目CD<=753”)

rs1!当月残高 = DSum(“当月残高”, “T試算表”, “科目CD>=711″ & ” and ” & “科目CD<=753”)

rs1.Update

rs1.Find “科目CD=819”

rs1!前月まで = DSum(“前月まで”, “T試算表”, “科目CD>=811″ & ” and ” & “科目CD<=818”)

rs1!借方発生 = DSum(“借方発生”, “T試算表”, “科目CD>=811″ & ” and ” & “科目CD<=818”)

rs1!貸方発生 = DSum(“貸方発生”, “T試算表”, “科目CD>=811″ & ” and ” & “科目CD<=818”)

rs1!当月残高 = DSum(“当月残高”, “T試算表”, “科目CD>=811″ & ” and ” & “科目CD<=818”)

rs1.Update

rs1.Find “科目CD=830”

rs1!前月まで = DSum(“前月まで”, “T試算表”, “科目CD>=820″ & ” and ” & “科目CD<=829”)

rs1!借方発生 = DSum(“借方発生”, “T試算表”, “科目CD>=820″ & ” and ” & “科目CD<=829”)

rs1!貸方発生 = DSum(“貸方発生”, “T試算表”, “科目CD>=820″ & ” and ” & “科目CD<=829”)

rs1!当月残高 = DSum(“当月残高”, “T試算表”, “科目CD>=820″ & ” and ” & “科目CD<=829”)

rs1.Update

rs1.Close
Set rs1 = Nothing
cnn1.Close
Set cnn1 = Nothing

Dim cnn2 As ADODB.Connection
Dim rs2 As ADODB.Recordset

Set cnn2 = CurrentProject.Connection
Set rs2 = New ADODB.Recordset

rs2.Open “T試算表”, cnn2, adOpenKeyset, adLockOptimistic

rs2.Find “科目CD=685”

rs2!前月まで = DLookup(“前月まで”, “T試算表”, “科目CD=522”) – DLookup(“前月まで”, “T試算表”, “科目CD=684”)
rs2!貸方発生 = DLookup(“貸方発生”, “T試算表”, “科目CD=522”) – DLookup(“借方発生”, “T試算表”, “科目CD=684”) – DLookup(“借方発生”, “T試算表”, “科目CD=522”) + DLookup(“貸方発生”, “T試算表”, “科目CD=684”)
rs2!当月残高 = DLookup(“当月残高”, “T試算表”, “科目CD=522”) – DLookup(“当月残高”, “T試算表”, “科目CD=684”)

rs2.Update

rs2.Find “科目CD=755”
rs2!前月まで = DLookup(“前月まで”, “T試算表”, “科目CD=685”) – DLookup(“前月まで”, “T試算表”, “科目CD=754”)
rs2!貸方発生 = DLookup(“貸方発生”, “T試算表”, “科目CD=685”) – DLookup(“借方発生”, “T試算表”, “科目CD=754”) + Nz(DLookup(“貸方発生”, “T試算表”, “科目CD=754”))
rs2!当月残高 = DLookup(“当月残高”, “T試算表”, “科目CD=685”) – DLookup(“当月残高”, “T試算表”, “科目CD=754”)
rs2.Update

rs2.Find “科目CD=831”
rs2!前月まで = DLookup(“前月まで”, “T試算表”, “科目CD=755”) + DLookup(“前月まで”, “T試算表”, “科目CD=819”) – DLookup(“前月まで”, “T試算表”, “科目CD=830”)
rs2!貸方発生 = DLookup(“貸方発生”, “T試算表”, “科目CD=755”) + DLookup(“貸方発生”, “T試算表”, “科目CD=819”) – Nz(DLookup(“借方発生”, “T試算表”, “科目CD=819”)) + Nz(DLookup(“貸方発生”, “T試算表”, “科目CD=830”)) – Nz(DLookup(“借方発生”, “T試算表”, “科目CD=830”))
rs2!当月残高 = DLookup(“当月残高”, “T試算表”, “科目CD=755”) + DLookup(“前月まで”, “T試算表”, “科目CD=819”) – DLookup(“当月残高”, “T試算表”, “科目CD=830”)
rs2.Update

rs2.Close
Set rs2 = Nothing
cnn2.Close
Set cnn2 = Nothing

Dim cnn3 As ADODB.Connection
Dim rs3 As ADODB.Recordset

Set cnn3 = CurrentProject.Connection
Set rs3 = New ADODB.Recordset

rs3.Open “T会社情報”, cnn3, adOpenKeyset, adLockOptimistic

rs3!集計始期 = Me!テキスト0
rs3!集計終了 = Me!テキスト2
rs3.Update
rs3.Close
Set rs3 = Nothing
cnn3.Close
Set cnn3 = Nothing

End Sub

異常に長いコードになってしまいました。
これを実行すると試算表が出来上がります。
======================================
久しぶりの更新です。事務所のブログに注力してしまい、つい
こちらのブログがおろそかになってしまいました。

本日も最後までお読みいただきありがとうございます。