Microsoft Access 掲示板

'非連結フォーム[S_1]のフォームモジュール
Option Compare Database
Option Explicit

Private Const SourceTableName As String = "T_1"

'コマンドボタン[cmdUpdate]の[クリック時]イベント
Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

    '入力チェック処理を実行し、その結果が False だった場合
    If InputCheck() = False Then
        'このプロシージャを抜ける
        Exit Sub
    End If

    Dim lngResult As VbMsgBoxResult

    '実行確認ダイアログを表示し、どのボタンがクリックされたかを受け取る
    lngResult = MsgBox("レコードの更新を実行しますか？", _
                        vbQuestion + vbYesNo + vbDefaultButton2, _
                        "実行確認")

    '[いいえ]ボタンがクリックされた場合
    If lngResult = vbNo Then
        'このプロシージャを抜ける
        Exit Sub
    End If

    Dim strSQL As String

    'UPDATE 文の生成処理を実行し、その結果を変数 strSQL に渡す
    strSQL = CreateUpdateStatement()

    '生成に失敗した場合
    If strSQL = "" Then
        'このプロシージャを抜ける
        Exit Sub
    End If

    Debug.Print strSQL

    DoCmd.SetWarnings False
    'SQL 文の実行
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    MsgBox "レコードが更新されました。", _
           vbInformation, _
           "実行完了"

'終了処理
Exit_cmdUpdate_Click:
On Error Resume Next

    DoCmd.SetWarnings True

    Exit Sub

'エラー時処理
Err_cmdUpdate_Click:

    Dim strErrTitle As String
    Dim strErrMsg As String

    strErrTitle = "実行時エラー (" & Me.Name & ".cmdUpdate_Click)"
    strErrMsg = Err.Number & ": " & Err.Description

    Debug.Print strErrTitle
    Debug.Print strErrMsg

    MsgBox strErrMsg, vbCritical, strErrTitle

    Resume Exit_cmdUpdate_Click
End Sub

Ｔ_１テーブルの上記フィールドを非連結フォームに入力値に更新したい

ＳＱＬ部の[Forms]![S_1]![c1～C4]を変数にした場合はどう記述するといいでしょうか？

各非連結コントロールの値を「 SQL 文におけるリテラル」として組み込みたい、
という意味でおっしゃっているのであれば、その SQL の実行時において、
いずれかの非連結コントロールの値が Null であることを許容するか否か次第です。

単純に Null 値をそのまま文字列連結させてしまうと、実行時に構文エラーが
発生する可能性が極めて高いでしょう。

hatena様　ありがとうございます。そのコードで問題なく更新できました。ＶＢＡ内での改行が絡み頭が混乱してました。
一つ教えて頂きたいのですが、ＳＱＬ内でフォーム値を使う場合も変数と同じ様に＆でつなぎ外に出さないといけないのですよね？　質問時の　"SET [F1]=[Forms]![S_1]![c1], " & _　は本来間違っているのですね？（ "SET [F1]=" & [Forms]![S_1]![c1] & ", " & _　が正しい）　たまたま動作していたのですね？　

skさんありがとうございます。これに関わる追加処理は現在ありませんので、以前色々処理させた時に間違った事があったと思います。様子みてみます。お世話になりました。

変数は文字列の外(""の外)に出して&で結合すればいいでしょう。
あと、SQL内では日付時刻型は#で囲む、文字列は'で囲みます。

Dim c1 As Long, c2 As Currency, c3 As Date, c4 As String
c1 = [Forms]![S_1]![c1]
c2 = [Forms]![S_1]![c2]
c3 = [Forms]![S_1]![c3]
c4 = [Forms]![S_1]![c4]
strSQL1 = "UPDATE [T_1] " & _
                 "SET [F1]=" & c1 & ", " & _
                     "[F2]=" & c2 & ", " & _
                     "[F3]=#" & Format(c3,"yyyy/mm/dd") & "#, " & _
                     "[F4]='" & c4 & "' " & _
                  "WHERE [ID]=" & [Forms]![S_1]![txID] & " ;"

日付をFormatで書式設定しているのは、OSの日付書式設定によってはうまく行かない場合があるのでその対策です。

テキストボックスが未入力だったりした場合のエラー対策は別途必要になります。

ＳＫ様　実行はDoCmd.RunSQL strSQL1　でしてます。明らかに間違ってのカンマを削除したら更新できました（初歩的なミスでした。）　すみませんでした。あと　ＳＱＬ部の[Forms]![S_1]![c1～C4]を変数にした場合はどう記述するといいでしょうか？　これが上手く出来なくて

対象組織の在籍期間Noごとに、役職・職種を持っているのですが、
単純に最新の役職を持つクエリ、最新の職種を持つクエリを作成
（それぞれのクエリで、在籍期間Noでグループ化して、役職・職種を先頭（最後）で抽出）
したものを、こちらのクエリから参照する方法もありますね（全くスマートではないですが）。

SELECT t.氏名, t.組織名, Min(t.発令日) AS 開始日, Max(t.終了日) AS 終了日
FROM アクションテーブル As t
GROUP BY t.氏名, t.組織名, t.在籍期間No
ORDER BY t.氏名, t.在籍期間No;

その SQL 文をどのオブジェクトのどのメソッドによって実行しようとしているのか。

以上の点についてもお答え下さい。

"[F4]=[Forms]![S_1]![c4], " & _

明らかに誤っているのは「最後の代入式の直後に ","（カンマ） が挿入されている」という点ですが、もし DoCmd オブジェクトの RunSQL メソッドではなく DAO.Database オブジェクト（または ADODB.Connection オブジェクト）の Execute メソッドによって SQL を実行されようとしているのであれば、それとはまた別の理由によって実行に失敗するでしょう。

ＳＫ様　Ｔ_１のF1は数値型、F2は通貨型、F3は日付型、F4はテキスト型です。
Ｔ_１テーブルの上記フィールドを非連結フォームに入力値に更新したいのです。Ｔ_１の入力済み値をＳ_１フォームから変更したいのです。　説明不足でした。

• テーブル[T_1]のそれぞれのフィールドのデータ型は何なのか。

• その SQL 文をどのオブジェクトのどのメソッドによって実行しようとしているのか。

とりあえず以上の 2 点について明記されることをお奨めします。

明細IDはオートナンバーです。

T発注Subの件数は3万件程なのですが、たまたま明細ID値を確認すると100万を超えてました。ﾃｰﾌﾞﾙの明細IDを細かく見ると2万超えた辺りから急に100万台になってました（途中が大きく抜けてます）。

T発注Mainのﾚｺｰﾄﾞを削除することはありますが（Subも連鎖削除される）、そんなに沢山した記憶はありません。

• [T発注Sub]に100万件以上ものレコードが格納された事実はない。

• 仮に100万件以上ものレコードが格納されていたとしても、およそ98万件ものレコードがまとめて削除された事実はない。

以上の 2 点を前提とした場合は、原因は「オートナンバー型のフィールドのシード値を変更させ得る操作」が行われたことぐらいしかないと思います。

例えば、[T発注Sub]の[明細ID]のシード値が20000である状態で、次のような追加クエリを実行して新規レコードを1件追加したとします。

INSERT INTO [T発注Sub] ([明細ID])
VALUES (1000000);

その後、[T発注Sub]をデータシートビューで開いて新規レコードを挿入した場合、そのレコードの[明細ID]の値は（ 20000 でも 20001 でも 20002 でもなく） 1000001 となり、それ以降に挿入されるレコードについても 1 ずつインクリメントされていきます。
DAO や ADO を介して新規レコードの挿入が実行された場合も同様です。

とりあえず、上記のように「[T発注Sub]の新規レコードの[明細ID]に代入される値を明示的に指定している」追加クエリ、マクロ、プロシージャが存在しないかどうかをご確認されることをお奨めします。

もしそのような操作が実行された形跡も可能性もないのであれば、他の原因を疑われた方がよいでしょう。

Hatenaさま
ありがとうございます。
>アクションテーブルに失効日のフィールドを追加しておいて、SetRenban関数のループ内で発行日の前日を前レコードの失効>日に代入するという処理を追加すればどうでしょう。

movePreviousとかで一旦戻って、Editすることができるんですね。
正直知りませんでした。今回、こちらのほうほうで完璧にできました。

今回、組織コードというおおくくりの組織の配下に、部門、部署というレベルがありまして、
表示する際は、氏名と開始日をもとに、所属歴を参照しては以下組織を持ってきていたのですが、
同じ在籍期間Noでの期間が長いと組織名称が変わっていたりして、終了時点の組織名称を参照する
方法に苦心していましたが、グループ内採番の日付Max値のデータを保持するクエリを作成して、
これを参照することで、Mayuさまのロジックを使わせていただき、なんとかこちらも対応できました。

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

Mayuさま
どうもありがとうございます。
SetSequenceNumber関数、これは凄いものですね！
今後、使えるようにしておきたいです。

私がやったものとは格段に改善しました。

回答有難うございます。

なるほど、大変勉強になり大変助かりました。
有難うございました。

ちょっと試してみたけどDSum関数でも数値と認識してくれないんですねぇ
無理やり数値と認識させればいいだけなので「+0」してやればよさそうでした

DLookUp(（略）)+0

これで

• レコードが無いときはNull+0でNullのまま
• 数値と認識してくれる
• クエリのフィールドプロパティで書式→通貨を設定してあげれば反映される

となりました

回答有難うございます。
Dlookupで引いてくる”金額”はテーブル上、”通貨”の設定なのですが、クエリに表示される数字は”通貨”の表示ではなくなり、Ccur関数で”￥”表示にしている次第です。

コレを踏まえて、対策があれば、ご教示いただけますと幸いです。

通貨表示をしようと思った

のなら、書式設定で表示を変えればいいと思いますが、Ccur関数を使う必要はありますか？

失礼しました。
カンマの位置等を変えた結果、無事空白が返されました↓
DLookUp("OP料金料金2","ALL OP料金","OP料金OPCD='" & [OP料金OPCD] & "'And OP料金Age='" & [OP料金Age] & "'")

コレを踏まえて、Ccur関数で、通貨表示をしようと思ったのです(↓の式)が、空白部分で、”#エラー”表示が出てしまい、
空白の場合、空白で返す式をご教示いただけますと幸いです。

Ccur(DLookUp("OP料金料金2","ALL OP料金","OP料金OPCD='" & [OP料金OPCD] & "'And OP料金Age='" & [OP料金Age] & "'"))

今回、ご相談させていただきたいのは、開始日は在籍期間NoのMinで良いのですが、
終了日が、理論上は[在籍期間No] + 1のMinの前日となると思っていますが、
私ができる初歩的なレベルですと、もうひとつクエリを作って、両者を結合させるとかになります。

アクションテーブルに失効日のフィールドを追加しておいて、SetRenban関数のループ内で発行日の前日を前レコードの失効日に代入するという処理を追加すればどうでしょう。

Public Sub SetRenban()
    Dim rs As DAO.Recordset
    Dim c As Long, d As Long
    Dim pre氏名, pre組織名
    Dim strSQL As String
 
    strSQL = "SELECT 在籍期間No, 終了日, 発令日, 氏名, 組織名 FROM アクションテーブル " & _
             "ORDER BY 氏名, 発令日;"
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbFailOnError)
 
    Do Until rs.EOF
        If pre氏名 = rs!氏名 Then
            If pre組織名 <> rs!組織名 Then
                c = c + 1
                pre組織名 = rs!組織名
            End If
            d = Format(DateAdd("d", -1, Format(rs!発令日, "0000/00/00")), "yyyymmdd")
            rs.MovePrevious
            rs.Edit
            rs!失効日 = d
            rs.Update
            rs.MoveNext
        Else
            c = 1
            pre氏名 = rs!氏名
            pre組織名 = rs!組織名
        End If
 
        rs.Edit
        rs(0) = c
        rs.Update
        rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
    MsgBox "完了"
End Sub

SELECT t.氏名, t.組織名, Min(t.発令日) AS 開始日, Max(t.終了日) AS 終了日
FROM アクションテーブル As t
GROUP BY t.氏名, t.組織名, t.在籍期間No
ORDER BY t.氏名, t.在籍期間No;

(続き)

■DML

SELECT x.氏名
     , x.組織名
     , Min( x.発令日 ) As 発令日
     , Max( Nz( DateAdd( 'd', -1, y.発令日 ), #12/31/2099# ) ) As 失効日
FROM アクションテーブル x
LEFT JOIN  アクションテーブル y
       ON x.氏名 = y.氏名
      AND x.GSeq日付 = y.GSeq日付 - 1
GROUP BY x.氏名
       , x.組織名
       , x.GSeq日付 - x.GSeq組織
ORDER BY x.氏名
       , Min( x.発令日 )
;

　
■結果

サンプルを載せておきます

最初に SetSequenceNumber関数 をデータベースにインポートして
利用可能な状態にしておきましょう

グループ毎連番を自動入力する関数

クエリで連番を表示する場合、DCount関数やサブクエリを利用する方法はあちこちで紹介されています。 しかし、この方法は自分より前のレコード件数をカウントするというロジックなのでレコード件数が多くなると幾何級数的に重くなります。また、グループ毎に連番を振るという仕様になると、条件式も複雑になってきます。 クエリは使わずにテーブルに連番フィールドを持たせて、そこに VBA で連番を書きこむようにすると高速...

fc2

　
■DDL

CREATE TABLE アクションテーブル (
      氏名     VARCHAR(50) NOT NULL
    , 発令日   DATETIME    NOT NULL
    , 組織名   VARCHAR(30) NOT NULL
    , GSeq日付 INT
    , GSeq組織 INT
    , CONSTRAINT pk_action PRIMARY KEY ( 氏名, 発令日 )
);

　
■データ例 (連番付与前)

　
■グループ連番の付与

Sub set_number()
    Call SetSequenceNumber("GSeq日付", "アクションテーブル", "氏名", "発令日")
    Call SetSequenceNumber("GSeq組織", "アクションテーブル", "氏名,組織名", "発令日")
End Sub

　
■データ例 (連番付与後)

新しい質問を立てるのはどうでしょう？
主キーと関連するテーブルを過不足なく提示したり、スクショを提示したりするのがおすすめです。

[在籍期間No]をひとつずらして結合させるところができずにいます。

こちらのスレッドでアドレスいただいた者です。

ベースになるデータの仕様が少し変わりまして、開始日・終了日を持たず、
発令日（=開始日）のみしか持たないデータソースを使うこととなりました。

SetRenban関数にて在籍期間Noをふり、開始日および終了日はSQLで在籍期間NoのMin、Max
にて計算するというものを実装しています。

今回、ご相談させていただきたいのは、開始日は在籍期間NoのMinで良いのですが、
終了日が、理論上は[在籍期間No] + 1のMinの前日となると思っていますが、
私ができる初歩的なレベルですと、もうひとつクエリを作って、両者を結合させるとかになります。

このあたりの実装でご相談させてください。

sk様
詳しい説明ありがとうございました。
条件付き書式でできました。

作成→その他フォーム→データシートで、フォームを作成しました。

Me.[日時].ForeColor = vbRed

フォームのデータシートビューにおいては、そのフォームの DatasheetForeColor プロパティの設定が優先され、テキストボックスの ForeColor プロパティの設定は事実上無効となります。

データシートフォームにおいて、それぞれのレコードごとにテキストボックスの前景色を切り替えるようにしたければ、条件付き書式を用いる方法しかありません。

条件付き書式も反映しません。

Month(Me.[日時]) = Month(Date)

Month([日時])=Month(Date())

なるほど！
考え方・式の書き方ともに納得です。
ありがとうございました！

前月の末日は「当月１日の１日前」です。つまり、「今日」から「今日の日」を引くと前月の末日になります。
例）2024/06/13 - 13 =>2024/05/31
日付を特定の書式で表示したい場合はformat関数を使います

=Format(Date()-Day(Date()),"yyyy\年m") & "月末 時点での預り在庫数"

ありがとうございます！

分割フォームとサブフォームがこんがらがっていたようです…
私が求めていたのは分割フォームでした！失礼致しました。
以前はうまくできなかったのですが、
Microsoft「既存のフォームを分割フォームにする」
をみて解決しました。

また、教えていただいたので、過去の掲示板を検索し、
複数条件での抽出について、目を通しました。
VBAを使うものが多く見えたため、今の環境や自分では
導入が難しそうで、私なりに小さくまとめた結果として
トグルボタンにて進めたいと思います。
トグルで検索できない分は、各自、クエリで抽出条件を
組み合わせて対応してもらうつもりです。

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

問題は出来る限り小さくまとめましょう

トグルボタンではなくプルダウンで、
TRUE/FALSEを複数選別できると良かったのですが、
実はそういうアクセスを以前使用しておりました。
コードを見るとVBAが長々と組まれており

実装が不明ですが、それほど難しいようには思いません。フォームの内容を元に複数条件で抽出を行うのはこの掲示板でも頻度の高い質問ですし

表の下部のフォームですが、長すぎる住所が
入り切らない時など閲覧者が自由に変えられるように
したい

とりあえず、分割フォームを試してみては？
データシートビュー部分であればサイズの変更ができます（行単位、列単位）

基本的に、フォームのデザインを動的に変更するような仕様はとても難しいです。レコードごとのデザインは共通になるので、「住所の長いレコードだけ高さを広げたい」という要望であれば、それは無理です。
また、実際に何かを導入しようとするのであれば、かなり具体的にフォームの作りを説明できる程度の知識は必要です

参考：フォームを2分割して境界線をドラッグしてリサイズ（hatena chipsさん）

返信に気付くの遅くなり申し訳ありません。
>>この部分を実際どのように実現しているかの説明が必要かと。
すみません、よくわからず使っていて、
過去のバックアップがあったので比較してみていたら、コンボボックスの更新後処理のところが間違っていたようで、
バックアップのものと同じように書き換えて解決しました。

お騒がせ致しましたm(__)m

hatenaさん　ありがとうございます。
コード記述したら思い通りの結果になりました。Left関数とInStr関数の組合せはすごいアイデアです。
WHERE条件もこうすればいいのですね。
大変お世話になりました。

下記のSQLで。

UPDATE [T発注Sub] INNER JOIN [Q一括変更用ﾁｪｯｸON分]
       ON [T発注Sub].[発注明細ID] = [Q一括変更用ﾁｪｯｸON分].[発注明細ID]
SET [T発注Sub].[備考・MEMO] = Left([T発注Sub].[備考・MEMO],InStr([T発注Sub].[備考・MEMO],'*')-1)
WHERE [T発注Sub].[備考・MEMO] Like '*[*]*';

注意点
SQLのLike演算子は、全角の＊と半角の*は区別しない
*はワイルドカードの意味になるので、文字としての*と認識させるには[*]というようにカッコで囲む

もし、全角の＊のみ対象としたいのなら、WHERE句を下記のように修正

WHERE InStr(1,[テーブル1].[aaa],'＊',0) > 0;

詳細は下記を参照してください。

【Access】クエリで大文字/小文字、全角/半角、ひらがな/カタカナを区別して文字列比較したい | hatena chips

hatenaさん　ありがとうございます。
入力したと思ってたのですが抜けてました。例：”あいうえお＊さしすせそ”　の＊(記号)以降の文字列を取り除きたい⇒”あいうえお”だけ残したい。不十分ですみませんでした。

[備考・MEMO]ﾌｨｰﾙﾄﾞに例えば　"あいうえおさしすせそ"　と入力されており、を含むそれ以降の文字列を削除する更新SQLを実行可能でしょうか？

「を含むそれ以降の文字列」の「を」の前には何が入りますか。(入力ミス？)
仕様が不明確です。
コーディングするには仕様を明確に言語化する必要があります。

（"あいうえお"だけ残す）

この意味が、
[備考・MEMO]が"あいうえお"で始まっている場合、"あいうえお"を残してそれ以降を削除する(言い換えるなら"あいうえお"にする)
ということなら、SQLは下記になります。

UPDATE [T発注Sub] INNER JOIN [Q一括変更用ﾁｪｯｸON分]
       ON [T発注Sub].[発注明細ID] = [Q一括変更用ﾁｪｯｸON分].[発注明細ID]
SET [T発注Sub].[備考・MEMO] = 'あいうえお'
WHERE [T発注Sub].[備考・MEMO] Like 'あいうえお*';

hatenaさん　ありがとうございます。
”田中”を変数のtyokusouに変更するだけで出来ました。
ややこしく考えすぎでした。とにかく変数の前に”で区切り ＆　変数　＆　で繋いでその後に”で記述始めたらいいのですね。
初歩的な事に丁寧な解説頂き、すみませんでした。
お世話になりました。

前回の回答と、リンク先を熟読して、一度自分でチャレンジしてみてください。
一度理解できれば、簡単なことだと思います。

どうしてもできないようでしたら、その旨返信してください。

ヒント

"SET [T発注Sub].[備考・MEMO] = [T発注Sub].[備考・MEMO] & '★田中さんに直送';"

'田中 の部分が変数なので、文字列をその部分だけ分割して&で結合する↓

"SET [T発注Sub].[備考・MEMO] = [T発注Sub].[備考・MEMO] & '★" & "田中" & "さんに直送';"

'"田中"の部分を変数にする

リンク先でも言ってますが、SQLとVBAは別物ですので、SQLの部分とVBAコードの部分を意識するようにしましょう。

文字列結合はSQLでもVBAでも &
引用符は SQLでは ' または " どちらでも可、VBAでは " のみ

hatenaさん　ありがとうございます。
コード修正したら出来ました。あと変数の前に★等の記号入れたいのですがどう記述したらいいでしょうか？
= [T発注Sub].[備考・MEMO] & ' " & tyokusou & " さんへ直送';"　の　変数tyokusouの前にです。
"　★田中さんに直送　”としたいのです。頭がこんがらがってしまって・・・すみません。

SQL内の文字列リテラルは引用符("または')で囲む必要があります。
つまり、下記のようなSQLになります。

UPDATE [T発注Sub] INNER JOIN [Q一括変更用ﾁｪｯｸON分] 
         ON [T発注Sub].[発注明細ID] = [Q一括変更用ﾁｪｯｸON分].[発注明細ID] 
       SET [T発注Sub].[備考・MEMO] = [T発注Sub].[備考・MEMO] & ' 田中 さんへ直送';

※tyokusou の中身が 田中 の場合

このSQLをVBA内に記述する場合は、VBAとしての文字列リテラルにする必要があるので、引用符(")で囲む必要があります。

Dim sSQL As String
sSQL = "UPDATE [T発注Sub] INNER JOIN [Q一括変更用ﾁｪｯｸON分] " & _
       "ON [T発注Sub].[発注明細ID] = [Q一括変更用ﾁｪｯｸON分].[発注明細ID] " & _
       "SET [T発注Sub].[備考・MEMO] = [T発注Sub].[備考・MEMO] & ' 田中 さんへ直送';"

変数部分は文字列リテラルの外に出す必要がありますので、下記のようになります。

Dim sSQL As String, tyokusou As String
tyokusou = "田中"
sSQL = "UPDATE [T発注Sub] INNER JOIN [Q一括変更用ﾁｪｯｸON分] " & _
       "ON [T発注Sub].[発注明細ID] = [Q一括変更用ﾁｪｯｸON分].[発注明細ID] " & _
       "SET [T発注Sub].[備考・MEMO] = [T発注Sub].[備考・MEMO] & ' " & tyokusou & " さんへ直送';"

下記を一読しておくと理解が深まると思います。

Access上のコード内で引用符(")と単引用符(')の使い分けについて - hatena chips

みなさま丁寧なご解説ありがとうございました。省略してうまくいく時と、そうでない時があり、少し困っていましたが、省略可能でもきちんと表現したいと思います。ありがとうございました。