Do Until (A2K)

[RNCIENG]

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

[johnhutchison]

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?

[RNCIENG]

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.

[johnhutchison]

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?

[johnhutchison]

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.

[RNCIENG]

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.