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

Jim Dettman (EE MVE)

membership

This solution is only available to members.

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

Start Free Trial

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

Jim Dettman (EE MVE)

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.

Dale Fye

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