myRS.Index ... cannot get this to work!

[ckib]

Hello friends,

My latest problem ... I get the error:

Run-time error '3800':

'contactID' is not an index in this table.

Here is my code:

Dim myDB As Database
Dim myRS As Recordset

Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset("tblContact", dbOpenTable)

myRS.Index = "contactID"
myRS.Seek "=", Me.rs_contactID
myRS.Edit
myRS!cont_last_contacted = Date
myRS.Update

myRS.Close

contactID is the primary key for tblContact. What is the the trick to getting this to work! THANKS to anyone who can help me.

 

[mdmilner]

Try explictly declaring the database & recordset objects as DAO
and make sure you have the reference listed.

Dim myDB As DAO.Database
Dim myRS As DAO.Recordset


Mike

 

[ckib]

dao.recordset

I explicitly declared the database and recordset objects as DAO as you suggested and it had no effect. I'm not sure what you mean by "make sure you have the reference listed" ??? Could you please explain? Thanks.

 

[mdmilner]

You need to have the Microsoft DAO 3.x (3.6 is office2k) Object Library listed under references. From inside any code module, go to Tools-References and find it on the list.

What happens is, Access uses the first reference it finds on the list. By Default this is ADO - and your syntax is incorrect for ADO. Explictly declaring DAO forces it to use Data Access Objects.

When I don't have the DAO reference, for example, VBA crashes just establishing the recordset for me.

I do have a question about what you are doing though.
Seeing the line:

myRS.Seek "=", Me.rs_contactID

makes me think you have a form based on the table and/or you're using a text box to tell you what index value to search for and then update that one field.

I think you may be doing something more complex than necessary.

Instead of this try this:

Dim myDB As DAO.Database
Dim strSQL As String

Set myDB = CurrentDb()
strSQL = "UPDATE tblContact SET cont_last_contacted =#"
strSQL = strSQL & Date & "# "
strSQL = strSQL & "WHERE contactID=" & Me.rs_contactID
DoCmd.RunSQL strSQL

Set myDB=Nothing

What this does is bypass the need to use recordsets at all.
The parameter (third line) restricts the update to only the properly indexed record.

Mike

 

[ckib]

using SQL

Mike,

That is exactly what I was doing in my code because it works, but I thought using a recordset was "more correct". See code below. My only problem is that when I use DoCmd.RunSQL it generates a message box for confirmation of the updates. Is there a way to override this behavior? Thanks!

Dim myDB As Database
Dim myUpdQuery As QueryDef
Dim strSQL As String
Dim lngContactID As Long

Set myDB = CurrentDb

lngContactID = Me.rs_contactID

' define SQL statement for action query

strSQL = "UPDATE tblContact SET cont_last_contacted = #" & Date & _
"# WHERE (((tblContact.ContactID)=" & lngContactID & "));"

'DoCmd.RunSQL (strSQL) tried this but generates msgbox confirmation box!

Set myUpdQuery = myDB.CreateQueryDef("", strSQL) ' create temp update query

' run temporary QueryDef

myUpdQuery.Execute
myUpdQuery.Close

 

[mdmilner]

Yes, it's under Tools-Options-Edit -- turn off the three check boxes on the right that ask for confirmation of all types of changes to tables.

Mike

 

[ckib]

msgbox confirmation

Thank you, I'll try that!

 

[meloncolly]

I strongly advise you NOT to turn it off under "Tools-Options-Edit"

That will turn off everything - including messages you NEED to see.

Instead turn it on and off in code:

docmd.setwarnings False
'do something that will normally create a confirmation pop up from access
docmd.setwarnings true

 

[ckib]

confirmation messages

Great idea. Thank you.

BTW, for anyone following this thread. I do have the reference for Microsoft DAO 3.6 Object Library selected. So, that can't be the reason that myRS.Index doesn't work. The following code (using myRS.MoveFirst and myRS.MoveNext) works fine:

Dim myRS As Recordset

Dim intSegmentNum As Integer
Dim intSegSize As Integer
Dim theLoopCount As Integer
Dim lngRemainder As Long
Dim lngAppendRecs 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 = "Contacts updated for this project = " & lngRemainder
MsgBox (strText)

' make sure we appended the correct number of records to tblSegment

lngAppendRecs = DCount("[seg_projectID]", "tblSegment", "[seg_projectID]='" & strProjectID & "'")
strText = "Segment records appended for this project = " & lngAppendRecs
MsgBox (strText)

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

intSegmentNum = Me!proj_segments ' initial value = max segment number

theLoopCount = lngRemainder / intSegmentNum ' calculate number of contacts per segment
' VBA will round up - handled by last loop execution

intSegSize = theLoopCount ' save copy of segment size

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

Set myRS = myDB.OpenRecordset(strSQL)

myRS.MoveFirst

Do While intSegmentNum > 0 ' do for each segment
Do While theLoopCount > 0 ' do for each contact within this segment
myRS.Edit
myRS!seg_num = intSegmentNum
myRS.Update
theLoopCount = theLoopCount - 1
myRS.MoveNext
Loop

lngRemainder = lngRemainder - intSegSize ' contacts remaining after each segment loop
intSegmentNum = intSegmentNum - 1 ' decrement segment number for next loop
If intSegmentNum = 1 Then ' if next loop is last segment
theLoopCount = lngRemainder ' use remainder
Else
theLoopCount = intSegSize ' initialize loop counter for next segment
End If

Loop

myRS.Close