' ***************** Begin Step 3 *******************

strText = "Step 3: Update tblSegment records for this project"

MsgBox (strText)

Dim myRS As Recordset

Dim intSegmentNum As Integer

Dim intSegsize As Integer

Dim theLoopCount As Integer

Dim lngRemainder As Long

' set initial value = total sample size

lngRemainder = DCount("[cont_projectID]", "tblContact", "[cont_projectID]='" & strProjectID & "'")

Me!proj_smpl_sz.Value = lngRemainder ' save sample size in project record

strText = "DCount returned " & lngRemainder

MsgBox (strText)

' now update tblSegment with the correct segment number for each contact

intSegmentNum = Me!proj_segments ' initial value = total number of segments

theLoopCount = lngRemainder / intSegmentNum ' calculate number of contacts per segment

' may have a remainder - handled by last loop execution

intSegsize = theLoopCount ' save copy of segment size

' Set myRS = myDB.OpenRecordset("tblSegment", dbOpenTable)

strSQL = "SELECT tblSegment.projectID,tblSegment.contactID,tblSegment.seg_num " & _

"FROM tblSegment WHERE (((tblSegment.projectID)='" & strProjectID & "'));"

MsgBox (strSQL)

Set myRS = myDB.OpenRecordset(strSQL, dbOpenTable)

' myRS.Index = "seg_projectID" ' set current index

' myRS.Seek "=", strProjectID

myRS.MoveFirst

Do While intSegmentNum > 0 ' do for each segment

strText = "begin updating tblSegment for segment number " & intSegmentNum

MsgBox (strText)

Do While theLoopCount > 0 ' do for each contact within this segment

' if the SQL works, all records in the set should have the correct project ID

' once that is working, take out the If logic and just update the segment number

If myRS!seg_projectID = strProjectID Then

myRS.Edit

myRS!seg_num = intSegmentNum

myRS.Update

theLoopCount = theLoopCount - 1

End If

myRS.MoveNext

Loop

strText = "update complete for segment number " & intSegmentNum

MsgBox (strText)

lngRemainder = lngRemainder - intSegsize ' contacts remaining after each segment loop

intSegmentNum = intSegmentNum - 1 ' will use this value to populate tblSegment records

If intSegmentNum = 1 Then ' if next loop is last segment

theLoopCount = lngRemainder ' pick up any remainder after divide operation

Else

theLoopCount = intSegsize ' initialize loop counter for next segment

End If

Loop

myRS.Close

strText = "Update Complete"

MsgBox (strText)