NEED CODE TO POP UP MESSAGE BASED ON CRITERIA IN TABLES

[avayaman]

I apologize for the double post I accidentally posted this in the wrong section.

Here is the situation, I have a database of records. One field is a date field containing the date the record was last changed or established.

There is another field of drop down choices.

What I want to do is run a query on the database such that if the date is 120 days past the date entered in the date field, and the value in the other field is "pending" a pop up message will appear. I know how to create the message. It the sql query i do not know how to program.

The table name is "maintable" the date field is "payrecord" & the field which I am looking for "pending" inside is called "status"

This is a club membership database. the purpose of this is to create a pop up alert when the database is opened if it is 120 days past when someone signed up or renewed and said they were going to send a check. People in that position are tagged as "pending". There is already a button to list them, this is just a reminder to go look at it if anyone in there is over 120 days in arrears.

Paul Beddows

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

 

[dhookom]

I expect you should create a query that returns the records you want to display. Then build a form based on the query and open it when the application starts up.

Duane
Hook'D on Access
MS Access MVP

 

[avayaman]

That is what I am doing, but i don't want the form to display, just an alert. I suspect the dLookup function is what I need to play with. I created a query temptest to separate the records in question.

I have been playing with this, but its wrong

Private Sub Report_Open(Cancel As Integer)
Dim varX As Variant
varX = DLookup("[payrecord]", "temptest", "[payrecord.value < Now - 120]")
If varX = 0 Then GoTo line2


line1: msgbox "Bleep"

line2:



End Sub

Paul Beddows

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

 

[avayaman]

Soory that shoudl be

Private Sub Report_Open(Cancel As Integer)
Dim varX As Variant
varX = DLookup("[payrecord]", "temptest", "[payrecord] < Now - 20")
If varX > 0 Then GoTo line2


line1: msgbox "Bleep"

line2:



End Sub

Paul Beddows

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

 

[avayaman]

Ok I got it working using a DSUM expression

Paul Beddows

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

 

[TheAceMan1]

How are ya avayaman . . .

[blue]DLoopUp[/blue] will return [payrecord] for the 1st record it finds (bear in mind you didn't include [blue]Status[/blue]) that meets your criteria ... [blue]what if there are others?[/blue]

I'm in total agreement with [blue]dhookom[/blue] on this form thing, [purple]espcially since this schema apparently bears priority of some special significance[/purple]. A form showing all those > 120 days would be an indication of the magnitude of this priority (a field of [blue]Oldest Pending[/blue] would allow handling in a better priority order). Further more, selecting a record from this form would take you to the mainform or any setup you devise to handle late payments. I understand how nice it would be to have [blue]non-pending[/blue]. After all those who pay ontime simply run thru the db. Its always the stragglers that keep your job alive!

I only ask that you give this more thought ... what is it you really need to spend your time with? ... those who pay on time ... or [red]those who don't![/red]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[avayaman]

I actually did it using dcount

Dim abc
Dim efg

efg = 0
abc = 0





abc = DCount("[payrecord]", "autoexec1", "[payrecord] < Now - 120")
If abc <> 0 Then GoTo line1
GoTo line2

line1:

If msgbox("THERE ARE ALERTS. View Now?", vbYesNo, "ALERT") = vbYes Then
efg = 1

msgbox "ALERT !! There are people who have indicated they will mail a check, but have not done so for 120 days. Check the Pending & Pending Renewals. Any over 120 days old, you should manually archive them. They can always be restored if the money arrives simply by changing them to active. Check with the accountant first to see if he has a check & has forgotten to notify you. You might want to email them first & remind them."
Else

GoTo line100
End If
line2: abc = 0

abc = DCount("[Duespaid]", "autoexec2", "[Duespaid] < Now")
If abc <> 0 Then GoTo line3
GoTo line4
line3:
If efg = 1 Then GoTo line21
If msgbox("THERE ARE ALERTS. View Now?", vbYesNo, "ALERT") = vbYes Then
efg = 1
line21:

msgbox "ALERT !! There are Dues Exempt members who have expired, press the Dues Exempt Members button & see if they should be renewed as exempt for another year or if they should revert to a normal member. If not sure, check with Paul or the accountant. There is usually a note as to why they are exempt in the comments box of their member card. Dues exempt members are usually performing duties for NATCOA, or are deployed in the military overseas. Some may have won a membership or got a year for 2 referrals. Peopel in here, may also be the free membership included with a corporate membership, you can tell by the letter in front of their member number. If so synchronize them with their companies expiry. Add years if necessary. MAKE SURE YOU RENEW ANY THAT ARE STAYING WAIVED AND TAG THEM FOR A CARD. IF THEY ARE REVERTING TO REGULAR MEMBERS, TICK THE 'SEND NOTICE' BOX SO THEY GET A REMINDER NOICE."
Else
GoTo line100

End If

line4: abc = 0
abc = DCount("[MemberNumber]", "qrystatsremove")
If abc <> 0 Then GoTo line5
GoTo line6
line5:
If efg = 1 Then GoTo line31
If msgbox("THERE ARE ALERTS. View Now?", vbYesNo, "ALERT") = vbYes Then
efg = 1
line31:

msgbox "ALERT !! There are members in default who require archiving and/or 'Do not renews' that require archiving. Press the 'Archive' button next to each"
Else
GoTo line100

End If
line6: abc = 0

abc = DCount("[MemberNumber]", "forumstatusarchived")
If abc <> 0 Then GoTo line7
GoTo line8
line7:

If efg = 1 Then GoTo line41
If msgbox("THERE ARE ALERTS. View Now?", vbYesNo, "ALERT") = vbYes Then
efg = 1
line41:

msgbox "ALERT !! There are archived members who require downgrading on the forum"
Else
GoTo line100

End If
line8: abc = 0

abc = DCount("[payrecord]", "PlatesNotSent")
If abc > 9 Then GoTo line9
GoTo line10
line9:

If efg = 1 Then GoTo line51
If msgbox("THERE ARE ALERTS. View Now?", vbYesNo, "ALERT") = vbYes Then
efg = 1
line51:
msgbox "ALERT !! There are more than 10 license plates needing to be sent. You should print out a report & mail it to the person responsible for that"
Else
GoTo line100
End If

line10: abc = 0


abc = DCount("[payrecord]", "AccidentallyArchived")
If abc <> 0 Then GoTo line11
GoTo line12
line11:

If efg = 1 Then GoTo Line61
If msgbox("THERE ARE ALERTS. View Now?", vbYesNo, "ALERT") = vbYes Then
efg = 1
Line61:
msgbox "ALERT !! It looks like there may be an active member who has been accidentally archived. Go to the reports page & select 'Accidentally Archived' at the bottom center & see who it is."
Else
GoTo line100
End If

line12:
line100: efg = 0
End Sub


Paul Beddows

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

 

[kjv1611]

I third the concern on using a query over DCOUNT. Besides the fact that you're not going to return all the possible data with DCOUNT, there is also more of a performance hit with DCOUNT vs a standard query.

That query could then be used as a record source for a report, another form for handing delinquent-only accounts, etc.

 

[avayaman]

Probably not a big issue, there are never more than 20 entries in question in any of those fields.

Paul Beddows

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

 

[kjv1611]

I think it'd be in your best interest long-term to seek out some other opinions in your own organization if possible. I know I wish that some folks who built databases that I have since inherited had sought out additional advice - either in the company or out.

If it can be done with a query, you're far better off in multiple ways. Even if a small record count, it's still somewhat of a performance factor. The DCOUNT, DSUM, etc, functions use more resources than you'd imagine for what they do. I've seen some huge differences on relatively small recordcounts.

But the main issue was mentioned by the others - the data your'e returning. You should be concerned with WHAT is delinquent, NOT just a count of them. Knowing 20 people did not pay is meaningless unless you know who they are. And if you build the query to tell you who they are, you can easily get the count off of that query... and still use probably less, and definitely not more, resources than the dcount.

 

[avayaman]

There are reports to show that. The database is very complex & highly automated. It even prints out member cards. It covers a lot of what-ifs. I was looking for a way to pop up a series of alerts when it starts up, to bring attention to tasks that need to be looked at. It uses an autoexec macro.

For example:

We accept both PayPal & checks. When someone signs up on line and indicates they are paying by check, they go into the database via a series of prompts and are placed in one of 2 status states, "Pending' or "Pending Renewal". The Operator needs to be alerted when one or more of those has exceeded 120 days, so we can pester them. After a check is received the status changes to "Active". There is a union query to show both "Pendings & Pending Renewals" and an associated report. It woudl be easy enough to create another query to show just those entries in there that are older than 120 days and just look at that query.

The alerts do not need to bring up the reports, simply alert that they need attention soon.

Renewing members or those who initially sign up for 2 years or more, get sent a license plate with a club logo. There is another individual who takes care of that function. She needs to be sent a list when there are more than 10 waiting. Dcount obviously has to be used for that one.

There is a 3rd party bulk mailer attached to the database. They are sent out about once a month to people who are expiring next month, expired, 30-60 days expired & 60-120 days expired. After that, they go into an archive. Once they are sent a mail in any of those categories they are tagged so they don't get multiple ones until they degenerate into the next category. In other words they could get an email in each of the 4 categories. Most tell us to get lost after 2 emails.

The date of the last bulk mail is recorded. One alert needs to pop up on boot up if no bulk mail in any of the 4 categories has been sent out for more than 28 days (The shortest month). The bulk mailer looks at the database and does not send a bulk mail for any of the 4 categories that are empty or to any individuals in it have already received one under that classification.

There is a forum associated with this database, only paid members have access to a large part of it. Members Handles on that forum are in the database. Once they get archived there is a report showing those who are registered on that forum so they can be downgraded by the admin there. An alert needs to pop up if there are entries in that report.

There are some other conditions.

DCount is obviously needed for 1, maybe 2 alerts, but a routine to detect if there are ANY entries present in some of the queries would suffice for many of them. If someone can show me how to code that, it would be better. I am sure it is simple but I seem to have a mental block about it.

So let me set some theoretical parameters on a sample:

Table is called "table1"

Query is called "query1"

"MemberNumber" is one category in that query

(or one I can add. Probably best if it is a numerical one since the DCount routines are looking at numbers & I don't want to run up against that error 2001. Easy enough to add a numerical field to a query even if it normally doesn't do anything.)

I need a simple routine to look at the query and if there are any entries present, bring up a MsgBox without using Dcount.


Also I have never used an autoexec macro before. The only way I could get it to work with a VB coded routine was to create a dummy report and have that dummy report execute the function "on open". The macro then closes it. There has to be another easier way. I know how to do it in Excel, but Access is another (strange) animal.


Paul Beddows

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