文庫本管理システム(改)

R7/11/10:更新

以前から気になっていたシステム内の<マクロ>を、全て VBA に変えました。『マクロをVisual Basic に変換』というツールがあるので、一から作るわけではなくてあまり苦労はなく、モジュールに組み込むときに少しだけ試行錯誤した程度です。これを機に『著者入力編集』に漢字の検索窓を追加しました。
以下はR3年の掲載内容で、R7/11/10:更新分は、末尾です。

　文庫管理システム(Access2003)

文庫本管理システム(R3年掲載)

• 久し振りにMS_Accessを触りました。BOOK_OFFで調達してくる文庫本を、Excelで記録していたのですが、数が馬鹿にならなくなって来て、簡便に検索と新規入力が出来るように簡易データベース化しました。
• 手持ちのAccessは2003年版で、機能的な制約はあるものの、題名と著者・出版社の記録・検索程度ならまだまだ有用だと思われますが、こちらの頭の中が錆びて来ておりますから、作成の記録として残すためにWebに掲載しました。
• 当初はマクロのみで動かせることが出来れば・・と考えていましたが、スムーズな動きを求めてVBA(イベント・プロシジャー)も使うことになりました。

---

テーブル

• 文庫本(題名)と著者名と出版社名のテーブルです。
• T_文庫本テーブルの[著者コード]と[出版社コード]は、T_著者名テーブルの[著者NO(key設定)]やT_出版社テーブルの[出版社NO(Key設定)]とリレーションを張ります。

---

クエリー

• 3つのテーブルを基に、上と右の２つの選択クエリ―を作成します。
• Q_文庫一覧は、題名や著者名で検索するフォーム(F_検索一覧)と本の題名等を登録・編集するフォーム(F_文庫入力編集)の基になるクエリーです。
• Q_著者は著者名を登録・編集するフォームの基になるもので、[かな]の並べ替え欄には"昇順"の設定をします。

---

フォーム(F_検索一覧)

• ヘッダー上部の「検索開始」「検索値クリア」「閉じる」はコマンドボタンで、クリック時の動作は、それぞれ「M_検索開始」、「M_検索値クリア」、「M_終了1」のマクロが設定されています。
• ヘッダー中ほどの非連結表示の空白はテキストボックスで、ここに本の題名の一部や著者名の一部を入力して、検索します。なお、テキストボックスのプロパティで、IME入力モードはオンにしています。
• 詳細部は表形式のフォームで、Q_文庫一覧を基にしています。
• 「M_検索開始」マクロの内容は、"フィルタの実行"で、
  where 条件式に：[題名] Like "*" & [Forms]![F_検索一覧]![題名検索] & "*" And [著者名] Like "*" & [Forms]![F_検索一覧]![著者名検索] & "*"
  が入っていて、題名や著者名の一部でも検索可能になっています。
• 「M_検索値クリア」の内容は、[題名検索]と[著者名検索]に""を代入するもの「値の代入」ですし、「M_終了1」のマクロは、「フォームを閉じる」アクションです。

---

フォーム(F_著者入力編集)

• レコードソースはQ_著者で、単票フォームです。
• プロパティ/イベント/開く時/に「マクロ3.移動」で[カナケンサク]にコントロールを移動します。
• ヘッダー部に非連結のテキストボックス名：[カナケンサク]を配置し、イベントの更新後処理に「マクロ3.検索」を設定してあります。
• 「マクロ3.検索」でデータが見つからない時には、新規入力処理へ進みます。
• 「新規入力」ボタンには、「レコードの移動」:(新しいレコード)と「コントロールの移動」:[著者名]を行うマクロが設定されています。
• 終了ボタンには、「F_文庫入力編集」から呼ばれた時のために、下記のVBA(イベント・プロシージャ)がセットされています。

F_著者入力編集の終了ボタン(コマンド8)のプロパティ/イベント/クリック時のプロシージァー

Private Sub コマンド8_Click()
    'フォームを閉じる
    DoCmd.Close acForm, "F_著者入力編集"
    '「F_文庫入力編集」が開いていたら[著者コード]を空にして再クエリ
    If SysCmd(acSysCmdGetObjectState, acForm, "F_文庫入力編集") = 1 Then
        Forms![F_文庫入力編集]![著者コード] = ""
        Forms![F_文庫入力編集]![著者コード].Requery
    End If
End Sub

---

フォーム(F_文庫入力編集)

• レコードソースはQ_文庫一覧で、単票フォームです。
• プロパティ/イベント/開く時/に「マクロ2.移動」で[題名検索]にコントロールを移動します。
• ヘッダー部に非連結のテキストボックス名：[題名検索]を配置し、イベントの更新後処理に「マクロ2.検索」を設定してあります。
• 「マクロ2.検索」でデータが見つからない時には、新規入力処理へ進みます。
• 「新規入力」ボタンには、「レコードの移動」:(新しいレコード)と「コントロールの移動」:[題名]を行うマクロが設定されています。
• 終了ボタンには、フォームを閉じるマクロを設定しました。
• 著者を入力するコンボボックスのコントロールソースは、Q_著者の[著者コード]で、表示は２列目の[著者名]です。(連結列：1)
• このコンボボックスのリスト外入力欄には、下記のイベント・プロシージャ(VBA)を設定して確認の後、F_著者入力編集フォームを呼び出して新規入力の後、コンボボックスを再クエリして著者を登録する手順にしました。

コンボボックス[著者コード]のプロパティ/イベント/リスト外入力時のプロシージァー

Private Sub 著者コード_NotInList(NewData As String, Response As Integer)
    'T_著者(Q_著者)に無いデータを入力したら
    Dim Tsuika As Integer
    Response = acDataErrContinue
    Beep
    Tsuika = MsgBox("新しい著者名です。追加しますか？", vbYesNo + vbQuestion, "確認")
    Select Case Tsuika
        Case vbYes
            'フォームを開く
            DoCmd.OpenForm "F_著者入力編集"
            '新規レコードへ移動
            DoCmd.GoToRecord , , acNewRec
          '[著者名]に代入してフォーカス
            Forms![F_著者入力編集]![著者名] = NewData
            Forms![F_著者入力編集]![著者名].SetFocus
        Case vbNo
            Me!著者コード = Null
            Me!著者コード.Dropdown
    End Select
End Sub

---

フォーム(F_蔵書管理メニュー)

• 単票フォームです。
• 各コマンドボタンのクリック時に、それぞれのフォームを呼び出すマクロが設定されています。
• ツール/起動時の設定/で、フォーム/ページの表示欄に設定して、立ち上げ時に自動的に表示されるようにします。

後書き

• 講習用のテキスト作成の経験はありますが、accessのWeb掲載はプロパティの詳細を全て表示できず、難しく感じました。
• イベント・プロシージャ(VBA)をWeb(html)上への表示にも困ったのですが、ネットでツールを見つけて、何とか見やすい形に出来ました。
• 超久しぶりのaccessで、「一応の完成」まで、長い時間が必要でした。悩みながらも楽しんだ数日間でしたが、まだもう少し工夫できそうです。

※R3年の掲載から約３年経過し、この間にメルカリで Access2010 を手に入れました。Access2003で組んだこの文庫本管理も2010上で問題なく稼働していますが、表示の様子が変わった以上に機能も増えているようです。(詳しくないので解説は無理です。)

---

題名・著者名検索フォームの改良(R6年1月更新)

• 検索フォームは、本の題名や著者名で目的の文庫本を探すためのものです。通常の本は一人の著者に対して一つ題名が付けられていますので、題名だけが対象の検索、著者名だけが対象の検索、および両方をANDで繋いだ検索をしています。短編集のように複数の作品が掲載されている場合も、本の題名は一つですからあまり問題にはなりません。
• しかしアンソロジーといった、本の題名は一つでも複数の著者の複数の作品を載せたものもあり、この扱い(検索)をどうするかが頭を悩ませていました。
• アンソロジーに対する従来の処理は、編集者の名前を著者名とするか、著者の中の筆頭もしくは知っている(著者名テーブルに存在する)著者を代表として記録していました。しかしこの方法は簡便ではありますが、不十分さを拭えない思いをしていました。
• そこでデータの記録を次のように変えて、検索フォームでは著者名の検索を備考欄に対しても行うことにしました。
• • 著者テーブル(T_著者)の著者名にアンソロジーを追加する。つまり「アンソロジー」という著者を設けて、複数の著者名は文庫本テーブルの備考欄にカンマ区切りで列記する。
  • 検索フォームの[検索開始]ボタンにはマクロを止めて、次のVBAモジュール(イベントプロシージャ)を張り付ける。
  Private Sub 検索開始_Click()
  ①  k_txt1 = "[題名] Like '*" & Me![題名検索] & "*'"
  ②  k_txt2 = "[著者名] Like '*" & Me![著者名検索] & "*'"
  ③  k_txt3 = "[著者名]='アンソロジー' And [備考] Like '*" & Me![著者名検索] & "*'"
  ④  ken_txt = k_txt1 & " And (" & k_txt2 & " Or " & k_txt3 & ")"
  ⑤  Me.Filter = ken_txt
  ⑥  Me.FilterOn = True
  End Sub
  • ⑤のFilter(プロパティ)に検索文字列をセットして、⑥FilterOnをTrueにすると検索結果が票形式のフォームに反映します。
  • Filterに直接検索文字列を設定してもＯＫですが、非常に記述が煩雑になるので①②③の変数(k_txt?)を定義してそれぞれ検索文字列を入れて置き、④ken_txtにまとめてからFilterに設定します。
  • ①②は題名と著者名の部分一致型の検索です。③は著者名を[備考]欄に求めたものです。
  • ①題名と②著者名は本を特定するため「And」で繋ぐ必要がありますが、②と③は一人の著者名を2つのフィールドで探すため「Or」条件になります。また、①②③を繋ぐときには②と③の条件を先に評価する必要があるため()でくくりました。
  • なお題名及び著者名が完全一致では使いづらいので、①～③には Like演算子を使った「あいまい検索」を設定しています。
  • ダブルクォーテーションとシングルクォーテーションが理解し辛いですが、Filterプロパティは、SQLとVBAのハイブリッドなので「そうなる？」ようです。

　R_結果印刷のデザインビュー

---

検索結果の印刷(R7年7月更新)

• 既読の本をメモしたい時があります。
• Print_Screen という手もありますが、Accessにはリポートという機能がありますから使ってみます。
• 検索で表示されたレコードをそのまま印刷すればよいはずが、意外に苦労しました。
• 同じ著者名のものなら、クエリを作れば複数のレコードのレポートは容易ですが、共通点のない複数レコードの場合は難しく、ネットで『フォームと同じ条件でフィルター』という方法を見つけてやっと実現しました。

• 検索フォーム(F_検索一覧)のヘッダーに<結果印刷>(=標題)のボタンを貼り付けます。
• ボタンのクリックイベントに次のVBAを設定しました。
Private Sub 結果印刷_Click()
'「結果印刷」ボタンをクリックしたら、R_検索結果を開いて
'ken_txtを条件にFilterを掛ける
    DoCmd.OpenReport "R_検索結果", acViewPreview, , ken_txt
End Sub
• つまり、F_検索一覧で使用したフィルター(ken_txt)をそのままR_結果印刷(レポート)に使う設定です。
• 検索結果とレポートでレコードの順番が異なるのは、レポートの題名に「昇順」の設定をしたためです。

後書き２

• Book_Offをよく利用しますが、ずっと以前に既読のものを買ってしまうことがあるため、メモの必要性を感じていました。
• 最近は印刷結果をサイフに入れて置いて、2度買いをチェックしています。
• 1年半ぶりにAccessを触りました。いろいろなことをすっかり忘れてしまっていて情けなくなりましたが、何とか目的を達っせたので少し「ホッと」感があります。

---

マクロをVBAに(R7年11月10日更新)

◎すべてのマクロをVBAに変えました。
◎『検索フォーム』用のVBAです。
Option Compare Database
Option Explicit
Dim ken_txt As String
Dim k_txt1 As String
Dim k_txt2 As String
Dim k_txt3 As String

Private Sub Form_Open(Cancel As Integer)
'最初は何も表示しない
    ken_txt = "題名=''"
    Me.Filter = ken_txt
    Me.FilterOn = True
    Me!題名検索 = ""
    Me!著者名検索 = ""
    DoCmd.GoToControl "題名検索"
End Sub

Private Sub 結果印刷_Click()
'「結果印刷」ボタンをクリックしたら、R_検索結果を開いて
'ken_txtを条件にFilterを掛ける
    DoCmd.OpenReport "R_検索結果", acViewPreview, , ken_txt
End Sub

Private Sub 検索開始_Click()
'題名と著者名は部分一致
    k_txt1 = "[題名] Like '*" & Me![題名検索] & "*'"
    k_txt2 = "[著者名] Like '*" & Me![著者名検索] & "*'"
'著者名は'アンソロジー'で、備考に著者の名前が入っている
    k_txt3 = "[著者名]='アンソロジー' And [備考] Like '*" & Me![著者名検索] & "*'"
'( )内の著者名検索の条件を優先する(記述方法が難しい)
    ken_txt = k_txt1 & " And (" & k_txt2 & " Or " & k_txt3 & ")"
    Me.Filter = ken_txt
    Me.FilterOn = True
End Sub

Private Sub 検索値クリア_Click()
    ken_txt = "題名=''"
    Me.Filter = ken_txt
    Me.FilterOn = True
    Me!題名検索 = ""
    Me!著者名検索 = ""
End Sub

Private Sub 検索閉じる_Click()
        DoCmd.Close acForm, "F_検索一覧"
End Sub

---

◎『文庫入力フォーム』用のVBAです。
Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToControl "文庫入力題名検索"
End Sub

Private Sub 文庫入力新規_Click()
    DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToControl "題名"
End Sub

Private Sub 文庫入力終了_Click()
    DoCmd.Close acForm, "F_文庫入力編集"  
End Sub

Private Sub 文庫入力題名検索_AfterUpdate()
    With CodeContextObject
        If (Forms!F_文庫入力編集!文庫入力題名検索 <> "") Then
            ' 題名検索が空でなければ
            DoCmd.GoToControl "題名"
            DoCmd.FindRecord Forms!F_文庫入力編集!文庫入力題名検索, acAnywhere, False, acDown, False, acCurrent, True
        End If
        If (.題名 Like "*" & .文庫入力題名検索 & "*") Then
            '見つかったら
            Exit Sub
        End If
        ' ヒットしなかった時
            If (MsgBox("新規に入力しますか", 4) = 6) Then
            ' 新しいレコード
            DoCmd.GoToRecord acForm, "F_文庫入力編集", acNewRec
            ' [番号] = Format(DMax("番号","T_文庫本")+1)
            .番号 = Format(DMax("番号", "T_文庫本") + 1)
            ' 「題名」
            DoCmd.GoToControl "題名"
            .題名 = .文庫入力題名検索
            End If
        ' 「題名検索」
        DoCmd.GoToControl "文庫入力題名検索"
        ' 「題名検索」 = ""
        .文庫入力題名検索 = ""
    End With
End Sub

---

◎『著者名入力フォーム』用のVBAです。
〇漢字(=[シメイケンサク])または かな(=[カナケンサク]) で探して、ヒットしなければ、自動的に新規入力になります。
Option Compare Database
'挿入前処理
Private Sub Form_BeforeInsert(Cancel As Integer)
    '連番の作成、現在の最大値＋１を入力
  Me![著者NO] = Format(DMax("著者NO", "T_著者") + 1)
End Sub

Private Sub Form_Open(Cancel As Integer)
    ' 「シメイケンサク」 へ
    DoCmd.GoToControl "シメイケンサク"
End Sub

Private Sub カナケンサク_AfterUpdate()
    With CodeContextObject
        If (Forms!F_著者入力編集![カナケンサク] <> "") Then
            ' 「かな」 へ
            DoCmd.GoToControl "かな"
            DoCmd.FindRecord Forms!F_著者入力編集![カナケンサク], acAnywhere, False, acDown, False, acCurrent, True
        End If
        If (.[かな] Like .[カナケンサク] & "*") Then
            Exit Sub
        End If
        ' 著者名新規
        Call 著者名新規
    End With
End Sub

Private Sub 著者入力新規_Click()
    ' 新しいレコード
    DoCmd.GoToRecord acForm, "F_著者入力編集", acNewRec
    ' [著者名]
    DoCmd.GoToControl "著者名"
End Sub

Private Sub 著者入力終了_Click()
    'フォームを閉じる
    DoCmd.Close acForm, "F_著者入力編集"
    '「F_文庫入力編集」が開いていたら[著者コード]を空にして再クエリ
    If SysCmd(acSysCmdGetObjectState, acForm, "F_文庫入力編集") = 1 Then
        Forms![F_文庫入力編集]![著者コード] = ""
        Forms![F_文庫入力編集]![著者コード].Requery
    End If
End Sub

Private Sub シメイケンサク_AfterUpdate()
      With CodeContextObject
        If (Forms!F_著者入力編集![シメイケンサク] <> "") Then
            ' 「著者名」 へ
            DoCmd.GoToControl "著者名"
            DoCmd.FindRecord Forms!F_著者入力編集![シメイケンサク], acAnywhere, False, acDown, False, acCurrent, True
        End If
        If (.著者名 Like .[シメイケンサク] & "*") Then
            Exit Sub
        End If
        ' 著者名新規
        Call 著者名新規
    End With
End Sub

Function 著者名新規()
    With CodeContextObject
        If (MsgBox("新規に入力しますか", 4) = 6) Then
            ' 新しいレコード
            DoCmd.GoToRecord acForm, "F_著者入力編集", acNewRec
            ' [著者NO] = Format(DMax("著者NO","T_著者")+1)
            .著者NO = Format(DMax("著者NO", "T_著者") + 1)
            .著者名 = Forms!F_著者入力編集![シメイケンサク]
            .[かな] = Forms!F_著者入力編集![カナケンサク]
            ' 「著者名」 へ
            DoCmd.GoToControl "著者名"
            Exit Function
        End If
        ' 「カナケンサク」
        DoCmd.GoToControl "カナケンサク"
        ' "" を代入
        .[カナケンサク] = ""
    End With
End Function

---

後書き３

• マクロを全てVBAに・・なんて大変な作業？・・と思っていたら、変換ツールを見つけて、２日間で主な部分は終えることができました。(それなりの試行錯誤はありました。)
• これを機会に・・と工夫を加えたり、コマンドボタンの『コマンド**』という名称を意味のある名に変えたり、少し充実感もあります。
• VBAの学習が必要・・と感じた数日間でした。

 

[＜ホーム＞へ戻る]