I can't get the OpenRecordset to work. First I tried setting the source to the name of my table, then tried a SQL string, but still no success. See code blow. Any help would be appreciated.
' ***************** 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)
' ***************** 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)