Next Record Not setting values

[wchernock]

I have the following code:

DoCmd.OpenTable "C101-Raw", acViewNormal, acEdit
DoCmd.GoToRecord acDataTable, "C101-Raw", acFirst
Do
BIOChg = Me.BIO
Call FullNameSplit(BIOChg)
' Set BIO value to value returned from the function call
BIO = BIOChg
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNext
' This was the other cmd I have tried
' DoCmd.GoToRecord acTable, "C101-Raw", acNext
MsgBox "Next BIO Value= " & Me.BIO
MsgBox "Current ClarityID Value= " & Me.PrjID

Counter = Counter + 1
If Counter > 4 Then Exit Do

Loop

The go to next record is executed , but the value for Me.BIO and Me.PrjID still displays the value for the first record. How do I get the values to update if the next record has been executed?

 

[WayneRyan]

w,

You aren't using the value returned by the function FullNameSplit.

DoCmd.OpenTable "C101-Raw", acViewNormal, acEdit
DoCmd.GoToRecord acDataTable, "C101-Raw", acFirst
Do
   BIOChg = Me.BIO
   Call FullNameSplit(BIOChg)
   ' Set BIO value to value returned from the function call
   BIO = BIOChg <-- BIO does not get Changed
                <-- You're not using anything returned by FullNameSplit!
                <-- Essentially you're doing: BIOChg = Me.BIO   Then: BIO = BIOChg
                <-- I think you can replace ALL of the lines so far with
                <-- BIO = FullNameSplit(Bio)
   DoCmd.RunCommand acCmdSaveRecord
   DoCmd.RunCommand acCmdRecordsGoToNext
   ' This was the other cmd I have tried
   ' DoCmd.GoToRecord acTable, "C101-Raw", acNext
   MsgBox "Next BIO Value= " & Me.BIO
   MsgBox "Current ClarityID Value= " & Me.PrjID

   Counter = Counter + 1
   If Counter > 4 Then Exit Do
   Loop

However, rather than manipulating a form/recordset, just use an update query:

Update C101-Raw
Set Bio = FullNameSplit(Bio)

hth,
Wayne

 

[WayneRyan]

w,

Forgot to add:

Is it possible to post the function FullNameSplit?

Is it a function or a Sub?

Wayne

 

[wchernock]

The function call works fine. The general logic is to get record #1, pass the BIO field to the function. The function splits the field then performs a lookup and passes back a new value. That value is then replaces the original BIO value and saves the record. (So far this all works fine). Now I want to go to the next record and repeat the process. The problem is that after executing the "goto next record" the value in the BIO field does not change to the value in record #2. I put in a bunch of msgbox statements and can verify that the value remains the value from the saved record #1.


I did originallly try the updatequery with no success so to do the same thing with the code I am troubleshooting. If I could get the nextrecord to work, I'd have the problem solved.

 

[wchernock]

Here is what I tried as an update query:

UPDATE [C101-Raw] SET [C101-Raw].BIO = FullNameSplit([C101-Raw]![BIO]);


Here is the function call:

Public Function FullNameSplit(FullName)
Dim LN As String
Dim FN As String
Dim LID As String
Dim stLinkCriteria As String
' MsgBox "Input Value = " & FullName, vbOKOnly
LN = Trim(Left(FullName, InStr(FullName, ",") - 1))
FN = Trim(Right(FullName, Len(FullName) - InStr(FullName, ",") - 1))
' MsgBox "LN FN = " & LN & " " & FN, vbOKOnly
stLinkCriteria = "[LastName] ='" & LN & "' and " & "[FirstName] ='" & FN & "'"
LID = DLookup("[XID]", "Tbl-Employee", stLinkCriteria)
FullName = LID
End Function


If I call the function from a command button, it does return the correct value
when I use it in the query, I am getting a nullvalue error for the LID line. When I put in the msgboxes a value does get passes in and parsed into FN and LN.

Ideas?

 

[WayneRyan]

W,

First, your FullNameSplit function does not return anything.

It should have a type, like:

Public Function FullNameSplit(FullName) As String

Then, at the end of the function it should have a line like:

FullNameSplit = LID

Secondly, if the DLookUp can't find a record in Tbl-Employee,
it will return a Null value. You can't assign that to LID.
You need to wrap the DLookUp in a Nz function:

LID = Nz(DLookUp(...), "Invalid user")

Third, where does the function reside? For a query to use it,
it has to be in a Public Module (modules tab on database window).
It should not be on your form.

I'd suggest that you put the function in a Public module, give it
a string type, and assign "FullNameSplit = LID" before the function
exits. Then, retry your update query.

hth,
Wayne