'各コントロールの入力状態をチェックする関数。
'全てのコントロールの値が適切であれば True、そうでなければ False を返す
Private Function InputCheck() As Boolean
On Error GoTo Err_InputCheck
InputCheck = False
'[txtID]のチェック（オートナンバー型の主キー[ID]と照応）
With Me![txtID]
If IsNull(.Value) = True Then
MsgBox "[ID]が入力されていません。", _
vbExclamation, _
"入力エラー"
.SetFocus
Exit Function
End If
If IsNumeric(.Value) = False Then
MsgBox "数値データに変換できない文字列が[ID]に入力されています。", _
vbExclamation, _
"入力エラー"
.SetFocus
Exit Function
End If
If DCount("*", SourceTableName, "[ID]=" & CLng(.Value)) = 0 Then
MsgBox "テーブル[" & SourceTableName & "]上に、[ID]の値が" & CLng(.Value) & "であるレコードは存在しません。", _
vbExclamation, _
"入力エラー"
.SetFocus
Exit Function
End If
End With
'[c1]のチェック（数値型フィールド[F1]と照応）
With Me![c1]
If Nz(.Value, "") <> "" Then
If IsNumeric(.Value) = False Then
MsgBox "数値データに変換できない文字列が[F1]に入力されています。", _
vbExclamation, _
"入力エラー"
.SetFocus
Exit Function
End If
End If
End With
'[c2]のチェック（通貨型フィールド[F2]と照応）
With Me![c2]
If Nz(.Value, "") <> "" Then
If IsNumeric(.Value) = False Then
MsgBox "数値データに変換できない文字列が[F2]に入力されています。", _
vbExclamation, _
"入力エラー"
.SetFocus
Exit Function
End If
End If
End With
'[c3]のチェック（日付/時刻型フィールド[F3]と照応）
With Me![c3]
If Nz(.Value, "") <> "" Then
If IsDate(.Value) = False Then
MsgBox "日時データに変換できない値が[F3]に入力されています。", _
vbExclamation, _
"入力エラー"
.SetFocus
Exit Function
End If
End If
End With
InputCheck = True
'終了処理
Exit_InputCheck:
Exit Function
'エラー時処理
Err_InputCheck:
InputCheck = False
Dim strErrTitle As String
Dim strErrMsg As String
strErrTitle = "実行時エラー (" & Me.Name & ".InputCheck)"
strErrMsg = Err.Number & ": " & Err.Description
Debug.Print strErrTitle
Debug.Print strErrMsg
MsgBox strErrMsg, vbCritical, strErrTitle
End Function
'非連結フォーム[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
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;
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.発令日 )
;
ｓｋ様 回答の表示はそういう事だったのですね。
変更用フォーム[S_1]の非連結コントロールは編集可能なのでユーザーでNullにされる可能性はありますので、変更実行ボタン（それに更新ＳＱＬコードを記載）に実行前にＮｕｌｌ値には別のコントロール値（元から代入させていた）を代入する方法をとってます。
それぞれの投稿の投稿者アイコンの下にある番号が、このスレッドにおける投稿順を示しています。
私が回答したのは hatena さん（ 5 番）よりも後です。
ビギナーさんの投稿（ 4 番）に返信する形を取ったため、その階層ツリーのノードとして表示されているだけです。
[フォームＡ]がテーブル[T_1]をレコードソースとする帳票フォームであるとして、
フォーム[S_1]の各非連結コントロールへの値の代入に際し、[フォームA]のカレントレコードから参照されたいずれかのフィールドの値が元から Null であるケースはないのでしょうか。
少なくとも、フォーム[S_1]のいずれかの非連結コントロールの値がユーザーの操作によって Null にされる可能性はゼロではないはずです。
ＳＫ様 失礼しました。hatena様の分の前に詳しい回答を沢山頂いていたのを見落としてました。申し分けありません。
Null値の事は気になってました。説明不足でしたがフォームＡのレコードをダブルクリックで変更用のフォームＳ１が開き、Ｓ１で変更しＳＱＬで更新の仕組みにしてます。Ｓ１のＮＵＬＬ対策として元々のフォームＡ値をＳ１の別のテキストボックスに代入してＮＵＬＬのテキストボックスにはそれを代入してから更新としてます。
もう1箇所。
1箇所修正。
各コントロールの入力チェック、コントロールの値に応じた条件分岐、
文字列に含まれる引用符のエスケープ処理などを考慮するのであれば、
以上のサンプルのような形となるでしょう。
ただ私個人の見解としては、こういうまどろっこしい文字列連結を行うコードを
記述するより、パラメータクエリを実行するようにした方が良いと思います。
各非連結コントロールの値を「 SQL 文におけるリテラル」として組み込みたい、
という意味でおっしゃっているのであれば、その SQL の実行時において、
いずれかの非連結コントロールの値が Null であることを許容するか否か次第です。
単純に Null 値をそのまま文字列連結させてしまうと、実行時に構文エラーが
発生する可能性が極めて高いでしょう。
hatena様 ありがとうございます。そのコードで問題なく更新できました。ＶＢＡ内での改行が絡み頭が混乱してました。
一つ教えて頂きたいのですが、ＳＱＬ内でフォーム値を使う場合も変数と同じ様に＆でつなぎ外に出さないといけないのですよね？ 質問時の "SET [F1]=[Forms]![S_1]![c1], " & _ は本来間違っているのですね？（ "SET [F1]=" & [Forms]![S_1]![c1] & ", " & _ が正しい） たまたま動作していたのですね？
skさんありがとうございます。これに関わる追加処理は現在ありませんので、以前色々処理させた時に間違った事があったと思います。様子みてみます。お世話になりました。
変数は文字列の外(""の外)に出して&で結合すればいいでしょう。
あと、SQL内では日付時刻型は#で囲む、文字列は'で囲みます。
日付を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;
以上の点についてもお答え下さい。
明らかに誤っているのは「最後の代入式の直後に ","（カンマ） が挿入されている」という点ですが、もし DoCmd オブジェクトの RunSQL メソッドではなく DAO.Database オブジェクト（または ADODB.Connection オブジェクト）の Execute メソッドによって SQL を実行されようとしているのであれば、それとはまた別の理由によって実行に失敗するでしょう。
ＳＫ様 Ｔ_１のF1は数値型、F2は通貨型、F3は日付型、F4はテキスト型です。
Ｔ_１テーブルの上記フィールドを非連結フォームに入力値に更新したいのです。Ｔ_１の入力済み値をＳ_１フォームから変更したいのです。 説明不足でした。
テーブル[T_1]のそれぞれのフィールドのデータ型は何なのか。
その SQL 文をどのオブジェクトのどのメソッドによって実行しようとしているのか。
とりあえず以上の 2 点について明記されることをお奨めします。
[T発注Sub]に100万件以上ものレコードが格納された事実はない。
仮に100万件以上ものレコードが格納されていたとしても、およそ98万件ものレコードがまとめて削除された事実はない。
以上の 2 点を前提とした場合は、原因は「オートナンバー型のフィールドのシード値を変更させ得る操作」が行われたことぐらいしかないと思います。
例えば、[T発注Sub]の[明細ID]のシード値が20000である状態で、次のような追加クエリを実行して新規レコードを1件追加したとします。
その後、[T発注Sub]をデータシートビューで開いて新規レコードを挿入した場合、そのレコードの[明細ID]の値は（ 20000 でも 20001 でも 20002 でもなく） 1000001 となり、それ以降に挿入されるレコードについても 1 ずつインクリメントされていきます。
DAO や ADO を介して新規レコードの挿入が実行された場合も同様です。
とりあえず、上記のように「[T発注Sub]の新規レコードの[明細ID]に代入される値を明示的に指定している」追加クエリ、マクロ、プロシージャが存在しないかどうかをご確認されることをお奨めします。
もしそのような操作が実行された形跡も可能性もないのであれば、他の原因を疑われた方がよいでしょう。
Hatenaさま
ありがとうございます。
>アクションテーブルに失効日のフィールドを追加しておいて、SetRenban関数のループ内で発行日の前日を前レコードの失効>日に代入するという処理を追加すればどうでしょう。
movePreviousとかで一旦戻って、Editすることができるんですね。
正直知りませんでした。今回、こちらのほうほうで完璧にできました。
今回、組織コードというおおくくりの組織の配下に、部門、部署というレベルがありまして、
表示する際は、氏名と開始日をもとに、所属歴を参照しては以下組織を持ってきていたのですが、
同じ在籍期間Noでの期間が長いと組織名称が変わっていたりして、終了時点の組織名称を参照する
方法に苦心していましたが、グループ内採番の日付Max値のデータを保持するクエリを作成して、
これを参照することで、Mayuさまのロジックを使わせていただき、なんとかこちらも対応できました。
どうもありがとうございました。
Mayuさま
どうもありがとうございます。
SetSequenceNumber関数、これは凄いものですね！
今後、使えるようにしておきたいです。
私がやったものとは格段に改善しました。
回答有難うございます。
なるほど、大変勉強になり大変助かりました。
有難うございました。
ちょっと試してみたけどDSum関数でも数値と認識してくれないんですねぇ
無理やり数値と認識させればいいだけなので「
+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] & "'"))
アクションテーブルに失効日のフィールドを追加しておいて、SetRenban関数のループ内で発行日の前日を前レコードの失効日に代入するという処理を追加すればどうでしょう。
(続き)
■DML
■結果
サンプルを載せておきます
最初に SetSequenceNumber関数 をデータベースにインポートして
利用可能な状態にしておきましょう
■DDL
■データ例 (連番付与前)
■グループ連番の付与
■データ例 (連番付与後)
新しい質問を立てるのはどうでしょう？
主キーと関連するテーブルを過不足なく提示したり、スクショを提示したりするのがおすすめです。
[在籍期間No]をひとつずらして結合させるところができずにいます。
こちらのスレッドでアドレスいただいた者です。
ベースになるデータの仕様が少し変わりまして、開始日・終了日を持たず、
発令日（=開始日）のみしか持たないデータソースを使うこととなりました。
SetRenban関数にて在籍期間Noをふり、開始日および終了日はSQLで在籍期間NoのMin、Max
にて計算するというものを実装しています。
今回、ご相談させていただきたいのは、開始日は在籍期間NoのMinで良いのですが、
終了日が、理論上は[在籍期間No] + 1のMinの前日となると思っていますが、
私ができる初歩的なレベルですと、もうひとつクエリを作って、両者を結合させるとかになります。
このあたりの実装でご相談させてください。
sk様
詳しい説明ありがとうございました。
条件付き書式でできました。
フォームのデータシートビューにおいては、そのフォームの DatasheetForeColor プロパティの設定が優先され、テキストボックスの ForeColor プロパティの設定は事実上無効となります。
データシートフォームにおいて、それぞれのレコードごとにテキストボックスの前景色を切り替えるようにしたければ、条件付き書式を用いる方法しかありません。
なるほど！
考え方・式の書き方ともに納得です。
ありがとうございました！
前月の末日は「当月１日の１日前」です。つまり、「今日」から「今日の日」を引くと前月の末日になります。
例）
2024/06/13 - 13 =>2024/05/31日付を特定の書式で表示したい場合はformat関数を使います
ありがとうございます！
分割フォームとサブフォームがこんがらがっていたようです…
私が求めていたのは分割フォームでした！失礼致しました。
以前はうまくできなかったのですが、
Microsoft「既存のフォームを分割フォームにする」
をみて解決しました。
また、教えていただいたので、過去の掲示板を検索し、
複数条件での抽出について、目を通しました。
VBAを使うものが多く見えたため、今の環境や自分では
導入が難しそうで、私なりに小さくまとめた結果として
トグルボタンにて進めたいと思います。
トグルで検索できない分は、各自、クエリで抽出条件を
組み合わせて対応してもらうつもりです。
ありがとうございました。
問題は出来る限り小さくまとめましょう
実装が不明ですが、それほど難しいようには思いません。フォームの内容を元に複数条件で抽出を行うのはこの掲示板でも頻度の高い質問ですし
とりあえず、分割フォームを試してみては？
データシートビュー部分であればサイズの変更ができます（行単位、列単位）
基本的に、フォームのデザインを動的に変更するような仕様はとても難しいです。レコードごとのデザインは共通になるので、「住所の長いレコードだけ高さを広げたい」という要望であれば、それは無理です。
また、実際に何かを導入しようとするのであれば、かなり具体的にフォームの作りを説明できる程度の知識は必要です
参考：フォームを2分割して境界線をドラッグしてリサイズ（hatena chipsさん）
返信に気付くの遅くなり申し訳ありません。
>>この部分を実際どのように実現しているかの説明が必要かと。
すみません、よくわからず使っていて、
過去のバックアップがあったので比較してみていたら、コンボボックスの更新後処理のところが間違っていたようで、
バックアップのものと同じように書き換えて解決しました。
お騒がせ致しましたm(__)m
hatenaさん ありがとうございます。
コード記述したら思い通りの結果になりました。Left関数とInStr関数の組合せはすごいアイデアです。
WHERE条件もこうすればいいのですね。
大変お世話になりました。