After Update or Update Query - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

After Update or Update Query

Darrin Hebert

4 years ago

I'm managing a member listing database and I want to flag a member as 'Too New To Rate" based on when they joined the club. I have a (Y/N) field named ToNewToRate in the members table.

I want to flag a member as too new to rate if they joined within the last two months.   This obviously changes as months pass, but I don't want to manually set the flag record by record.

What is the best way to accomplish this.  I tried using a database macro 'after update' but I believe this is record specific and I want the action to run on the entire table to reset the flag if warranted.

I then tried to create an update query, but I'm having trouble with the criteria.  the logic behind setting and removing the flag is as follows...

Assuming 5/1/2022 is the current date, if a member joined the club on 5/1/2022 the flag should be set to yes until 7/1/2022.  This is sort of like an automatic toggle.  I would love help on doing this automatically, but perfectly willing to run the update query every time I need to.

If you are aware of a better way of doing this, I'm all ears.

Thanks a million for any assistance anyone can provide.

Kevin Robertson @Reply

4 years ago

You could do something like this:

'create a sub to be called from multiple places

Private Sub SetRating()



    Dim D As Date

    

    If JoinDate <> "" Then

        D = DateAdd("m", -2, Date)

        If JoinDate >= D Then

            TooNewToRate = True

        Else

            TooNewToRate = False

        End If

    End If

    

    Me.Refresh

    

End Sub

'run code when form opens and when you navigate records

Private Sub Form_Current()



    SetRating

    

End Sub

'run code after the date has been updated

Private Sub JoinDate_AfterUpdate()



    SetRating

    

End Sub

	Kevin Yip @Reply 4 years ago
Darrin, since the flag could change every day, you could create a query with a calculated field: SELECT JoinDate, IIf(JoinDate >= DateAdd("m", -2, Date), True, False) As Flag FROM Mytable; This way, it is truly automatic. When you need to check statuses, you just open this query.

Darrin Hebert OP @Reply

4 years ago

Hi Kevin, thanks for the suggestion.... The VBA approach makes sense, but I've not yet advanced in this area of learning.... my trouble with this suggestion is with the Sub itself.  in my database the members table has a field name of [Date Joined CC] so I'm having some trouble making reference to that field in the members table.
I get a compile error saying "External name not defined".
I modified your code to match my field name as follows....

Private Sub SetRating()

    Dim D As Date

    If [Date Joined CC] <> "" Then
        D = DateAdd("m", -2, Date)
        If [Date Joined CC] >= D Then
            TooNewToRate = True
        Else
            TooNewToRate = False
        End If
    End If

    Me.Refresh

End Sub

Scott Axton @Reply

4 years ago

Darrin -
If you are looking for a way to have a visual queue to see the status of the member check out the
Conditional Formatting video.
Just think of basing it on the join date field.

The solutions you get here will tend to lean toward VBA but you could set it up in a Macro.  Either way you will need something to trigger that macro or VBA code.  When do you want it to run in other words.  Start of the database?  When you open that member?  Want to do it manually, ie push of a button? Etc.  That will determine where to place the code or macro.

The second thing you are speaking about is updating records, so think Update Query.

Where exactly are you in the courses?  That will help us tailor a solution to your knowledge level until you get more advanced.

Piece of advice:  I don't know how far along you are in your db with forms, reports and such but really consider removing spaces from your field names.

Darrin Hebert OP @Reply

4 years ago

Hi Scott.

I'm not really looking for a visual queue, but rather wanting to exclude "new members" from certain reports, based on them being new members. My desire is to have my members table always being aware of how new a member is... ie the flag. Keeping the flag checked or unchecked based on the current date. I think this could be accomplished in many ways... maybe devising a query that can determine the difference between the current date and the join date would be a bit more straight forward... I really just want to exclude the member from certain reports... I don't have to do it by setting the flag in the table.

As for my learning level... I've been an access user for many many years 20+, I tend to look for training as I run into something I'm not familiar with. I'm a technology guy (career wise) but have never been a developer per se. so for me, I explore topics of interest and review concepts. Hope that helps and really appr the response/support

	Kevin Robertson @Reply 4 years ago
Here is one way to do it with an Update Query (see screenshot below) IIF Update Queries

	Kevin Robertson @Reply 4 years ago

	Darrin Hebert OP @Reply 4 years ago
Kevin, Thanks a million... This will work just fine for me. I will just run this update query as a step before producing the report I want to exclude new members from. oh, and I will follow your advice about rename fields... I heard the recommendation in several of the classes, just never went back to do it... but I will. Thanks again!!!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View