A message box is required if all tasks completed

[aymanmb]

Thanks for the efforts and time, appreciate it. I will try it

As for the keys, I believe I have a primary key for the orders table (OrderID). for the orderdeatils table, I did not know what to do because I have there the OrderID field linked to the OrderID field of Orders table.

Should I define it also as a primary key in the OrderDetails table? or should I add another field e.g. OrderDetailsID and in this case it will not be linked to any where in my database (and so I am not sure of its value or need).

in the Norhwind example, I saw the OrderID and ProductID (both are available as primary keys in Order and Products tables) are defined as keys but did not undestand how to defibe 2 keys in one table.

appreciate your insight on that (key dfinitions) of my database, thanks

 

[aymanmb]

I tried the code after renamingthe check box. It gave me the error as per attached photos/ Also attached a photo of th code I pasted in the afterupdate event of the check box.

regards

 

[aymanmb]

Sorry I pasted the code in beforeupdate by mistake, it works great, you are a genius. the sad thing is that I do not understand the code yet, I wull try to figure it out.

would appreciate answering me on the Keys issues in the above reply of mine

thanks

 

[boblarson]

Here's an explanation of the code:

    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim intFalse As Integer

declares a recordset variable, a string variable for the SQL statement, and the count of false completed.

    strSQL = "SELECT tbl_OrderDetails.OrderID, tbl_OrderDetails.Completed " & _
            "FROM tbl_OrderDetails " & _
            "WHERE (((tbl_OrderDetails.OrderID)=" & [Forms]![Frm_Orders]![sFrm_OrderDetails].[Form]![OrderID] & "));"

The SQL string is put in (we just needed the OrderID and the Completed fields as we only need to match up the completed with the current order id) and in the "Where" clause, since we are referring to an item on the subform, we need to concatenate in the form item. Also, notice where there are spaces at the end of the lines and then the & _ which extend the lines so that you can make it more readable.

    Set rst = New ADODB.Recordset

You have to use the Set command to initialize the recordset variable

    rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Opens the recordset by using the SQL statement in strSQL and sets the connection to the current database's connection. The adOpenForwardOnly is for speed since we're really only needing to look at the records, not manipulate them. The same goes for adLockReadOnly as we aren't making any changes, just reading records.

    DoCmd.RunCommand acCmdSaveRecord

Even though the AfterUpdate event of the last checkbox has triggered, the form's AfterUpdate event hasn't. So, we need to save the record after clicking the checkbox so that it's true value will show up when we go through the recordset.

    Do Until rst.EOF
        If rst("Completed").Value = 0 Then
            intFalse = intFalse + 1
        End If
        rst.MoveNext
    Loop

Just a quick loop to move through the recordset to see if the "Completed" field has a value (which would be -1 if selected). If it has a value of 0, then it counts as FALSE and so we increment the intFalse variable so we can know later that there are still records that haven't been marked as completed.

    If intFalse = 0 Then
        MsgBox ("all tasks are completed, please enter the EndDate above")
    End If

If the variable's value is zero that means that we didn't run across an uncomplete during our loop through the recordset and that would mean that all are complete.

rst.Close
Set rst = Nothing

When using variables like this you should close the recordset and then set the variable to nothing so that the memory space it takes up on the computer will be cleared and released back to the operating system.

I hope that helps explain things. I'll work on answering your other questions in a bit.

 

[boblarson]

After looking again, I see that you do have a primary key in the Orders table, but not in the OrdersID table.

So it should be added:

 

[aymanmb]

Thank yu for your time and care, a reall master.

as for the keys, I will do a primary key for the OrderDetails table.

I understand that foreign key means that I will link this field e.g. OrderID field of Orderdetails to its field in Orders table.

Annd that on the orderDetails table only one key will appear (visually) which is the primary key but the foreign key will not represented by the key sympol, Am I right.

thanks

 

[boblarson]

Annd that on the orderDetails table only one key will appear (visually) which is the primary key but the foreign key will not represented by the key sympol, Am I right.

You are Correct

 

[aymanmb]

Final request Bob,

I want to modify code above so that if the user uncheck one of the orderdetails records to check if not all records are checked to ask the user to remove the EndDate if it was entered before.

thanks

 

[aymanmb]

Dear Bob,

I managed to do it myself (last request above), I am gratefull to you and happy that I managed to manipulate the code and achieve what I want

thanks again

 

[boblarson]

Glad to hear.