After update for check box not working (1 Viewer)

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
I can't figure out why this is not working.
I have an order form that has a subform called order details
I have this code in the after update for checkbox on the order details
I also have the same code on the checkbox on the subform of that form for order details accessories
neither one works, what the heck do i have wrong! I have tried both with and without the Me.dirty line but no joy.
Code:
Private Sub IsComplete_AfterUpdate()

    If Me.IsComplete = True Then
    Me.CompDate = Date
    Me.Dirty = False
    Else
    Me.CompDate = Null
    Me.Dirty = False
    End If
    
End Sub
 

June7

AWF VIP
Local time
Today, 03:27
Joined
Mar 9, 2014
Messages
5,425
What does "not working" mean - error message, wrong result, nothing happens?

The IsComplete field is really redundant. If CompDate has a value then record is flagged as complete. If intent is just to make life simpler for user so they don't have to type in current date, could use a button instead of checkbox.
 
Last edited:

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
What does "not working" mean - error message, wrong result, nothing happens?

The IsComplete field is really redundant. If CompDate has a value then record is flagged as complete. If intent is just to make life simpler for user so they don't have to type in current date, could use a button instead of checkbox.
Sorry for my poor explanation - it is such a simple task I was dumbfounded that it didn’t work.
what is supposed to happen is this:
When I check the checkbox field, IsComplete, the code should set the date field, CompDate to today’s date, it the checkbox in unchecked it should set the date to null.
it works now, for some reason I had to put a refresh command for the sub form to get it to work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:27
Joined
Feb 19, 2002
Messages
42,981
We can't see your form and so don't know what other data might be present but forcing the record to save at this point is generally a bad idea. If you have other fields, they might not already be entered and so there would not be proper validation.

PS, formatting your code will make it easier to read. That means for YOU also.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
I can't figure out why this is not working.
I have an order form that has a subform called order details
I have this code in the after update for checkbox on the order details
I also have the same code on the checkbox on the subform of that form for order details accessories
neither one works, what the heck do i have wrong! I have tried both with and without the Me.dirty line but no joy.
Code:
Private Sub IsComplete_AfterUpdate()

    If Me.IsComplete = True Then
    Me.CompDate = Date
    Me.Dirty = False
    Else
    Me.CompDate = Null
    Me.Dirty = False
    End If
   
End Sub
If you have Me.Dirty = False in each section of the If, then just execute it once outside of that If construct?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Sep 12, 2006
Messages
15,614
if the record is not able to be saved, then you should get an error, but your error handling may suppress the error report.

It's worth adding record selectors to your form, even if only for testing. The black arrowhead changes to a pencil if the record is dirty (edited)
Note that if your check box is not bound to the table, clicking the checkbox of itself will not make the record dirty - so maybe it's not doing anything because the record has already been saved, and doesn't need to be saved again.
 

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
We can't see your form and so don't know what other data might be present but forcing the record to save at this point is generally a bad idea. If you have other fields, they might not already be entered and so there would not be proper validation.

PS, formatting your code will make it easier to read. That means for YOU also.
The check box is just to mark a record complete. No other data is changed (well, except for the date checked)

I don’t understand what you mean by formatting my code - especially the “That means for YOU also” Please explain.
thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
I don’t understand what you mean by formatting my code - especially the “That means for YOU also” Please explain.
thanks
You are not indenting your code.
Makes it harder for us to read, so even harder for you.?
Code:
Private Sub IsComplete_AfterUpdate()

    If Me.IsComplete = True Then
        Me.CompDate = Date
    Else
        Me.CompDate = Null
    End If
    Me.Dirty = False
    
End Sub
 

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
You are not indenting your code.
Makes it harder for us to read, so even harder for you.?
Code:
Private Sub IsComplete_AfterUpdate()

    If Me.IsComplete = True Then
        Me.CompDate = Date
    Else
        Me.CompDate = Null
    End If
    Me.Dirty = False
   
End Sub
Oh that, I started doing that with all my code just last night. It makes it much easier to read, especially nested If,Then statements. Thanks again :)
 

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
Oh that, I started doing that with all my code just last night. It makes it much easier to read, especially nested If,Then statements. Thanks again :)
OK Gasman, I think I am indenting correctly now. Please take a look at my code below and guide me to what I am doing wrong.
This is the afterupdate event on the main form that has two nested subforms. So its: Order>OrderDetail>OrderAcc

So RS gets Order Detail records for the current order, then RS2 is supposed to get Order Detail Accessories for the current Order Detail record

The line "orddetid = rs.Fields(OrderDetailID)" in the first loop must not be setting the variable "orddetid" -

Or if I am going about this all wrong, please guide me in the right direction.
Thanks

Code:
Private Sub IsComplete_AfterUpdate()

    Dim db As DAO.Database
    Dim rs As Recordset
    Dim strItemComp As String
    Dim ordid As Long
    Dim rs2 As Recordset
    Dim strAccComp As String
    Dim orddetid As Long
    
    ordid = Me.txtOrdID
    
    'OrderDetailID from oder detail subform
    'orddetid = Me!frmOrderDetailsListToMarkForCompletion.Form!txtDetID
        
    Set db = CurrentDb
    strItemComp = "SELECT OrderDetailID, IsComplete, CompDate FROM tblOrderDetails WHERE OrderID = " & ordid
    strAccComp = "SELECT OrderAccID, OrderDetailID, IsComplete, CompDate FROM tblOrderAcc WHERE OrderDetailID = " & orddetid

    Set rs = db.OpenRecordset(strItemComp)
    'Set rs2 = db.OpenRecordset(strAccComp)

    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
                    orddetid = rs.Fields(OrderDetailID)
                    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
                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
 

June7

AWF VIP
Local time
Today, 03:27
Joined
Mar 9, 2014
Messages
5,425
Did you consider comments in post 2? Quite possibly IsComplete field is totally unnecessary and this VBA can be eliminated. I expect the "IsComplete" status can be calculated when needed instead of committing to table. Also, only the master record should have this status data, not all related child records.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
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. :)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Sep 12, 2006
Messages
15,614
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.
 

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
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.
I know how to put a break code in but I don’t know how to see where it’s going wrong or if it’s firing.
 

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
Did you consider comments in post 2? Quite possibly IsComplete field is totally unnecessary and this VBA can be eliminated. I expect the "IsComplete" status can be calculated when needed instead of committing to table. Also, only the master record should have this status data, not all related child records.
The check boxes are required. This is not an order db in the sense that I am invoicing with it and can check it complete once invoiced. It is a fabrication db. I need the check boxes for a query that is used to determine if the records will show up in list for employees to be able to clock in and out of.
 

June7

AWF VIP
Local time
Today, 03:27
Joined
Mar 9, 2014
Messages
5,425
Set breakpoint and execute code one line at a time. Check content of variables as code progress, see if it goes where you expect for the given data.
If you want to provide db for analysis, follow instructions at bottom of my post.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Sep 12, 2006
Messages
15,614
BREAKPOINTS

In the code module click in the margin where your check should take place. You should see a brown/burgundy circle/blob marking the breakpoint.

When the code runs, your code will stop at the break point. You can then press F8 to advance the code a line at a time. You can hover over variables to see their values, and thereby make sure that the code IS doing what you expect. You can run the code, rather than F8. I think it's F7, or the run option in the run menu of the debugger,

You can set multiple breakpoints, and it will stop at the next breakpoint. You can use a breakpoint to check/manage a loop. You can edit the code while the code is running. Some edits will stop the code, but some will work. You will be warned if the code will stop.

Click the blob to remove the break point. There is a menu option to remove all breakpoints (maybe F9 offhand)
 

slharman1

Member
Local time
Today, 06:27
Joined
Mar 8, 2021
Messages
467
Set breakpoint and execute code one line at a time. Check content of variables as code progress, see if it goes where you expect for the given data.
If you want to provide db for analysis, follow instructions at bottom of my post.

BREAKPOINTS

In the code module click in the margin where your check should take place. You should see a brown/burgundy circle/blob marking the breakpoint.

When the code runs, your code will stop at the break point. You can then press F8 to advance the code a line at a time. You can hover over variables to see their values, and thereby make sure that the code IS doing what you expect. You can run the code, rather than F8. I think it's F7, or the run option in the run menu of the debugger,

You can set multiple breakpoints, and it will stop at the next breakpoint. You can use a breakpoint to check/manage a loop. You can edit the code while the code is running. Some edits will stop the code, but some will work. You will be warned if the code will stop.

Click the blob to remove the break point. There is a menu option to remove all breakpoints (maybe F9 offhand)
Got it, Thanks.
The problem in my code is that the recordset if empty, but it shouldn't be, when I design a select query the same way i get my records.
Can't figure out where i am going wrong.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Sep 12, 2006
Messages
15,614
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"
 

Users who are viewing this thread

Top Bottom