Am I doing this mysql query correct

[avayaman]

To give you a better idea, this is membership database for an organization. Each member has a status assigned to them, like Active, expired, deceased, in default, etc. A member automatically moves from Active to Expired when the current date passes the date in a field (Duespaid). They automatically become "In default" 60 days later when the date passes the value in another field (Cardexpire). Member cards are dated 60 days after actual member expiry. The reason for this, is it enables the database to send out canned emails during the 60 day interim period. The status field is updated by Mysql each time the database is opened. This is part of what I am looking to get answered. Now you have a picture of what it is all about here is my problem, or lack of.

I have a table of stats on my switchboard page . You can see what this looks like at this link:

http://www.natcoa.com/images/switchboard.jpg

These are generated in text boxes by a query using the DSum & IIF functions. I use, for example:

=DSum("Active","qrystats"), etc

in the Control source of one of the text boxes. ("Active" is one of the possible entries in the Status field)

The query "qrystats" contains entries for each possible value of the status field - Active: Sum(IIf([status]='Active',1,0))




No problem there. They are all based on the value of "status" in "tblmembers". Its value is determiend by the code I have reproduced at the end of this post and that code is run as soon as the database is opened (Switchboard open is where the code is placed). If I work on the database, it does not update the values shown on the Switchboard unless I close it down & reload, or toggle the switchboard between form & design view. To solve this I put a button on the page, labeled it "refresh" and pointed it to the same code. It seems to work, I think.

I am not sure I am doing this correct or in the most efficient manner. At one time I tried to refresh both the page & query in VB, and the routine stopped working all together and nothing I did, would make it work again even though the code looked the same. I fortunately back up frequently while playing around.

Anyway here is the code. I am not 100% sure I have all the logic worked out, that does not matter I can tweak that, but you should be able to see what I am doing. I tried only one DoCmdRun SQL statement at the very end, instead of doing it 5 times, but it did not seem to work proper.

Dim SQL As String


SQL = "UPDATE tblmembers SET status='Expired'" _
& " WHERE Duespaid<Date() AND status='active'"

DoCmd.RunSQL SQL

SQL = "UPDATE tblmembers SET status='In Default'" _
& " WHERE Cardexpiry<Date() AND status='Expired'"

DoCmd.RunSQL SQL

SQL = "UPDATE tblmembers SET status='In Default'" _
& " WHERE (Duespaid + 60)<Date() AND status='Pending'"

DoCmd.RunSQL SQL


SQL = "UPDATE tblmembers SET status='Active'" _
& " WHERE Duespaid>Date() And Status='Pending'"

DoCmd.RunSQL SQL

SQL = "UPDATE tblmembers SET status='Remove'" _
& " WHERE (Cardexpiry+60)<Date() AND status='In Default'"


DoCmd.RunSQL SQL

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com