Set myRS = myDB.OpenRecordset(strSQL, dbOpenTable)

[ckib]

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)

 

[Norie]

Where are you setting myDB?

 

[ckib]

setting mydb

I'm setting mydb at the start of the procedure like this:


Dim myDB As Database
Set myDB = CurrentDb()

The error that I get says The Microsoft database jet engine could not find the object 'SELECT ....' . Make sure the object exists ....

 

[Norie]

Why are you using dbOpenTable in the OpenRecordset?

Perhaps it's causing VBA to look for a table with the same name as strSQL.

 

[ckib]

dbopentable

Removed the dbOpenTable ... now I get Too few parameters. Expected 2.

I've seen that error before and it was caused by incorrect quotes ... I'll check.

 

[ckib]

hit report by mistake!

Not even sure what that did.

I just tried this:

strSQL = "SELECT * FROM tblSegment " & _
"WHERE (((tblSegment.projectID)='" & strProjectID & "'));"

Received:

Too few parameters. Expected 1.

Any more ideas? Thanks.

 

[Norie]

Have you double checked all the field and table names?

 

[ckib]

bad field name in SQL string

Yup, incorrect field name reference. Thank you!!!!!!!!

BTW, by accident I hit the report button on one of your messages. Not sure what that did, hopefully nothing bad!

 

[Norie]

Well it reports me to the moderators.

But I'm sure they'll check the thread.