Check Related Records

[JK]

I have a form called DemandLog that is bound using a SQL query to
tblDemandLog. The form has six tabs. The first tab is the main tab where a
Sunday date is entered. Then, I have five tabs, one for each day of the week.
On each day tab there is a sub form.

The user selects a Sunday date on the main tab. Then, when a demand item is
entered into one of the day tabs (sub form [tblDemandLogDetails]) the date is
automatically calculated based on the main tab.

Each table has a Complete check box. The entire week can be checked as
complete and each item in each day can be checked complete.

I want to warn the user and cancel the operation if the user checks the main
form complete check box when there is an item in any one of the five sub
forms (all bound to the same table) that is incomplete.

I'm trying to use the before delete code from one of the examples I found
online. I posted the code below; I'm just not sure if this will work or how
to make it work. Any help would be appreciated.

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset
Dim varRelate As Variant

' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
' Open the test query
Set qd = db.QueryDefs("qryCheckRelateWeek")
' Set the company parameter
qd!WeekNo = Me.ID
' Open a recordset on the related rows
Set rst = qd.OpenRecordset()
' If we got rows, then can't delete
If Not rst.EOF Then
varRelate = Null
' Loop to build the informative error message
rst.MoveFirst
Do Until rst.EOF
' Grab all the table names
varRelate = (varRelate + ", ") & rst!TableName
rst.MoveNext
Loop
MsgBox "You cannot delete this Week because you" & Chr(13) & Chr(10)
& "have related rows in " & _
varRelate & "." & Chr(13) & Chr(10) & "Delete these records
first, and then delete the Week.", _
vbOKOnly + vbCritical, gstrAppTitle
' close all objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' Cancel the delete
Cancel = True
Exit Sub
End If
' No related rows - clean up objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' No related rows, so OK to ask if they want to delete!
If vbNo = MsgBox("Are you sure you want to delete Week " & Me.WeekDate &
"?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
Cancel = True
End If

 

[NetworkTrade]

in the BeforeUpdate event of the checkbox; you can write If/then statements
for the other controls status, trigger a MsgBox, etc.

 

[JK]

I'm not a programmer; I'm able to get by with help - but that's it. I don't
suppose you'd be willing to help me write the statement? If not, I completely
understand.

If so,

Main Table: tblDemandLog
2nd Table: tblDemandLogDetails

Main Table Check Box: "Complete"
2nd Table Check Box: "Complete"


Identical value found in both tables is called "ID." It's an auto-number in
the Main Table and carried over into the 2nd table when a new record is
created.

So, if the ID value is the same, and the 2nd table has any records where the
complete check box is equal to false, then display a message and cancel the
event.

If forms!DemandLog.ID is equal to (or can be found in)
tblDemandLogDetails.ID and
tblDemandLogDetails.Complete is equal to false then
Display MsgBox “You have incomplete demand items on [WeekDay].â€
Cancel = True

WeekDay is a date field found in the tblDemandLogDetails table.

Thx.

in the BeforeUpdate event of the checkbox; you can write If/then statements
for the other controls status, trigger a MsgBox, etc.


--
NTC

I have a form called DemandLog that is bound using a SQL query to
tblDemandLog. The form has six tabs. The first tab is the main tab where a
Sunday date is entered. Then, I have five tabs, one for each day of the week.
On each day tab there is a sub form.

The user selects a Sunday date on the main tab. Then, when a demand item is
entered into one of the day tabs (sub form [tblDemandLogDetails]) the date is
automatically calculated based on the main tab.

Each table has a Complete check box. The entire week can be checked as
complete and each item in each day can be checked complete.

I want to warn the user and cancel the operation if the user checks the main
form complete check box when there is an item in any one of the five sub
forms (all bound to the same table) that is incomplete.

I'm trying to use the before delete code from one of the examples I found
online. I posted the code below; I'm just not sure if this will work or how
to make it work. Any help would be appreciated.

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset
Dim varRelate As Variant

' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
' Open the test query
Set qd = db.QueryDefs("qryCheckRelateWeek")
' Set the company parameter
qd!WeekNo = Me.ID
' Open a recordset on the related rows
Set rst = qd.OpenRecordset()
' If we got rows, then can't delete
If Not rst.EOF Then
varRelate = Null
' Loop to build the informative error message
rst.MoveFirst
Do Until rst.EOF
' Grab all the table names
varRelate = (varRelate + ", ") & rst!TableName
rst.MoveNext
Loop
MsgBox "You cannot delete this Week because you" & Chr(13) & Chr(10)
& "have related rows in " & _
varRelate & "." & Chr(13) & Chr(10) & "Delete these records
first, and then delete the Week.", _
vbOKOnly + vbCritical, gstrAppTitle
' close all objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' Cancel the delete
Cancel = True
Exit Sub
End If
' No related rows - clean up objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' No related rows, so OK to ask if they want to delete!
If vbNo = MsgBox("Are you sure you want to delete Week " & Me.WeekDate &
"?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
Cancel = True
End If