Link to home
Avatar of j e
j e

asked on

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,
je

    Dim rsrec As Recordset
    Set rsrec = CurrentDb.OpenRecordset("tbl_Users_name_issues")
   
    rsrec.MoveFirst
   
    Do While Not rsrec.EOF
       
        With rsrec
     
            rsrec.Edit
     
            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

            rsrec.Update
       
        End With
       
        rsrec.MoveNext
       
    Loop
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Rgonzo1971
Rgonzo1971

Hi,

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.

Regards
BTW, you might want to take 5 minutes and review:

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

 for a couple more tips on writing good code.

Jim.
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)
    Next
    strLName = Trim(strLName)
endif