asked on

VBA Compile Error - Data Member not found

I'm converting an app to Access 2010 and getting a compile error: data member not found. Following is the code.

Public Function ServiceProcessSet(UserID, ServiceID, ProcessType) As Boolean
Dim tmpUserID As Variant
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM sysUsers INNER JOIN tblContact ON sysUsers.UserID = tblContact.ContactID WHERE ServiceID = " & ServiceID)
If rs.RecordCount > 0 Then
rs.Edit
rs!ProcessType = ProcessType
rs.Update
Else
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO SysUsers(WSID,UserID,ServiceID,DateStart,ProcessType) SELECT '" & GetWSID() & "' as WID," & UserID & " as UID, " & ServiceID & " AS SID, '" & Now & "' AS start,'" & ProcessType & "'")
DoCmd.SetWarnings True
End If
End Function

The error happens on the rs.edit statement. Does this mean that the frontend needs to have the backend tables linked before the compiled? The table referenced, SysUsers, is a linked table that is shared among all the apps on various workstations. This routine is run during startup.

Or is there something else wrong?

Thanks for your help.

Judith

Rey Obrero (Capricorn1)

try changing the declaration of these two

Dim db As Database
Dim rs As Recordset

with

Dim db As DAO.Database
Dim rs As DAO.Recordset

JudithARyan

ASKER

Ray, It works! However, I have a lot of those statements in my application. Is there a way to edit a string in multiple VBA routines/multiple forms at once? Or am I a dreamer?

Judith

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

and one more thing, it is a better practice to add Option Explicit to the top of all your modules

see this tutorial from Jim dettman

https://www.experts-exchange.com/videos/536/MS-Access-Writing-Solid-VBA-Code.html

JudithARyan

ASKER

Thank you so much, you've just made my job a lot easier!

Judith