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
JudithARyanTech SupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
JudithARyanTech SupportAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
you can use the Find and replace function of the VBA project

you can invoke it with Ctrl + F

select  Dim db As Database from the codes
and paste in the "Find what"  box  
click Replace and paste in the Replace With Box
Dim db As DAO.Database

then select the option Current project from the Search option box

do the same with the rs, but be careful to check all the replaced text.
- do it one at a time to make sure by clicking Replace then find Next
- don't be tempted to click the Replace ALL button

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
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


http://www.experts-exchange.com/videos/536/MS-Access-Writing-Solid-VBA-Code.html
JudithARyanTech SupportAuthor Commented:
Thank you so much, you've just made my job a lot easier!

Judith
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.