Results 1 to 6 of 6

Thread: Do Until (A2K)

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

    Do Until (A2K)

    My recordset has several fields, SurveyRecord (numeric), SurveyReminder (yes/no ckbox) and fkMemberID (text)
    On my form I have a control, SurveyDate. I want to add code to SurveyDate's AfterUpdate event.

    My code should look to the specified recordset and compare the fkMemberID to the forms fkMemberID. Once it finds a match a match it needs see what number is in SurveyRecord. If SurveyRecord's number is 0 (an Even number) it needs to set SurveyReminder to True.

    I've spent several days trying to come up with the correct code to work but I can't seem to get it right. I'm hoping someone will take a look at it and give me some advice.

    <pre>Dim SurveyRecord
    Dim sSQL As String
    Dim nR As String
    Dim nReminder As Integer

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

    'Update Survey Record by 1
    SurveyRecord = Nz(DMax("SurveyRecord", "tblSrvRspns", "[fkMemberID] = '" & nDemoID & "'"), 0) + 1
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Do Until (A2K)

    I am not sure that I completely unbderstand, but what jumps out at me from your post is this line:

    If sSQL = 0 Then

    You have defined sSQL as a string, and set it to be a slab of sql, so there is no way it is ever going to be 0.

    So what are you trying to test for here?
    Regards
    John



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

    Re: Do Until (A2K)

    I don't doubt how I wrote the code is incorrect.....I will try to explain it as best as I can.

    tblSrvRspns has several fields, one of which is SurveyRecord (numeric). SurveyRecords stores the number of Surveys for a member. Each time a survey is done a value is assigned, if it's the first one for member 123456789, then SurveyRecord number would be 1, each time member 123456789 has a survey, that number is increased by 1. Each time a member is admited to the hospital, they get a survey, they get another survey on discharge. Therefore, 2 surveys have to be completed in order to end this part of case management. It takes 2 survey's make a set.

    tblSrvRspns also has another field, SurveyReminder (yes/no ckbox). Using SurveyRecord's values, I have code that provides a report to the the Case Mgr. he/she has Survey's that need to be completed. So if the value of SurveyRecord is an ODD #, the case manager will get a reminder because the SET isn't complete. However, if SurveyRecord value is an EVEN #, then the set IS COMPLETE. If the set is complete, I need to set SurveyReminder to -1 so the other code won't populate the report with that member.

    Example:

    Record 1
    Member ID: 123456789
    SurveyRecord: 1
    SurveyReminder: 0 *This record would appear on the report because it needs the follow up survey

    Record 2
    MemberID: 123456789
    SurveyRecord: 2
    SurveyReminder: 0 *but needs to be -1 because SurveyRecord value is an even # the set is complete, therefore should be omitted from the Reminder report the code should set SurveyReminder to "-1"

    Record 3
    MemberID: 123456789
    SurveyRecord: 3
    SurveyReminder: 0 *This record needs to appear on the report because the Set isn't complete

    The purpose of the code I'm having problems with is: It needs to look at tblSrvRspns.....check the memberID and check that member's SurveyRecord #, if Even, then set SurveyReminder to -1.

    I thought using the "MOD" part of the code would evaluate the SurveyRecord....I use that in the other code for Reminders. It divides SurveyRecord by 2 to return either a 1 or 0. Is this where I'm wrong?

    Thank you very much for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Do Until (A2K)

    You don't need the field SurveyRecord at all as you can determine the number of surverys for each member by just counting them.

    Dim lngcount as long
    lngcount = dcount("tblSrvRspns.*", "tblSrvRspns", "tblSrvRspns.[fkMemberID] = '" & nDemoID & "'")

    (I don't know what nDemoID is, but you used it in the first post - I am assuming it holds the MemberID - if that is wrong, replace it with something that does.)

    if lngcount mod 2 = 1 then
    me.SurveyReminder = true
    else
    me.SurveyReminder = false
    end if

    The use of me assumes that SurveyReminder is a control on the current form.

    SurveyReminder is a field in tblSrvRspns. When the follow up survey is actually completed, you then need to go back to the previous survey and set Survey Reminder back to false, as it is not needed any more. Would it be easier if SurveyReminder was a field in the members table, so there is only one value for it for each member?
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Do Until (A2K)

    n further reflection, I don't think you need the field Survery Reminder at all. When you come to produce the report for the Case Managers, just count the number of surveys for each patient. If the count is odd (worked out using mod) then include them in the report, if the count is even, don't.
    Regards
    John



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

    Re: Do Until (A2K)

    Thank you so much for the advice....you have no idea how much I appreciate it.

    Your solution works perfectly. I had to make changes to the code (DCount syntax wasn't working correctly) but I got it working and is listed below should anyone ever need it.

    <pre>Dim lngcount As Long

    lngcount = DCount("[fkMemberID]", "tblSrvRspns", _
    "[fkMemberID]= '" & nDemoID & "'")

    If lngcount Mod 2 = 0 Then
    Me.ReminderComplete = True
    Else
    Me.ReminderComplete = False
    End If</pre>


    And I will use the same code (changing Mod 2=1) for the purpose of my reminders.

    Have a great day....and again, thank you.
    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
  •