Microsoft Access 掲示板

ありがとうございます。
伝わりづらく申し訳ありません。

①リストフォームでメンバー一覧を表示するとき、所属部署で絞り込みたい時があります。
①①の目的を果たすため、メンバー1人に複数の所属部署が存在した場合、現状はメンバーIDを複数作成し、所属部署だけ違うデータを複数作成していました。
③②が効率的な方法ではないため、１つのメンバーIDに複数の所属部署情報を持たせたいと考えました。
④所属部署が何個存在するか決めづらいので（現状多くて3個くらいだとは思いますが…）サブフォームを作ったほうが良いかと考えました。
⑤サブフォームには今のところ、所属部署のみ表示予定です。（プルダウンで選択）
⑥所属部署のレコードソースは「Q_所属部署」でフィールドは所属部署ID、所属部署名の予定です。
⑦一番の目的は①になります。

以上で、わかりづらい部分があればご指摘ください。申し訳ありません。

hiroton様
ご丁寧に模範例まで教えて頂きありがとうございます。

チェックボックスを使ったことがないド素人なのですが、
チェックボックスON/OFF(True/false)で新規登録モードと切り替えみたいなことができるんですね。
読んだ参考書では、新規登録モード(Touroku)と修正モード(kakikae)、同じフォームで処理出来るような仕様になっていていて、ただし修正モード(kakikae)では主キーのような管理番号は書き換え対象から外してあったりしたので、今回のようなことで困っていました。

色々試してみて、また新たな質問挙げさせてもらうかもしれません。
いつも本当にありがとうございます。

hiroton様
早速の返答、教えて頂きありがとうございます。
サンプルのファイルでうまくいきました。
（フィールド名とTxbox名が少し違うだけの本番用では何故かまだできませんが・・）
フィールドの型が数値型かテキスト型かで書き方がちがうのですね。

Format 関数の参考サイトも教えて頂きありがとうございます。行き詰まったときに参考にさせていただきます。

質問して以来、作成する時間が取れておらず、やっと時間がとれたので確認中です。
おっしゃるとおり、実際の条件は「30日締 2月後30日払」といったものでした。
最初に質問した時きちんとかけておらず申し訳ありません。
今からいただいた回答を確認して作ってみます。わからなければ質問させていただくかもしれません。

どのようなことが希望なのか不明瞭なので、現在の状況を確認するためにいろいろ聞きました。

「F_リスト」「F_メンバー」のレコードソースが同じで方かいいかどうかは目的次第です。

現状はどのようになっていて、どの部分をどのように改善したいのか具体的に質問してもらえると、的確な回答でできると思います。

例えば、

そこで質問ですが、サブフォームを作った場合、「F_リスト」で所属部署の数だけメンバーを表示することはできますでしょうか。

サブフォームを作成するのは「F_メンバー」内にということですよね。
このサブフォームのレコードソースはなんでしょうか。テーブル名、フィールド名を提示してください。
また、『「F_リスト」で所属部署の数だけメンバーを表示する』とは具体的にどのような表示をイメージしていますか。

調整して、同じレコードソースにしました。今まで深く考えていませんでしたが、可能な場所は同じクエリにしたいと思いました！

なるほど！！すごい！うまくいきました。感動です。ありがとうございました。

＞「F_リスト」「F_メンバー」のレコードソースは同じものですか。
ちがうクエリを使っていました。データの更新を同期させる問題もあるし、同じものにしたほうがよいのでしょうか。
＞メンバー、部署のデータを登録するテーブルなど関連するテーブルの、テーブル名、フィールド名、主キー設定
「T_メンバー」名前、担当部署（複数存在）、内線番号などがフィールド名です。主キーは「メンバーID」です。

「F_リスト」は帳票フォームです。

3.（1.2.）が実現できた場合

いくつか方法がありますが、基本は

kaikae処理

If 新規登録処理 Then
    施設ID = ほにゃらら
End If

という作りにすることです。
このフラグは、クエリ検索フォームでボタンのどちらを押したかで切り替わるものなので、例えば（良いサンプルではありません）

クエリ検索フォーム上に、「新規登録モード」チェックボックスを配置
新規登録ボタン

Me!新規登録モード = True
Docmd.OpenForm "クエリ検索フォーム"

詳細/修正ボタン

Me!新規登録モード = False
Docmd.OpenForm "クエリ検索フォーム"

kaikae処理

If Forms!クエリ検索フォーム!新規登録モード Then
    施設ID = ほにゃらら
End If

のように作ると期待する動作ができます。クエリ検索フォーム側で処理することなのでもっと具体的な質問であれば各ボタンを押したときのコードを提示するようにしてください（質問自体新しく立て直したほうがいいかもしれません）

もう一度言いますが、この作りは良いものではありません

・修正モードの際は、フォーム1で選択した「施設ID」に紐付いた値が表示さる

こういう処理ができているようなので、登録修正用フォーム側に「新規登録モード」チェックボックスを置いて設定を変えるような形にすればいいでしょう

ざっくり読んでみて

1.新規登録モードの際、ユーザーが[cmb施設_種別名称]選択した後に、
［Tx連番］の値が、「Tx施設_国cd」と「Tx施設_種別cd」の組合せを1つのグループとして、
1～の連番（値がある場合最大値に+1）が自動で入るようにしたい

施設_国cd   ：数値型　⇒数値3桁
施設_種別cd ：数値型　⇒数値2桁

strWhere = "施設_国cd='" & Me!Tx施設_国cd & "' AND 施設_種別cd='" & Me!Tx施設_種別cd & "'"
↓

strWhere = "施設_国cd=" & Me!Tx施設_国cd & " AND 施設_種別cd=" & Me!Tx施設_種別cd

条件（SQLのWHERE句）の書き方の問題で、フィールドの型とデータの型を合わせる必要があります

strWhere = "施設_国cd='" & Me!Tx施設_国cd & "' AND 施設_種別cd='" & Me!Tx施設_種別cd & "'"

2.（1.）で種別名称を選択した後、[Tx施設ID]に、
数値8桁（国cd;3桁 + 種別cd;2桁 + 連番;2桁）という形式で自動で施設IDの値が入るようにしたい

Me!Tx施設ID = Format(Me!Tx施設_国cd, "000") & Format(Me!Tx施設_種別cd, "00") & Format(new連番, "00")

Format 関数
format関数はよく使う関数なのでいろいろ（検索とかして）勉強するといいですね

ありがとうございました。

UNION ALL にしてください。
UNIONだけだと重複データは1件にまとめられます。

SELECT * FROM クエリ1
UNION ALL
SELECT * FROM クエリ2;

すみません。解決しました・・・
hatena様にSQL文を表示するよう言われたので改めて見直すと必要のないテーブルとクエリが結合されておりエラーが起こったみたいです(><)
ご迷惑をおかけし申し訳ありませんでした。

情報が不足しているにも関わらずお返事いただきありがとうございます。
今色々試していると、恐らく新規登録したデータがクエリに登録されていないからかと思われます。
もとのテーブルからクエリを作っていますが、そのテーブルの方にはデータが入力されていますがなぜかクエリには反映されていないようです。

情報が不足しています。もう少し具体的に、どこでどのようにしたときに、エラーがでるのか説明してください。

社員情報登録フォームで新規レコードを入力したときにエラーがでるのでしょうか。
もし、そうなら、そのフォームのレコードソースのクエリのSQL文を提示してください。

まずは、
「F_リスト」「F_メンバー」のレコードソースは同じものですか。
また、メンバー、部署のデータを登録するテーブルなど関連するテーブルの、テーブル名、フィールド名、主キー設定を提示してください。

また、「F_リスト」は帳票フォームでしょうか。データシートフォームでしょうか。

下記でどうでしょうか。

Private Sub btn新規登録_Click()
    DoCmd.OpenForm "F_テスト", , , , acFormAdd, acDialog
    Me.プルダウン項目.Requery
End Sub

acDialogでダイアログモードで開くと、フォームを閉じるまで、次のコードが実行されないので、
F_テストで入力後、閉じると、プルダウン項目.Requery が実行されます。

>String型にNullは代入できないようになっています。

そういうことだったんですね！とても参考になりました。ありがとうございます。

ありがとうございました。解決いたしました。
Switch関数について調べてみようと思います。

IIf([請求日]>#4/1/2014# And [請求日]<#10/1/2019#,0.08,0.1) AS 消費税率

の部分ですね。Switch関数を使うといいでしょう。

Switch([請求日]>=#10/1/2019#,0.1,[請求日]>=#4/1/2014#,0.08,[請求日]>=#4/1/1997#,0.05,[請求日]>=#4/1/1989#,0.03,True,0) AS 消費税率

軽減税率は考慮してません。

Switch 関数 - Access

本日、解決したのでご報告です。
私は、Left([氏名],Instr([氏名],"　")-1) 部分が問題なのかと思っておりましたが、saku様のおっしゃる通り、姓:"No." & [顧客ID]の連結を試してみたところ、「指定されたフィールドSQLステートメントのFROM句にある複数のテーブルを参照しました。」というエラーで[顧客ID]がまずダメだと分かりました。

そこで
[顧客ID]　→  [テーブル名].[顧客ID]　としたところ、このエラーは回避できました。

さらにもう一つ、問題があったようで、
Left([氏名],Instr([氏名]," ")-1)　　→   Left([氏名],InStr([氏名] & " "," ")-1)
と修正したところ、思い通りの文字列が取得できました。

一人で考えていたらまず考えつかなかったので本当に助かりました。
ありがとうございました。

hiroton様

早速ご返答いただき誠にありがとうございます。

cmb年度_AfterUpdate()

を入れたところ、うまく動きました。

すでに入力済のデータで年度をいじることはほぼないと思いますので、
大丈夫かと思います。
本当にありがとうございます。

ついでに、
新規・修正が同じフォームということなので、このコードだけだと、修正モードの時でも[cmb年度]を修正すると連番が変わります（処理が働きます）

年度（データ）は修正するけど一度取得した番号は変えたくないとかだと仕様に合わせてもうひと手間必要になりますね

思われている通りであっています

テキストボックスの使いまわしをするのであればコントロールソースは非連結にしてVBAで処理することにしましょう。タイミングは

1.新規登録の際、ユーザーが[cmb年度]で年度を選択した後に、

ということなので[cmb年度]の更新後処理で

Private Sub cmb年度_AfterUpdate()
    Dim new年度連番 As Long
    new年度連番 = Nz(DMax("年度別連番", "T統合", "年度='" & Me!cmb年度 & "'"), 0) + 1
    Me!Tx年度別連番 = new年度連番
    Me!Tx管理番号 = "t_" & Me!cmb年度 & Format(new年度連番, "000")
End Sub

hiroton様

ご回答いただき誠にありがとうございます。

2つのコントロールソースに、頂いた式を挿入し、
新規登録時（サンプルコードの1，2の処理）の際は、
やりたいことが実現できました。

しかし、1点問題が出てきてしまい、
申し訳ございませんが、
再度質問させて頂きたいと思います。

もともと2018．2019等過去データには値が入っており、

別途用意してある検索フォームから
「詳細/修正」ボタンを押すと
今回のフォームが開き、
検索フォームで選択された「ID」に連動した値が、
該当の登録フォームの、各レコードの値が挿入される仕様になっています。
（サンプルコードの「4.検索フォームから選択したデータの値取り込み」の部分。）

新規登録モードと修正モードが同じフォームで出来るような形になっています。

今回の修正後、
検索フォームで「詳細/修正」ボタンを押すと、

実行時エラー '2448'
このオブジェクトに値を代入することはできません。

'===============================================================================
'  4.検索フォームから選択したデータの値取り込み

           .Tx年度別連番.Value = oRS("年度別連番").Value
           .Tx管理番号.Value = oRS("管理番号").Value

の部分がデバックで表示され、該当フォームを開くことができなくなってしまいました。

もともとは、「詳細/修正」モードの場合、
非連結のテキストボックスで、
IDに連動したレコードを表示させていただので、
コントロールソースにあらたな数値を設定してしまったので、
エラーがでてしまったのでしょうか？
VBAでの処理が必要なのかどうか、
「新規登録」とは別に「詳細修正フォーム」を新たに作ったほうが良いのか、
質問ばかりで申し訳ありませんが、よろしくおねがいします。

軽く見た感じVBAでどうこうしたいということではないのかな

DMax関数は第三引数に条件を指定できます

1.新規登録の際、ユーザーが[cmb年度]で年度を選択した後に、
［Tx年度別連番］の値が、
選択したテーブルの（年度）グループに紐付いた（年度別連番）で
最大値に+1した値（値がない場合は1～の連番）
が自動で入るようにしたい

Tx年度別連番のコントロールソース

=Nz(DMax("年度別連番","T統合","年度='" & [cmb年度] & "'"),0)+1

2.（1.）で年度を選択した後、[Tx管理番号]に、
テキスト形式で、「t_yyyy_000」という形式で自動で値が入るようにしたい
⇒yyyy：[cmb年度]で選択した値
⇒000：[年度別連番]を000形式にした値

Tx管理番号のコントロールソース

="t_" & [cmb年度] & Format([Tx年度別連番],"000")

saku様、早速ご教示いただいてありがとうございます。
姓:"No." & [顧客ID] & "　" & Left([氏名],Instr([氏名],"　")-1) のコード問題ないのですね。
姓:"No." & [顧客ID]と、姓:"No." & cstr([顧客ID])ができるかどうか、すぐに試したいところなのですが、職場でしかできないため、明日、早速試してみたいと思います。
　

私の環境(Access2019)では、姓:"No." & [顧客ID] & "　" & Left([氏名],Instr([氏名],"　")-1) のコード問題なく表示されました。環境が違うからかなぁ・・

顧客IDはオートナンバーと思いますが、まずは、姓:"No." & [顧客ID]で連結できるか試してみては？
姓:"No." & cstr([顧客ID])　として連結してみても出来ませんか？

コード記述はそれぞれあると思いますが以下のようにしてみてはどうでしょうか？

Dim infoPath As String

'infoPath変数に代入するのであれば先に判定した方が良いと思います。
'コントロールの値が、空白("")またはNULL値かによって条件処理
if len(nz(trim(Me!txt客パス)))<>0 then
    infoPath = trim(Me!txt客パス)
else
    MsgBox "客パスが入力されていません。",vbOKOnly + vbExclamation
    exit sub
end if

'txt情報は、コントロール名かな？
'上記と同じように空白("")またはNULL値かによって条件処理
if len(nz(trim(Me!txt情報)))<>0 then
    'AcDirは自作の関数でしょうか？　そのままｶｷｶｷ..
    If AcDir(infoPath, vbDirectory) <> "" Then
        Shell "Explorer.exe " & Me!txt客パス, vbNormalFocus
    else
        MsgBox "保存しているパスが存在しません。パスを確認してください。",vbOKOnly + vbExclamation
        exit sub
    end if
else
    MsgBox "先に情報を入力してください。",vbOKOnly + vbExclamation
    exit sub
end if

ポイントは、空白(""=vbnullstring)または、NULL値どちらでも入力文字数が０(ゼロ)であれば未入力としています。
参考になれば幸いです。

Me!txt客パス の値がNullになっていませんか？

String型にNullは代入できないようになっています。
Variant型にするか、
代入時に　If (IsNull(n) = False) Then　等でNullを代入しないようにしたらどうでしょうか…。

それ以降のif節内については素人なのでわかりかねますが……申し訳ないです。

出ませんでした。
ありがとうございました。
これで、業務を早くすることができます。

パスワードがかかっていても普通にインポートはできます。
一度、手動でインポートしてみてください。パスワード無しにインポートできると思います。

下記のコードのみで実行してもエラーが出ますか。

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MTtest", "C:\Users\Desktop\aaaa.xlsx", True, "Sheet1!W4:AF200"

strDocName = "C:\Users\Desktop\aaaa.xlsx"です。
失礼しました。
エラーの箇所は、
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "MTtest",
"C:\Users\Desktop\aaaa.xlsx", True, "Sheet1!W4:AF200"

です。

元ファイルのExcelにはPWがかかってまして、

   With oApp
        .Visible = True
        .Workbooks.Open FileName:=strDocName, _
         Password:="abcd"

        .ActiveWorkbook.Close False
        .Quit
    End With
    Set oApp = Nothing

これが必要かなと思いまいした。

お手数かとおもいますが、よろしくお願いします。

どのコードがエラーが出てますか。

strDocName = "C:\Users\Desktop\aaaa"
これは拡張子がないですが、実際のものかちゃんと拡張子ついてますか。

あと、エクセルブックをインポートするだけなら、
「Excelファイルオープン」のコードは不要です。

hatenaさん、申し訳ありませんでした。リンクテーブルを削除してから再リンクの方法をテーブルのインポートで行ったらパスワード入力画面が出て、入力後うまくいったようです。なにせ、アクセスを使いだして20年以上になりますが、当方70歳でいろいろ苦慮しています。アドヴァイス有難うございました。

hatenaさん、有難うございました。リンクテーブルを削除してから再リンクしましたが、、「パスワードが間違っています」と表示されて、パスワードの入力画面が出ないのでテーブルリンクができませんでした。

リンクマネージャーを使わずに、リンクテーブルを削除してからリンクしなおしたらどうなりますか。

hiroton様
失礼しました、学生マスタとは学生テーブルの打ち間違いです*本文を修正いたしました。

SQLについて確認いたしました、想定していた挙動どおりです。
迅速にご教示いただき誠にありがとうございました。

１．学生テーブル

SELECT 学生マスタ., "★" AS 取得済成績
FROM 学生マスタ;

学生マスタとは何者でしょう？

学生の情報が抜け落ちてしまっているクエリ１をつかって計算させるのはめんどくさいです。専用にクエリを作り直してしまったほうがいいでしょう

SELECT 学生テーブル.学生番号, 学生テーブル.氏名, Nz(Sum(IIf([修得年度]="2019",1)),0) AS 取得済成績
FROM 学生テーブル LEFT JOIN 成績テーブル ON 学生テーブル.学生番号 = 成績テーブル.学生番号
GROUP BY 学生テーブル.学生番号, 学生テーブル.氏名;

これは、ACCESSのデザインビューで作成したクエリをそのままSQLビューで表示したものです。SQLビューに張り付けてデザインビューに切り替えれば「クエリのデザインビュー例」が確認できます

修正版

コントロールソース｜='<font style="BACKGROUND-COLOR:#' & Right("00" & Hex([カラーコード]),2) & Mid(Hex([カラーコード]+16777216),4,2) & Mid(Hex([カラーコード]+16777216),2,2) & '">     </font>'

RGB並び順を修正
HTMLのカラーコード指定は6桁（3桁でもいいけど）必要なので10進数→16進数変換で特定の場合に上位桁がなくなってしまうのを修正
divタグは不要っぽかったので削除

RGBは確かに逆順にしないとダメでした。いい感じの技術資料が見つけられなかったんですが、実装次第で異なる場合もあるようですRGBのHex並びが逆（AddinBox/VBAユーザーの為のOpenOffice.org 備忘録さん）
これはもう、VBAではそう、としか言いようがなさそうです

カラーコード表記は例えば「#FF0000」で、最初の#を除いて先頭2文字が1つ目のグループで「R」の量を表す、3～4文字目が・・・
となっていて、「桁」自体は存在しません。これを数値にする場合、「桁」を与える必要があります

数値で同様のことを考えると、1つ目のグループは一の位で2つ目のグループは十の位で・・・と考えることができます。そして、これを文字であらわすときは一番大きいグループから表記します。

一の位が３
十の位が２
百の位が１
ならば３×１＋２×１０＋１×１００　→　１２３

これをRGBに当てはめ、
1つ目のグループ（R）がFF
2つ目のグループ（G）が00
3つ目のグループ（B）が00
ならば、FF×1＋00×256＋00×256×256　→　&H0000FF

このように考えると
カラーコード表示のRGB(255,0,0)→#FF0000
16進数表示のRGB(255,0,0)→&H0000FF

のように、文字の並び順だけ見れば逆になるという表現方法も間違いとは言えないことがわかると思います