Need VBA Code to set true or false to two records based on date

[dlhappy]

I do not know VBA syntax or how to properly set up programming using VBA code in MS Access.

I would like to automate changes to two records in the database depending on the current date. The situation is that I have two records for one person (each with a different primary auto number field). Each of the two records has a different address for mailing purposes. There is a winter residence and a summer residence. When I prepare a mailing list, I archive the inactive address, depending on the current date, and the active address only is included on the mailing list.

So I would like the VBA code to do the following:

;;REM summer address
If membershipID=12345 ((if date > 04/30/2012 and date < 12/01/2012) set Archived=false, else set Archived=true)

;;REM winter address
If membershipID=67890 ((if date > 04/30/2012 and date < 12/01/2012) set Archived=true, else set Archived=false)


Can the date be used with MMDD only without YYYY so that the code would not have to be edited each year?

Also, how do I enter the VBA code into the database.
--------------------------------------------------------------
NOTE: I already have some unrelated code in this database. The code was written for me with instructions for its insertion, a few years ago. At this point, I don't remember the steps taken to enter the code into the database. But in this case, the code is linked to a form and only activates if I add or edit a record.

It is shown as a Microsoft Office Access Class Objects/Form frmMembership.
It inserts the current date into the AddDate field when a new record is created. The EditDate field has the current date inserted when there is an edit to an existing record.
this is the unrelated code that already exists in this database:

Option Compare Database

Option Explicit

Private Sub AddDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub EditDate_AfterUpdate()

End Sub

Private Sub EditDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
Me![EditDate] = Date
End If

End Sub

------------------------------------------------------------------------------------------------


However, in both cases, I manually have to post something in a record for the VBA codes to activate.
What I want now, if for the two records to automatically update the Archived field whenever the database is opened -- that is, I don't want to have to remember to do anything to the two records based on the date. I want VBA code to have the database do this automatically whenever the database is opened.

Thanks.

Allan

 

[plog]

VBA isn't the best answer, a query is. Specifically a select query and not an update query.

Instead of using this table directly you set up a query to select the correct records to work with based on the date parameters you've defined. Then when you need it you simply reference this new query or run it to get the records you need based on the date.

If you need more specific advice, let me know exactly how you are using this data. Is it the basis for reports, forms or other queries? Or are you simply opening and working directly on the table?

 

[dlhappy]

VBA isn't the best answer, a query is. Specifically a select query and not an update query.

Instead of using this table directly you set up a query to select the correct records to work with based on the date parameters you've defined. Then when you need it you simply reference this new query or run it to get the records you need based on the date.

If you need more specific advice, let me know exactly how you are using this data. Is it the basis for reports, forms or other queries? Or are you simply opening and working directly on the table?

Hi Plog:

Thanks for your reply.

I am an unpaid volunteer (as is everyone else) for a non-profit educational organization. We have a paid membership and mail publications to members a few times a year. Some members are not required to pay as they have a special category status. Also, to maintain a complete history, members are never deleted but rather archived due to non-payment, death, etc. The mailing list that I generate before each publication mailing includes all members except those that are archived, and those for whom we do not have a proper address (address line 1 is blank -- we had such a member). Since it is very easy to accidentally press the mouse key and thereby place an "x" in the archived field, I have another field, ArchiveReason. If the archived box is checked but there is no archive reason (ArchiveReason is blank), then the member IS included on the qrymembershipMailingList. This query is exported as an Excel worksheet and emailed to the company that prints and mails our publications for us. Incidentally, I also have a error query that shows if there are any members who are archived and for whom there is no archive reason. The query should return no records, but if it does, I correct the record(s), to either enter a proper reason, or uncheck archived box, before generating a mailing list.

As I mentioned in my first post, we have a member (and presumably could get more) who has a winter residence and a summer residence and wants publications mailed to the proper residence, depending upon the dates. I have been two records for him (but use only one record to record financial data so as to prevent duplicate information in the total for all members). I archive one record or the other, depending on the dates. It is this function that I would like to automate (archiving one record and unarchiving the other for him, as the dates warrant).

This is the SQL view of the Design View of qryMembershipMailingList:

SELECT tblMembership.Title, tblMembership.LastName, tblMembership.FirstName, tblMembership.AddressLine1, tblMembership.AddressLine2, tblMembership.State, tblMembership.Country
FROM tblMembership
WHERE (((tblMembership.AddressLine1)<>" ") AND ((tblMembership.Archived)=False)) OR (((tblMembership.Archived)=True) AND ((tblMembership.ArchiveReason) Is Null))
ORDER BY tblMembership.LastName, tblMembership.FirstName;

How would I change the query to automatically take only the two records into account as far as the current date, so as to only include the record for the winter address or summer address, as the case may be, for that member, while not changing anything as far as other members. I know I would need to use the member's two IDs.(membershipID from tblMembership. What should I enter into the qryMembershipMailingList design view and wouldn't I have to enter the same in all of the address fields (Addressline1, Addressline2, State)?

Thanks.

Allan



This is the table relationship:

 

[plog]

What field would you use to determine which record to use? I mean, where is the date field that determines if it is a winter or summer address?

Also, your description on how the process works is contradictory.

If the archived box is checked but there is no archive reason (ArchiveReason is blank), then the member IS included on the qrymembershipMailingList. This query is exported as an Excel worksheet and emailed to the company that prints and mails our publications for us. Incidentally, I also have a error query that shows if there are any members who are archived and for whom there is no archive reason. The query should return no records, but if it does, I correct the record(s), to either enter a proper reason, or uncheck archived box, before generating a mailing list.

At first you say you include records that are marked archived and have no reason. But then you say you run a query so you can manually correct them prior to running the list. Which is it?

If you do correct them prior to running a list, then you can simplify your WHERE clause to:

WHERE (tblMembership.AddressLine1<>" ") AND  (tblMembership.Archived=False)

The key to the correct address based on the date is identifying what date in the record allows you to know which record to use.

 

[dlhappy]

Hi Plog:

First I'll clear up the apparent inconsistency. I have on occasion forgotten to run the query that shows an error situation; that is, the archived box is checked ("true") but there is no archived reason (ArchiveReason is blank). When I run that query, if any such inconsistent records are found, I can correct the incorrect records by unchecking the archived box. However, since I do sometimes forget to run that error checking query, I find that the qryMembershipMailingList where statement, as shown above acts in such a way to to treat that type of error as if the person is not archived and thereby include him/her on the mailing list. That way, they do not miss publications solely do to an incorrect check mark in the archived field. Since that works fine, I don't want to change the compound where-clause for a simpler one, although you are correct, it would not be needed IF I always remembered to run the error checking query and manually made corrections to any incorrect records.

As for the real issue here, the member with the two addresses, I have two records for him, as I said, each record having a different address. I do not have an actual date field for each record but rather have the Notes (memo) fields with comments that for him, includes "Dec-Apr. address." on one record, and "May-Nov.address" on the other record. I also have these two statements, one for each record, in the ArchiveReason field. All that I do (but I have to remember to do so), is before running qryMembershipMailList, is put a check mark in the Archived filed for the correct record, based on the current month.

Do you suggest having two new fields added named, perhaps, "SeasonStartDate" and "SeasonEndDate" and then modify the query for those fields to show current date =or> than the start date, etc. and =or< the end date --- but then the problem of the year will exist, in that I would have to modify the query where-clause every year, as well as his two records. Also, what if another member subsequently has two addresses with different dates from the present member. Then the query won't work properly, unless there is a way to have separate statements based on MembershipID..

Thanks.

Allan

 

[plog]

I don't mind helping you, but the setting up access do this automatically might be more work than manually adding the correct data this problem effects to the mailing list each time you generate one. But, I'm game if you are.

The below works only for entire months (1st day of one month through last day of another month), if you want it to work for specific days, it gets even worse.

To have access automatically decide which record you want, you are going to have to store the dates that the record is good for--for every record. I suggest you do this by adding 2 new fields to your address table named something like this:

AddrUseMonthBegin
- numeric field to hold number of month that you begin using this records address
AddrUseMonthEnd
- numeric field to hold number of month that you stop using this records address

Then you need to populate data for this field every record. For people who have one address AddrUseMonthBegin should be set to 1 and AddrUseMonthEnd should be set to 12. That way their values will evaluate to January 1st through December 31st and always pass validation and be included.

For a person with one address to use April through November and another to use December through March you would set the first record's AddrUseMonthBegin to 4 and AddrUseMonthEnd to 11, and the other record to 12 and 3 respectively.

Next you have to update your WHERE clause to see if each records' AddrUseMonthBegin and AddrUseMonthEnd values calculate to a record you should use based on the current date. This is what your new WHERE clause would look like:

WHERE (((tblMembership.AddressLine1)<>" ") AND ((tblMembership.Archived)=False) AND ((ValidateAddrUsePeriod([AddrUseMonthBegin],[AddrUseMonthEnd]))=True)) OR (((tblMembership.Archived)=True) AND ((tblMembership.ArchiveReason) Is Null))

Then you are going to have to create a module with the following code for the ValidateAddrUsePeriod function which is now in your WHERE clause:

Function ValidateAddrUsePeriod(BeginMonth, EndMonth)
    ' determines if current date is within first day of BeginMonth and last day of EndMonth

ret = False
    ' default value
    
If (Month(Date) >= BeginMonth) And (Month(Date) <= EndMonth) Then ret = True
    ' current month falls between BeginMonth and EndMonth, which occur in same year
    ' i.e. Current Month = 4, BeginMonth=3, EndMonth=7, ret would be True
    
If (BeginMonth > EndMonth) And (Month(Date) >= BeginMonth) Then ret = True
    ' current month falls after BeginMonth, which occurs in different year than EndMonth
    ' i.e. Current Month = 10, Begin Month=9, EndMonth=2, ret would be True

If (BeginMonth > EndMonth) And (Month(Date) <= EndDate) Then ret = True
    ' current month falls before EndMonth, which occurs in different year than BeginMonth
    ' i.e. Current Month = 2, Begin Month=8, EndMonth=4, ret would be True
    
ValidateAddrUsePeriod = ret
    ' returns result
    
End Function

Then your query will select the correct records based on the date ranges.

 

[dlhappy]

HI Plog:

You're right that the setting up would be more work than that which I do manually, especially since we only have three regular mailings per year. However, your solution is a perfect way to accomplish the task, not just for the one member but is universal for any and all current or future members that have or don't have two different addresses and require mailings to those addresses based on the month of the year.

I imagine that magazine companies probably use a system such as that you have developed, but with different criteria so as to automate the process of mailing renewal notices and stopping the mailing of magazines up on the expiration of a subscription.

What I probably will do, after my wife and I go next week, and return a little more than a week later, from a visit to her parents, is copy the database and work with the copy to incorporate your method. In total, right now, we have 315 records so even if I had to manually type a "1" for the begin month and a "12" for the end month for all but two records (the member with the two addresses) it would not be too much work --- however I could probably automate that with a find/replace for the field (replace " " with 1, for example) and in the table, set the default value to 1 for BeginMonth and 12 for EndMonth.

Originally, when I started this thread, I though that it was a simple matter of adding a few lines of code just for the two records of the one member with the two address. I really appreciate the time that you have taken to come up with and post the solution which will be universal for the entire database. Many thanks!

Allan

 

[dlhappy]

I don't mind helping you, but the setting up access do this automatically might be more work than manually adding the correct data this problem effects to the mailing list each time you generate one. But, I'm game if you are.

The below works only for entire months (1st day of one month through last day of another month), if you want it to work for specific days, it gets even worse.

To have access automatically decide which record you want, you are going to have to store the dates that the record is good for--for every record. I suggest you do this by adding 2 new fields to your address table named something like this:

AddrUseMonthBegin
- numeric field to hold number of month that you begin using this records address
AddrUseMonthEnd
- numeric field to hold number of month that you stop using this records address

Then you need to populate data for this field every record. For people who have one address AddrUseMonthBegin should be set to 1 and AddrUseMonthEnd should be set to 12. That way their values will evaluate to January 1st through December 31st and always pass validation and be included.

For a person with one address to use April through November and another to use December through March you would set the first record's AddrUseMonthBegin to 4 and AddrUseMonthEnd to 11, and the other record to 12 and 3 respectively.

Next you have to update your WHERE clause to see if each records' AddrUseMonthBegin and AddrUseMonthEnd values calculate to a record you should use based on the current date. This is what your new WHERE clause would look like:

WHERE (((tblMembership.AddressLine1)<>" ") AND ((tblMembership.Archived)=False) AND ((ValidateAddrUsePeriod([AddrUseMonthBegin],[AddrUseMonthEnd]))=True)) OR (((tblMembership.Archived)=True) AND ((tblMembership.ArchiveReason) Is Null))

Then you are going to have to create a module with the following code for the ValidateAddrUsePeriod function which is now in your WHERE clause:

Function ValidateAddrUsePeriod(BeginMonth, EndMonth)
    ' determines if current date is within first day of BeginMonth and last day of EndMonth

ret = False
    ' default value
    
If (Month(Date) >= BeginMonth) And (Month(Date) <= EndMonth) Then ret = True
    ' current month falls between BeginMonth and EndMonth, which occur in same year
    ' i.e. Current Month = 4, BeginMonth=3, EndMonth=7, ret would be True
    
If (BeginMonth > EndMonth) And (Month(Date) >= BeginMonth) Then ret = True
    ' current month falls after BeginMonth, which occurs in different year than EndMonth
    ' i.e. Current Month = 10, Begin Month=9, EndMonth=2, ret would be True

If (BeginMonth > EndMonth) And (Month(Date) <= EndDate) Then ret = True
    ' current month falls before EndMonth, which occurs in different year than BeginMonth
    ' i.e. Current Month = 2, Begin Month=8, EndMonth=4, ret would be True
    
ValidateAddrUsePeriod = ret
    ' returns result
    
End Function

Then your query will select the correct records based on the date ranges.

Hi:

I finally got around to trying the above suggestions. I made a copy of the database to work with. I added the two new fields and updated all records to include the data for the new fields. I also created a revised form and MailingListNew query. I copied and pasted the above codes, the first into the query design, and the second, in a module (in the VBA window), respectively.

When trying to open the new query, I get the following error message, and the query won't open:

Undefined function 'ValidateAddrUserPeriod' in expression.

How do I correct this?

Thanks.

 

[plog]

Go into the Module and click 'Debug' in the top menu and then 'Compile X' where X is the name of the Module.

That will point out any errors in the code.

 

[dlhappy]

Go into the Module and click 'Debug' in the top menu and then 'Compile X' where X is the name of the Module.

That will point out any errors in the code.

Hi Plog:

I did that and researched a beginners book and was able to correct the error. However when I open the query in question qryMembershipMailingListNEW it opens without any error messages, BUT, it does not work properly.

The database originally had a few hundred records. I deleted all but 6 (5 active and 1 archived) and modified the names, addresses, etc. so as to really be anonymous.

When the qryMembershipMailingListNEW is opened, it lists Michael Mu, twice, once with each different address, even though, I added the two numeric fields for the months at each address.

I hope that I am not imposing too much, but I think that the best way to see the problem is to see the modified sample database directly, including the way the query, in design view shows how it transformed the "WHERE" code that you wrote for me. Therefore, I have attached, the sample database in a ZIP file with this message/posting.

I did not bother to delete all of the various forms, queries, etc. that really have nothing to do with the modification that I desire. So, please just ignore them. The changes made for desired result appear in tblMembership, frmMembershipNEW, qryMembershipMailingListNEW and module1.

Thanks for your previous help and any additional help that you can provide.

 

[plog]

You've got 3 lines of criteria, some that are redundant (Is Null And Is Null) and some that are contradictory (False And True). To get the ValidateAddrUsePeriod function to work, you need to add TRUE to every criteria line you are using.

 

[dlhappy]

You've got 3 lines of criteria, some that are redundant (Is Null And Is Null) and some that are contradictory (False And True). To get the ValidateAddrUsePeriod function to work, you need to add TRUE to every criteria line you are using.

Hi Plog:

I corrected the query. Incidentally, all of those redundant and contradictory statements did look strange to me; however, Access automatically put them in when I added (pasted) the complete where statement that you wrote for me. So, I guess Access did not translate it well.

Anyway, after correcting the query, IT WORKS PERFECTLY, AS INTENDED. I also tested this by changing the system date to August to see if the summer address would be in the mailing list instead of the winter address after the change, and the query showed the record with the summer address. Changing back to December, the query again showed the winter address only.

Thank you very, very much for all the time that you spent helping me with this, it is greatly appreciated!

 

[dlhappy]

Hi:

I just noticed that the code failed to work now that we are in January and the current month = 1.

I found the error and I'm posting it here, really for myself, because I've been copying this thread by saving the webpage, complete onto my computer so as to have it for reference.

Anyway, the last If statement was originally as follows:

If (BeginMonth > EndMonth) And (Month(Date) <= EndDate) Then ret = True
' current month falls before EndMonth, which occurs in different year than BeginMonth ' i.e. Current Month = 2, Begin Month=8, EndMonth=4, ret would be True
---------------------------------------------------------------------------------------------------------

The error is that in the line of code, EndDate should be EndMonth.

I didn't mention it before, but when I ran the Debug, besides defining EndDate as an integer, which I should not have because as I now discovered, EndDate should have been EndMonth, I had to include the following statement after the Function statement: Dim ret As Boolean

Thanks.