Set myRS = myDB.OpenRecordset(strSQL, dbOpenTable)

ckib

Board Regular
Joined
Sep 14, 2005
Messages
219
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)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Where are you setting myDB?
 

ckib

Board Regular
Joined
Sep 14, 2005
Messages
219
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 14, 2005
Messages
219

ADVERTISEMENT

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

Board Regular
Joined
Sep 14, 2005
Messages
219
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you double checked all the field and table names?
 

ckib

Board Regular
Joined
Sep 14, 2005
Messages
219
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-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Well it reports me to the moderators.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,631
Messages
5,597,282
Members
414,134
Latest member
Snowfall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top