Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2016)
縦横追加
投稿日時: 18/11/16 21:10:59
投稿者: sim2018

お世話になります。
フォームのボタンをクリック時のイベントでテーブル1の列のデータを別のテーブル2に
追加するVBAを作っています。
テーブル1
番号 氏名 カナ 参加 日付
 1  あ  ア  〇  11/16
 1  い  イ  ×  11/16
 2  う  ウ  〇  11/16
 3  え  エ  〇  11/16
 ・  ・  ・  ・  ・
となっています
これをボタンを押して
番号 氏名1 カナ1 参加1 日付1 氏名2 カナ2 参加2 日付2 氏名3 カナ3 参加3 日付3
 1  あ  ア  〇  11/16   い  イ  ×  11/16
 2  う  ウ  〇  11/16
 3  え  エ  〇  11/16
 ・  ・  ・  ・  ・
といった具合に
番号ごとに行に追加したいと考えています。
いま、
 
   Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim lngIdx As Long
   
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("TESTWK", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("TEST", dbOpenDynaset)
   
   Do Until rs1.EOF
       For lngIdx = 1 To 10
           rs2.AddNew
                rs2!番号 = rs1!番号
                rs2!氏名 = rs1.Fields("氏名" & lngIdx)
                rs2!カナ = rs1.Fields("カナ" & lngIdx)
                rs2!参加 = rs1.Fields("参加" & lngIdx)
                rs2!日付 = rs1.Fields("日付" & lngIdx)
            rs2.Update
        Next lngIdx
        rs1.MoveNext
    Loop
   
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
まで書いたのですが、追加されません。
なにがたりないか、どなたかご教授お願いいたします。

回答
投稿日時: 18/11/19 13:40:31
投稿者: sk

引用:
テーブル1
番号 氏名 カナ 参加 日付
 1  あ  ア  〇  11/16
 1  い  イ  ×  11/16
 2  う  ウ  〇  11/16
 3  え  エ  〇  11/16
 ・  ・  ・  ・  ・
となっています

このテーブルの主キー(またはユニークキー)は
どのフィールドなのでしょうか。

投稿日時: 18/11/19 15:19:50
投稿者: sim2018

お世話になります。
主キーは設定していません。
ユニークキーとしては"番号"になります。
なお、あとで気づいたのですが、この記述は横から縦にする記述でした。
求めているのは、縦から横です。
VBAでどのように書けばいいかご教授願えると助かります。
よろしくお願いいたします。

回答
投稿日時: 18/11/19 16:20:03
投稿者: sk

引用:
主キーは設定していません。
ユニークキーとしては"番号"になります。

引用:
テーブル1
番号 氏名 カナ 参加 日付
 1  あ  ア  〇  11/16
 1  い  イ  ×  11/16
 2  う  ウ  〇  11/16
 3  え  エ  〇  11/16
 ・  ・  ・  ・  ・
となっています

複数のレコードで値が重複しているので
[番号]だけではユニークキーとはなり得ません。
 
例えば[番号]と[氏名]の値の組み合わせが一意になるのであれば、
これら 2 つのフィールドが複合ユニークキーとなり得ます。
( 1 つの[番号]グループにおいて、[氏名]の値が同じである
レコードが 2 件以上存在してはならない)
 
引用:
なお、あとで気づいたのですが、この記述は横から縦にする記述でした。
求めているのは、縦から横です。

[TEST]が正規形テーブル、[TEXTWK]が非正規形テーブルであると
仮定した場合は、次のようなコードを実行することになるかと。
 
(標準モジュール)
-------------------------------------------------------------------
Sub subConvertRecords()
 
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset
    Dim lngIdx As Long
    Dim strSQL As String
    
    Set db = CurrentDb
     
    strSQL = "SELECT [番号]" & _
             " FROM [TEST]" & _
             " WHERE [番号] IS NOT NULL" & _
             " GROUP BY [番号]" & _
             " ORDER BY [番号]"
    Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)
     
    Set rs2 = db.OpenRecordset("TESTWK", dbOpenDynaset)
     
    Do Until rs1.EOF
        strSQL = "SELECT *" & _
                 " FROM [TEST]" & _
                 " WHERE [番号] = " & rs1![番号] & _
                 " ORDER BY [氏名]"
        Set rs3 = db.OpenRecordset(strSQL, dbOpenSnapshot)
        lngIdx = 1
         
        rs2.AddNew
        rs2![番号] = rs1![番号]
        Do Until (rs3.EOF) Or (lngIdx > 10)
            rs2.Fields("氏名" & lngIdx) = rs3![氏名]
            rs2.Fields("カナ" & lngIdx) = rs3![カナ]
            rs2.Fields("参加" & lngIdx) = rs3![参加]
            rs2.Fields("日付" & lngIdx) = rs3![日付]
            rs3.MoveNext
            lngIdx = lngIdx + 1
        Loop
        rs2.Update
         
        Set rs3 = Nothing
        rs1.MoveNext
    Loop
     
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
End Sub
-------------------------------------------------------------------
 
但しこの場合、[TESTWK]の 1 から 10 までの添え字が付加されている
各フィールドに対して[TEST]のどのレコードの値が代入されるかは、
[TEST]側における[氏名]の値(昇順)によって決定されることになります。

投稿日時: 18/11/19 16:55:01
投稿者: sim2018

お世話になります。
早速の返信ありがとうございます。
 
ユニークキーについては、2つの項目が必要なのですね、わかりました。
 
最終的にしたいのは、レポートに表示することを目指しています。
 
番号を基準として
レポートの1ページのデータが
(番号 氏名 カナ 参加 日付)×32行
 
まで縦のデータを横に追加して
いき、33行になると次ページに改行してページを増やしていきます。
イメージは
--------------------------------------------------
番号 《1》
氏名1 カナ1 参加1 日付1    ---1行目
氏名2 カナ2 参加2 日付2    ---2行目
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
氏名32 カナ32 参加32 日付32    ---32行目
                     1ページ
---------------------------------------------------
番号 《2》
氏名33 カナ33 参加33 日付33    ---33行目
氏名34 カナ34 参加34 日付34    ---34行目
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
氏名64 カナ64 参加64 日付64    ---64行目
                     2ページ
---------------------------------------------------
[b]番号
 《2》
氏名1 カナ1 参加1 日付1    ---1行目
氏名2 カナ2 参加2 日付2    ---2行目
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
氏名32 カナ32 参加32 日付32    ---32行目
                     1ページ
---------------------------------------------------
という感じで
 
縦にある番号は重複しますが、同じ番号の中にある名前は重複しません。
これらをもとに教えていただけるとありがたいです。
よろしくお願いいたします。
 
 
 
 

投稿日時: 18/11/19 17:10:55
投稿者: sim2018

修正します、
イメージは
--------------------------------------------------
番号 《1》
氏名1 カナ1 参加1 日付1    ---1行目
 氏名2 カナ2 参加2 日付2    ---2行目
  ・  ・   ・  ・ 
  ・  ・   ・  ・ 
  ・  ・   ・  ・ 
 氏名32 カナ32 参加32 日付32    ---32行目
                      1ページ
---------------------------------------------------
[b]番号 《1》
[/b] 氏名33 カナ33 参加33 日付33    ---33行目
 氏名34 カナ34 参加34 日付34    ---34行目
  ・  ・   ・  ・ 
  ・  ・   ・  ・ 
  ・  ・   ・  ・ 
 氏名64 カナ64 参加64 日付64    ---64行目
                      2ページ
---------------------------------------------------
[b]番号 《2》
氏名1 カナ1 参加1 日付1    ---1行目
 氏名2 カナ2 参加2 日付2    ---2行目
  ・  ・   ・  ・ 
  ・  ・   ・  ・ 
  ・  ・   ・  ・ 
 氏名32 カナ32 参加32 日付32    ---32行目
                      1ページ
---------------------------------------------------
となります。

回答
投稿日時: 18/11/19 17:16:11
投稿者: sk

引用:
最終的にしたいのは、レポートに表示することを目指しています。
  
番号を基準として
レポートの1ページのデータが
(番号 氏名 カナ 参加 日付)×32行

ここまでは分かるとして、
 
引用:
縦のデータを横に追加していき、33行になると
次ページに改行してページを増やしていきます。

何故そのレイアウトで行列変換を行なう必要があるのかが
さっぱり分かりません。
 
引用:
番号 《1》
氏名1 カナ1 参加1 日付1    ---1行目
氏名2 カナ2 参加2 日付2    ---2行目
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
氏名32 カナ32 参加32 日付32    ---32行目

正規形テーブル[TEST]をレコードソースとするレポートを作成して、
[番号]のグループヘッダーセクションを追加なされば充分なのでは
ないでしょうか。

投稿日時: 18/11/19 19:28:57
投稿者: sim2018

返事が遅れましたが、
すでにあるレポートに値を代入するイメージです。
確かに
 
正規形テーブル[TEST]をレコードソースとするレポートを作成して、
[番号]のグループヘッダーセクションを追加なされば充分なのでは
ないでしょうか。
 
言われてるやり方もありますが、そのやり方で不明な部分が2点あります。
まず レポートは横書きで(←後から後から条件追加で申しわけありません)
------------------------------------------------------------------------------------------------
番号 《1》
氏名1 カナ1 参加1 日付1   --1行目   |氏名17 カナ17 参加17 日付17 ---17行目    
氏名2 カナ2 参加2 日付2   --2行目   |氏名18 カナ18 参加18 日付18 ---18行目      ・  ・   ・  ・          | ・  ・   ・  ・ 
 ・  ・   ・  ・          | ・  ・   ・  ・ 
 ・  ・   ・  ・          | ・  ・   ・  ・ 
氏名16 カナ16 参加16 日付16 --16行目  |氏名32 カナ32 参加32 日付32 --32行目  
                    1ページ
------------------------------------------------------------------------------------------------
このようにレポートを2列にする方法がわかりません。
 
2つ目が番号でグループを組んで ページの最後32 64 96・・・行に満たない場合もありますが、その場合にもレポートの最後が縮まないような空白の状態?のレポートにするつもりなのです。
それを作るにはやはりデータの代入しかないのかなと思っています。
 
わかりにくかったらすみません。再度指摘してください。
よろしくお願いいたします。
 
 
 
 

回答
投稿日時: 18/11/20 01:20:25
投稿者: hatena
投稿者のウェブサイトに移動

sim2018 さんの引用:

------------------------------------------------------------------------------------------------
番号 《1》
氏名1 カナ1 参加1 日付1   --1行目   |氏名17 カナ17 参加17 日付17 ---17行目    
氏名2 カナ2 参加2 日付2   --2行目   |氏名18 カナ18 参加18 日付18 ---18行目      ・  ・   ・  ・          | ・  ・   ・  ・ 
 ・  ・   ・  ・          | ・  ・   ・  ・ 
 ・  ・   ・  ・          | ・  ・   ・  ・ 
氏名16 カナ16 参加16 日付16 --16行目  |氏名32 カナ32 参加32 日付32 --32行目  
                    1ページ
------------------------------------------------------------------------------------------------
このようにレポートを2列にする方法がわかりません。

レポートのデザインビューでリボンの[ページ設定]タブ-[ページ設定]-[レイアウト]で
「列数」を「2」に設定すればいいでしょう。
下記を参考にしてください。
 
複数列のレポートでヘッダーも複数表示する方法 - hatena chips
https://hatenachips.blog.fc2.com/blog-entry-199.html
 
sim2018 さんの引用:

2つ目が番号でグループを組んで ページの最後32 64 96・・・行に満たない場合もありますが、その場合にもレポートの最後が縮まないような空白の状態?のレポートにするつもりなのです。
それを作るにはやはりデータの代入しかないのかなと思っています。

番号グループで改ページすればいいのでは。
番号グループフッターを表示させて、高さは0にして、「改ページ」プロパティを「カレントセクションの後」に設定すればいいでしょう。

投稿日時: 18/11/20 08:30:05
投稿者: sim2018

skさん
hatenaさん
ありがとうございます。
 
2列にできることは、確認しました。
 
だだ、やはり今ある様式に代入という形をとりたいので、当初の方法をとりたいと思います。
 
そこで、skさんの教えてくださった
 
(標準モジュール)
-------------------------------------------------------------------
Sub subConvertRecords()
   
     Dim db As DAO.Database
     Dim rs1 As DAO.Recordset
     Dim rs2 As DAO.Recordset
     Dim rs3 As DAO.Recordset
     Dim lngIdx As Long
     Dim strSQL As String
      
     Set db = CurrentDb
       
     strSQL = "SELECT [番号]" & _
              " FROM [TEST]" & _
              " WHERE [番号] IS NOT NULL" & _
              " GROUP BY [番号]" & _
              " ORDER BY [番号]"
     Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)
       
     Set rs2 = db.OpenRecordset("TESTWK", dbOpenDynaset)
       
     Do Until rs1.EOF
         strSQL = "SELECT *" & _
                  " FROM [TEST]" & _
                  " WHERE [番号] = " & rs1![番号] & _
                  " ORDER BY [氏名]"
         Set rs3 = db.OpenRecordset(strSQL, dbOpenSnapshot)
         lngIdx = 1
           
         rs2.AddNew
         rs2![番号] = rs1![番号]
         Do Until (rs3.EOF) Or (lngIdx > 10)
             rs2.Fields("氏名" & lngIdx) = rs3![氏名]
             rs2.Fields("カナ" & lngIdx) = rs3![カナ]
             rs2.Fields("参加" & lngIdx) = rs3![参加]
             rs2.Fields("日付" & lngIdx) = rs3![日付]
             rs3.MoveNext
             lngIdx = lngIdx + 1
         Loop
         rs2.Update
           
         Set rs3 = Nothing
         rs1.MoveNext
     Loop
       
     Set rs1 = Nothing
     Set rs2 = Nothing
     Set db = Nothing
   
 End Sub
-------------------------------------------------------------------
で実行してみましたが、「パラメータが少なすぎます1を指定してくださいという」
メッセージがでました。何が原因として考えられますか?
また、教えていただけると助かります。
よろしくお願いいたします。

回答
投稿日時: 18/11/20 09:33:22
投稿者: sk

sim2018 さんの引用:
「パラメータが少なすぎます1を指定してくださいという」
メッセージがでました。何が原因として考えられますか?

テーブル[TEST]に、[番号]または[氏名]という名前の
フィールドが存在しないからでは。
 
仮にテーブル[TEST]及び[TESTWK]の両方に
[番号]という名前のフィールドが存在している
とするならば、
 
引用:
strSQL = "SELECT *" & _
         " FROM [TEST]" & _
         " WHERE [番号] = " & rs1![番号] & _
         " ORDER BY [氏名]"
Set rs3 = db.OpenRecordset(strSQL, dbOpenSnapshot)

例えば、[氏名]というフィールドが存在している
正規形テーブルは[TEST]ではなく[TESTWK]である
(レコードが追加される非正規形テーブルは
[TESTWK]ではなく[TEST]である)可能性が考えられます。
 
なお、私の例示したコードは以下の前提に基づいて
記述したのですので、それぞれのテーブル/フィールドの
名前は実際のものに合わせて適宜修正するようにして下さい。
 
sk さんの引用:
[TEST]が正規形テーブル[TESTWK]が非正規形テーブルであると
仮定した場合は、次のようなコードを実行することになるかと。

 
sim2018 さんの引用:
だだ、やはり今ある様式に代入という形をとりたいので、
当初の方法をとりたいと思います。

sim2018 さんの引用:
イメージは
--------------------------------------------------
番号 《1》
氏名1 カナ1 参加1 日付1    ---1行目
氏名2 カナ2 参加2 日付2    ---2行目
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
氏名32 カナ32 参加32 日付32    ---32行目
                     1ページ
---------------------------------------------------
番号 《1》
氏名33 カナ33 参加33 日付33    ---33行目
氏名34 カナ34 参加34 日付34    ---34行目
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
 ・  ・   ・  ・ 
氏名64 カナ64 参加64 日付64    ---64行目

Access のテーブルに定義できるフィールドの数は最大 255 個までです。
無制限に定義できるわけではありません。
 
[番号]を差し引けば 254 個、[氏名], [カナ], [参加], [日付]の
代入先となる 4 個のフィールドを 1 組とするならば
254 / 4 = 63 組までが限界です。
 
1 つの[番号]グループにつき 64 件以上のレコードが
正規形テーブルに格納されるケースがあり得るのであれば、
その方式を採用されるのはお奨め致しかねます。
 
hatena さんが回答されたように、レポートのページ設定と
グループヘッダーセクションの設定で何とかされた方が
よいでしょう。

投稿日時: 18/11/20 16:11:06
投稿者: sim2018

返事が遅れましたが、
 
254 / 4 = 63 組までが限界です。
 
そのような制約があるのですね。
今回は20組まではないので、なんとか大丈夫かと思います。
 
「パラメータが・・・」
についてはまだ未解決ですが....
 
今は別なPCで(AC2000で)同じように
フォームのボタンをクリック時にイベントで先ほどの標準モジュール
を記述しているのですが、今度は
 
「実行時エラー3464 抽出条件で型が一致しません」
 
    Do Until rs1.EOF
          strSQL = "SELECT *" & _
                   " FROM [TEST]" & _
                   " WHERE [番号] = " & rs1![番号] & _
                   " ORDER BY [氏名]"
 → Set rs3 = db.OpenRecordset(strSQL, dbOpenSnapshot)
 

と別なエラーが出ます。いろいろ調べてみるのですがいまだに
解決できません。
たびたびで申し訳ありませんが、解決策として他の記述があれば
よろしくお願いいたします。

回答
投稿日時: 18/11/20 17:48:54
投稿者: sk

引用:
「実行時エラー3464 抽出条件で型が一致しません」

引用:
strSQL = "SELECT *" & _
         " FROM [TEST]" & _
         " WHERE [番号] = " & rs1![番号] & _
         " ORDER BY [氏名]"

テーブル[TEST](及び[TESTWK])の[番号]の
データ型は何なのでしょうか。
 
仮に数値型ではなくテキスト型である場合は、
次のように書き換えればよろしいでしょう。
 
--------------------------------------------------------
 
strSQL = "SELECT *" & _
         " FROM [TEST]" & _
         " WHERE [番号] = '" & rs1![番号] & "'" & _
         " ORDER BY [氏名]"
 
--------------------------------------------------------

投稿日時: 18/11/20 19:24:00
投稿者: sim2018

早速の返事ありがとうございます。
テーブルは全てテキスト型です。
 
上記の記述にかえたところ
 
入力テーブルまたはクエリ´false´が見つけることができませんでした。そのテーブルや
クエリが存在していること、または名前が正しいことを確認して下さい。
 
とメッセージがでました。
テーブル名は間違えていないと思うのですが…
 
何度もすみません考えられる部分を指摘していただけると助かります。
宜しくお願い致します。
現在の記述は
 
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset
    Dim lngIdx As Long
    Dim strSQL As String
     
    Set db = CurrentDb
      
    strSQL = "SELECT [番号]" & _
             " FROM [TEST]" & _
             " WHERE [番号] IS NOT NULL" & _
             " GROUP BY [番号]" & _
             " ORDER BY [番号]"
              
    
    Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)
      
    Set rs2 = db.OpenRecordset("TESTWK", dbOpenDynaset)
      
    Do Until rs1.EOF
        strSQL = "SELECT *" & _
                 " FROM [TEST]" & _
                 " WHERE [番号]" = " & rs1![番号]& " '" & _
                 " ORDER BY [氏名]"
                  
        Set rs3 = db.OpenRecordset(strSQL, dbOpenSnapshot)
        lngIdx = 1
          
        rs2.AddNew
        rs2![番号] = rs1![番号]
        Do Until (rs3.EOF) Or (lngIdx > 10)
            rs2.Fields("氏名" & lngIdx) = rs3![氏名]
            rs2.Fields("カナ" & lngIdx) = rs3![カナ]
            rs2.Fields("参加" & lngIdx) = rs3![参加]
            rs2.Fields("日付" & lngIdx) = rs3![日付]
            rs3.MoveNext
            lngIdx = lngIdx + 1
        Loop
        rs2.Update
          
        Set rs3 = Nothing
        rs1.MoveNext
    Loop
      
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
となっています。

回答
投稿日時: 18/11/21 09:16:46
投稿者: sk

sim2018 さんの引用:
strSQL = "SELECT *" & _
         " FROM [TEST]" & _
         " WHERE [番号]" = " & rs1![番号]& " '" & _
         " ORDER BY [氏名]"

sk さんの引用:
strSQL = "SELECT *" & _
         " FROM [TEST]" & _
         " WHERE [番号] = '" & rs1![番号] & "'" & _
         " ORDER BY [氏名]"

投稿日時: 18/11/21 10:06:37
投稿者: sim2018

skさん
ありがとうございます。
できました!
いろいろなアドバイス本当にありがとうございます。
 
あと、32組後につきの行に追加する方法を考えてみます。
また不明な点があればお聞きするかもしれません。
 
できるまでお付き合いいただき本当にありがとうございました。