UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Looping Through Datasheet Automatically, Access 2016    
 
   
amcintosh
post Feb 18 2019, 12:26 AM
Post#1



Posts: 38
Joined: 1-January 19



Need your help with something

Trying to setup a code to automatically update (on Form load) the Valid column on All Records so it reads "Valid" if the system date < the Expiry Date, and Expired if the system date > then the expiry date on the sub form as below
Attached File  Skips1.JPG ( 43.52K )Number of downloads: 7



So I made this Macro up (which is temporarily assigned to a command button), however all it does is kills access and make my system fan go fast.

Any ideas where I've stuffed up?

CODE
Private Sub Command68_Click()

Dim sdate As Date
Dim edate As Date
sdate = Date

Dim rst As DAO.Recordset
Set rst = Me.ExSFRM_Residue.Form.RecordsetClone

Do Until rst.EOF
      edate = Me.ExSFRM_Residue!txtExpireDate
            If edate < sdate Then
            With Me.ExSFRM_Residue!txtApvd
                  .Value = "Expired"
                  .BackColor = RGB(255, 69, 0)
            End With
            Else
            With Me.ExSFRM_Residue!txtApvd
                  .Value = "Valid"
                  .BackColor = RGB(124, 252, 0)
            End With
End If
Loop
            
End Sub


Cheers



Go to the top of the page
 
theDBguy
post Feb 18 2019, 12:41 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


Hi. You shouldn’t need to use code to do this. Just use a calculated column in your query and conditional formatting in your form.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Feb 18 2019, 02:37 AM
Post#3



Posts: 737
Joined: 25-January 16



Right, expression in textbox ControlSource like:

=IIf([txtExpireDate] < Date(), "Expired", "Valid")

Or do the calculation in query.

There is no need to save this calculated value to table.









This post has been edited by June7: Feb 18 2019, 02:46 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
amcintosh
post Feb 18 2019, 10:56 PM
Post#4



Posts: 38
Joined: 1-January 19



Cheers for that. Was so simple, I don't know why I didn't thought of that.
Go to the top of the page
 
theDBguy
post Feb 18 2019, 11:03 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,073
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 09:32 AM