Error During Loop For Updating True/False

[Levonas]

What I am trying to do here is run through all records and see if the field called "CLASSUP" date is equal to or earlier than today, then if that is true I want to set the checkbox called "NMT" to False, Else go to the next record.
So I set this module up and when I call it, I get an error at the rs!NMT = False
I get it even if I put "rs.NMT = False"
I get it even if I put "rs.Edit" and then on the next line put "rs!NMT = False" or "rs.NMT = False"
When I thought I had it at one point, there were no errors, but when I opened up the table to verify the records were updated, nothing had changed.

Public Sub UpdateNMTStatus()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MASTER", dbOpenTable)
    Do While Not rs.EOF
        If rs!CLASSUP <= Date Then
           rs!NMT = False
           Else
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Just FYI I have coded the form to run on a record while the form is open so I get immediate update of the NMT checkbox based off the date and it works fine, but I am not clicking through all 1700+ to get them all to update.


So, what am I doing wrong? If this is not the best way, what is the best way to update the value of a checkbox (which then defines how queries work based off the true or false of the checkbox) in all the records based off the date criteria?
Thanks

[ssanfu]

Because I can't see your dB, you do have a field in the table named "NMT"?

On a COPY of your dB, run this query (SQL).
"UPDATE Master SET NMT = TRUE"

Now run your code. If you single step (step debug) through your code you will be able to see the variables/data change.
I made a couple of changes in your code

Public Sub UpdateNMTStatus()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("MASTER", dbOpenTable)
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast
        
        'debugging info
        MsgBox "Record count = " & rs.RecordCount
        
        rs.MoveFirst

        Do While Not rs.EOF
            If rs!CLASSUP <= Date Then
                rs.Edit
                rs!NMT = False
                rs.Update
            End If
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Sub

You could attach your dB for analysis.........


Edit: Changed the FALSE to TRUE in the SQL. ( I had it backwards)

[Levonas]

Yes, there is an NMT field in the "MASTER" table.
I will give it a try tomorrow when I get in to the office.
Question, will that SQL set all of the values to false when I run it? Because I definitely do not want that. They need to be true unless specifically set to false or the date is today or earlier. The value is defaulted to true for every new record.
Or was that simply for the purpose of testing the code? The db I am working on is just an update from version 2 to version 3. Version 2 is running with a backup copy tucked away so whatever we do, don't worry about screwing up the db haha. It's back up is safe.
Another question, if the field "CLASSUP" date is null or the date is later than today, will that code just skip any update and move to the next one? That is the reason I put the Else right before the End If in my code thinking that is how it would function. Just curious.
I was going to attach the database but for some reason I could not get it below 1.23mb after stripping it down to one form, the table with only 2 made up records, 1 module with the code, and compacting it. The upload kept saying it was too large yadda yadda. I will see if I can get it smaller tomorrow.

[ssanfu]

will that SQL set all of the values to false when I run it

Yes, but I had it backwards. The SQL should set NMT to TRUE, so the code can set NMT to False when "CLASSUP" is less than or equal to Today.

Or was that simply for the purpose of testing the code?

Yes.

That is why you use a copy of the dB. Setting NMT to TRUE gives you a known state. Running the code would set NMT to False if the conditions were met.

if the field "CLASSUP" date is null or the date is later than today, will that code just skip any update and move to the next one?

Yes. Don't need the Else clause, there was nothing in it.



Do a "Compact and Repair", the Zip it.

[ssanfu]

I just created a test table with the two fields. Added dummy data - 10 records - 1 with Null date.

Code completed as expected. Dates that were <= today set to False, records with date null or GT today, left as True

Can modify the code to always set NMT state (won't need the SQL Update line:

Public Sub UpdateNMTStatus()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb

    Set rs = db.OpenRecordset("MASTER", dbOpenTable)
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast

        'for debugging
        'Comment out when done debugging code.
        MsgBox "Record count = " & rs.RecordCount

        rs.MoveFirst

        Do While Not rs.EOF
            rs.Edit

            If rs!CLASSUP <= Date Then
                rs!NMT = False
            Else
                rs!NMT = True
            End If

            rs.Update
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    'for debugging
    'Comment out when done debugging code.
    MsgBox "Done"

End Sub

[Levonas]

Well that certainly is good news. I will have a little more to look forward to other than the weekend tomorrow thanks to you.
As soon as I get in I will run this through the course on the database and report back.
The one thing I see I will need to leave alone is that even though the default for NMT is true, there are instances every week where a student will come in and not be assigned to NMT (which is a division in a department here), but the student goes to another division within the department and has no affirmed class up date yet, so we then set the value to false. In that case the criteria would be that the date is null and set to false. So I will need leave out always setting the value true. Other than that, this maybe able to be marked as solved tomorrow!

[Levonas]

ssanfu, this worked like a charm. I adjusted the code by removing the "Else rs!NMT = True" to allow for the variation in student status and it is spot on for what is needed.
Thank you!

[Levonas]

I just ran into an issue. I tested that on a DB that was not split and it worked. When I tried it on the DB that was split, it had an error here;

Set rs = db.OpenRecordset("MASTER", dbOpenTable)

This is the location of the DB;

S:\CRRY\CID_UNIT\Special Assistants\PRIVATE\CIDUCS DUTY SECTIONS

The frontend is located on the desktop of the users.

[ssanfu]

From Help:
When creating a Recordset object using a non-linked TableDef object in a Microsoft Access workspace (Access workspace: A workspace that uses the Access database engine to access a data source. The data source can be an Access database file, an ODBC database, such as a Paradox or Microsoft SQL Server database, or an ISAM database.), table-type Recordset objects are created. Only dynaset-type or snapshot-type Recordset objects can be created with linked tables or tables in Microsoft Access database engine-connected ODBC databases.
(I added the bold blue)

You can't update a snapshot type recordset, so use dbOpenDynaset. Try

Set rs = db.OpenRecordset("MASTER", dbOpenDynaset)

or if you want Access to decide which recordset type to use, don't include the type clause"

Set rs = db.OpenRecordset("MASTER")

[ssanfu]

The one thing I see I will need to leave alone is that even though the default for NMT is true, there are instances every week where a student will come in and not be assigned to NMT (which is a division in a department here), but the student goes to another division within the department and has no affirmed class up date yet, so we then set the value to false. In that case the criteria would be that the date is null and set to false. So I will need leave out always setting the value true. Other than that, this maybe able to be marked as solved tomorrow!

Can the date be changed so that NMT should be set to TRUE? That is why I added the ELSE clause.

Consider:

        Do While Not rs.EOF
            rs.Edit

            If rs!CLASSUP <= Date Or IsNull(rs!CLASSUP) Then
                rs!NMT = False
            Else
                rs!NMT = True
            End If

            rs.Update
            rs.MoveNext
        Loop

[Levonas]

You sir, are the man. That cleared it right up and the process is back in business.