Error updating recordset

I'm trying to update a recordset and having some problems.  I managed to work through most -- but can't get past this one....  I keep getting "Invalid procedure call or argument" as the error message.  It looks like it is updating the first two records of the recordset and then stops.  Here is the code.....  what am I missing?

Thanks for your help,

    Dim rsrec As Recordset
    Set rsrec = CurrentDb.OpenRecordset("tbl_Users_name_issues")
    Do While Not rsrec.EOF
        With rsrec
            InstrVal = InStr(1, rsrec![Fullname], " ")
            InstrRevVal = Len(rsrec![Fullname]) - InStrRev(rsrec![Fullname], " ")
            'identify name parts
            stfname = Left(rsrec![Fullname], (InstrVal - 1))
            stmi = Mid(rsrec![Fullname], (InstrVal + 1), Len(rsrec![Fullname]) - (InstrVal + InstrRevVal + 1))
            stlname = Right(rsrec![Fullname], (InstrRevVal))
            'populated revised name parts
            ![Fname-revised] = stfname
            ![MI-revised] = stmi
            ![Lname-revised] = stlname

        End With
Jeanne ElmuccioAsked:
Who is Participating?

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

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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It's probably related to this:

 InstrVal = InStr(1, rsrec![Fullname], " ")

 You never verify that you actually have a value.  If no space in the name, a zero would be returned and other calls will fail as a result.

Couple other comments:

1. Make sure your explicit as possible when working in code.   For example, on your recordset, you don't say if it is DAO or ADO, both of which have recordset objects, your code will fail possibly in the future if you modify the references and start using both.

It should be:

 Dim rsrec as DAO.Recordset.

2. Same is true for rest of variables your using.   Put:

 Option Explicit

at the top of the module, then do a compile.  Anything your using will need to be explicitly dim'd:

Dim InstrVal as Integer

This is a good way to prevent mistakes.


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

at which line do you get the error?

Are all the records with a Fname MI LName Structure

if not you have to adapt your code for different posibilities
- No MI
- No First name, etc.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, you might want to take 5 minutes and review:

 for a couple more tips on writing good code.

Dale FyeOwner, Developing Solutions LLCCommented:
Splitting names can be problematic.  The way you have it, it assumes that there is a middle name (in some cases there might not be), and that the last name does not contain any spaces, but names like "Van Gogh" do.

You might want to consider the Split function to split your Fullname field into pieces based on the space character.  You can then add some logic to test for a single character MI and multiple "words" in the last name.  This is just a sample with some simple logic, but would need to be expanded to handle this process better.

Dim arName() as string
Dim intLoop as integer
arName = Split(rsRec![FullName], " ")
strfName = arName(0)
if ubound(arName) >= 1 then strMi = arName(1)
if ubound(arName) >=2 then
    strLName = ""
    for intLoop = 2 to ubound(arName)
        strLName = strLName & " " & arName(intLoop)
    strLName = Trim(strLName)
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.