Results 1 to 3 of 3

Thread: Reminder (A2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reminder (A2K)

    Good Morning,

    My database houses survey data. Within it is a table, t_Responses which has:
    <UL><LI>pkResponseID
    <LI>fkDemoID
    <LI>fkLOBID
    <LI>dSurveyDate
    <LI>nSurveyRecord
    <LI>fkUserID
    <LI>ReminderComplete[/list]A survey is conducted on Admission of member and again on discharge, so there would be a set of survey's (2 survey's to make the process complete). A member can have 20 surevey's as they are given the survey's each time they are admitted to the hospital. For instance member 1234567889:
    <UL><LI>Admits on 1/1/06---SurveyRecord1
    <LI>Discharges on 1/18/06--SurveyRecord2
    <LI>Admits on 2/1/06--SurveyRecord3
    <LI>Discharges on 2/18/06--SurveyRecord4[/list]It's only when the SurveyRecord value is "EVEN" is the process complete.

    If the SurveyRecord is "ODD", the process isn't complete and I want to find a method to reminder the Case Mgr. that a discharge survey requires a "FOLLOW UP". There isn't a required time because discharge is always unknown. So I've came up with the following but not sure how to incorporate the SurveyRecord issue in and was hoping someone could fill me in.

    <pre>Private Sub Form_Close()
    Dim sReminder As String, sDemo As String
    Dim sStaff As String, sSQL As String

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    sSQL = "SELECT * FROM t_Response _

    WHERE fkUserID = " & GetUser() & " And "_
    & "[ReminderComplete] = False "

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    If rst.EOF Then GoTo ExitHandler

    Do While Not rst.EOF
    sDemo= rst!fkDemoID ' This is the members Demographic ID

    sReminder = sReminder & vbCrLf & "a 2nd Survey for" & sDemo & _
    " is required"

    rst.MoveNext

    Loop

    sReminder = "Please note the following:" _
    & sReminder & vbCrLf & "Show report?"

    If MsgBox(sReminder, vbQuestion + vbYesNo) = vbYes Then
    DoCmd.OpenReport "rSurveysDue", acViewPreview
    End If

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub
    </pre>

    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reminder (A2K)

    Try
    <code>
    sSQL = "SELECT * FROM t_Response " & _
    "WHERE fkUserID = " & GetUser() & " And " & _
    "ReminderComplete = False And " & _
    "nSurveyRecord Mod 2 = 1"
    </code>
    nSurveyRecord Mod 2 returns the remainder of nSurveyRecord after division by 2; this is 0 if nSurveyRecord is even and 1 if it is odd.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reminder (A2K)

    Worked beautifully Hans....Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •