After update for check box not working (1 Viewer)

[slharman1]

Yes, look tidy. Google Smart Indenter, to tidy up existing code. Plus you *could* type it in any old way and use it each time. You can indent the whole module.
Best to get into the habit though.

Wow! Thanks Gasman! This makes all the old code I have written (old meaning before yesterday ) so much easier to read.
Now why is my second recordset empty? RS2 in my code in #11 When I run a query built exactly like it I get my records, but here nothing.
Thanks

 

[slharman1]

Does that mean you get to the code block you thought wasn't working, but it doesn't do anything?
If so, the problem is a different problem.

Using breakpoints, and adding msgbox's or debug statements to trace the flow of a programme are things we have to use to track down the logic errors.

You could even open a query temporarily to see the expected results.

docmd.openquery "qryname"

I have done that by creating a query exactly the same as my code and I get my records.
It is rs2 in my code that is rs.count 0
Not sure how to open the rs2 in a query but hovering over it when string break point shows it to be empty.
the select statement for rs is pretty much the same except for field names and it returns the expected records, but rs2 returns empty.

 

[slharman1]

I would make sure the code is actually firing.
Put a break point in, and step through the code to see what if anything is going wrong.

no errors, i am just not getting my records.

 

[gemma-the-husky]

Yes, but I mean that's what's going wrong. It's not the after update event. It's something else.

You have commented out the line that sets orderdetid,
Is that why you get no records?

 

[slharman1]

Ok, I've cleaned up the code so it is easier for you guys to read. I have also moved the second loop from outside of the If Fasle Then , rs2 still empty. When stepping through the code orddetid returns the correct value using debug.print orddetd , and the recordset should contain records but it is empty. What the heck am I doing wrong?

Private Sub IsComplete_AfterUpdate()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim strItemComp As String
    Dim strAccComp As String
    Dim ordid As Long
    Dim orddetid As Long

    ordid = Me.txtOrdID
    strItemComp = "SELECT OrderDetailID, IsComplete, CompDate FROM tblOrderDetails WHERE OrderID = " & ordid
    strAccComp = "SELECT OrderAccID, IsComplete, CompDate FROM tblOrderAcc WHERE OrderDetailID = " & orddetid
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strItemComp)

    If Me.IsComplete = True Then
        If MsgBox("Marking main order complete will mark ALL items and accessories for this Order as complete!", vbYesNo, "Are you sure?") = vbYes Then
            Me!txtCompletionDate = Date
            rs.MoveFirst
            Do Until rs.EOF
                If rs!IsComplete = False Then
                    rs.Edit
                    rs!IsComplete = True
                    rs!CompDate = Date
                    rs.Update
                End If

                orddetid = rs.Fields("OrderDetailID")
                Debug.Print orddetid
                Set rs2 = db.OpenRecordset(strAccComp)
                If rs2.RecordCount > 0 Then
                    rs2.MoveFirst
                    Do Until rs2.EOF
                        If rs2!IsComplete = False Then
                            rs2.Edit
                            rs2!IsComplete = True
                            rs2!CompDate = Date
                            rs2.Update
                        End If
                        rs2.MoveNext
                    Loop
                End If

                rs.MoveNext
            Loop
            Me.Dirty = False
            Exit Sub
        Else
            Me.Undo
        End If
    Else
        Me.txtCompletionDate = Null
        Exit Sub
    End If
    Me.Dirty = False
End Sub

 

[gemma-the-husky]

your third line of code

strAccComp = "SELECT OrderAccID, IsComplete, CompDate FROM tblOrderAcc WHERE OrderDetailID = " & orddetid

OrderDetID hasn't been set.

 

[slharman1]

your third line of code



OrderDetID

your third line of code



OrderDetID hasn't been set.

I am setting in the first Do Until, after the first End If.
Do I have to move the SQL in side the loop?
Debug.print is returning the correct value.

 

[slharman1]

Yes, but I mean that's what's going wrong. It's not the after update event. It's something else.

You have commented out the line that sets orderdetid,
Is that why you get no records

The rs2 recordset is empty, that is what’s going wrong.
Did you look at post #25?
orddetid was (in #11) and is ( in #25) set lower in the code. It is set to the OrderDetailID field in rs.recordset. It should be giving me records on some instances of the loop and not on others.
Thanks

 

[Pat Hartman]

I need the check boxes for a query that is used to determine if the records will show up

June is trying to tell you that storing BOTH the date and the check is redundant. Your queries can use the date as the selection criteria instead of the YN field

Where ShipDT is Null

OR

Where ShipDT is Not Null

Depending on what you want to select.

You can use a button on the form to toggle the date so the user doesn't have to type it. We always strive to avoid storing redundant data in a relational database. You never want to run the risk of the two fields being in conflict and having both adds no value. Just adds jeopardy.

 

[slharman1]

June is trying to tell you that storing BOTH the date and the check is redundant. Your queries can use the date as the selection criteria instead of the YN field

Where ShipDT is Null

OR

Where ShipDT is Not Null

Depending on what you want to select.

You can use a button on the form to toggle the date so the user doesn't have to type it. We always strive to avoid storing redundant data in a relational database. You never want to run the risk of the two fields being in conflict and having both adds no value. Just adds jeopardy.

Hmm...... So if the record has a completion date - it is completed. So there is no need for the check box. Is that what you are trying to tell me?.
Because clicking the button to set the CompDate to today is not storing data in a field, it is just setting the CompDate to Date()

Instead of the button, I could use an unbound check box - correct?

That sounds like a better database.
I will correct that.

But Pat does that have anything to do with why rs2 keeps returning an empty recordset?
Thanks

 

[June7]

As already advised, rs2 is empty because variable orddetid is not set.

I am confused. Code has changed from a simple procedure to edit data in current record to editing entire recordsets. Why would edit in a single record mandate edit of multiple records? Suggest again you provide db for analysis.

The loop checks if IsComplete is False and if it is change values of IsComplete and CompDate.

Instead of looping recordsets, could probably just run UPDATE action sql.

 

[Pat Hartman]

You can use an unbound checkbox as the "toggle" for date. The problem is that people will expect it to be checked when there is a date and unchecked when there isn't. That's why I suggested a command button. It should be less confusing visually.

 

[Gasman]

So if the record has a completion date - it is completed.

Are you reading this as you write it?

 

[slharman1]

You can use an unbound checkbox as the "toggle" for date. The problem is that people will expect it to be checked when there is a date and unchecked when there isn't. That's why I suggested a command button. It should be less confusing visually.

my form is in continuous view and looks similar to a datasheet in order to get a list view and be able to have headers and footers and a button is not very appealing.
I only want the check box so if the user check the check box, it puts the current date in the completion field OR
if there is a date in the field when the form opens, the check box is already checked.
How would my checkbox get out of sink with the date field being null or >0?

 

[Pat Hartman]

If the checkbox is unbound, it can have only ONE state. Either checked or unchecked and whatever it is will be duplicated on ALL visible rows. For a continuous or DS view form, I would use the double-click event of the date control to work as the toggle.

When you have two fields that essentially contain the same data, there is always room for them to be different. Presumably, when you create the original form, you will test it well and ensure that checking the box is the ONLY way to update the date and you can even add validation in the Form's BeforeUpdate event to ensure they match. However, should you ever update the table with a query, you'll need to remember to keep the two fields in sync. Should you ever make another form that shows this data and allow updating, you have to ensure that the fields stay in sync. Maybe, you won't be the programmer who makes the bad change. Why create a situation where it can happen when you know better/

 

[slharman1]

You have a lot of fun with me don't you?
I am really not as stupid as I appear on here (well maybe not )

What I need is a check box for the user to declare the item complete and enter the current date into a field, now I see it can be an unbound check box as oppose to a button as pat suggests do to the continuous form's appearance. (very unattractive with a row of buttons)
But I think he is concerned with the check box getting out of sync with the date field.
My concern is using vba to set the date in the 2nd nested subform as those records are not visible on screen unless the user is on the current order detail record for the accessories record. Or will vba handle the checkbox getting checked since it is a unbound control instead of a field.
As much time as it takes me to write good code I don’t want to spend all day on it only to discover I can’t get the checkbox updated.
Sounds a little circular to me: If date is not null then chkbox true. And if chkbox =True then date = current date.
Darn it, lots too learn. where am I going wrong!

 

[Pat Hartman]

Only one record of a continuous form is ever "current". The current record is the one affected by the code in the form events so if you check the checkbox on row 3, that record and that record ONLY gets updated regardless of the fact that all rows show the same value in the unbound checkbox.

If you see different values for the checkbox in different rows, then the chckbox is BOUND rather than unbound and that means its value is being stored in the form's RecordSource in whatever field the checbox is bound to.

 

[gemma-the-husky]

@slharman1

I didn't realise you were setting orddetid further down in the code.
It looks like you are getting plenty of advice now.

Actually having a "completed" flag looks strange. Can you not determine the process is completed by some other mechanism, that avoids the need for a specific status flag?