VBA Function Need help

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi All,
i am needing help with my code, I would like to update a
recordset fields, with information from the same recordset
field.

Field1
row1 "CUS01"
row2 "null"
row3 "CUS02"
row4 "null"

After looping
Field1
row1 "CUS01"
row2 "CUS01"
row3 "CUS02"
row4 "CUS02

Will anyone Help
Regards
Mark
 
Something like the following untested aircode should work. I've omitted the
creation of the recordset in the first place.

With rsCurr
If Not .EOF Then
If Not IsNull(!Field1) Then
txtPrevValue = !Field1
Else
' What do you want to do if Field1 is Null for the first row?
Endif
End If
.MoveNext
Do While Not .EOF
If IsNull(!Field1) Then
.Edit
!Field1 = txtPrevValue
.Update
Else
txtPrevValue = !Field1
End If
.MoveNext
Loop
End With

Note that I'm assuming DAO in the example above. If you're using ADO, omit
the .Edit line
 
Cheers for the help however, i ended up with the following.
rst.MoveFirst 'move to the first row
strIn = Trim(rst.Fields(0)) 'copies what is
in the field, to the strIn variable
strNum = Len(strIn) 'Converts the string
to a number
Select Case strNum
Case Is >= 1 ' if the number is
greater than 1 the continue with code
Start:
If rst.EOF Then GoTo handler ' if at the end of the
records, exit the function
Do
rst.Edit
rst.Fields(0) = strIn 'edits the field with
the strIn text
rst.Update
rst.MoveNext

strOut = rst.Fields(0) 'check's the next
field for any text
strNum = Len(strOut) 'Converts the string
to a number
If strNum >= 1 Then Exit Do 'If the field has no
text, then continues editing the fields untill it has
Loop Until strNum >= 5

strIn = Trim(rst.Fields(0)) 'copies what is
in the field, to the strIn variable
GoTo Start 'retuns back to the
begining of the loop,to edit with the new text
End Select
handler:
rst.MoveFirst 'move to the first row

Do While Not rst.EOF
strIn = Trim(rst.Fields(1)) 'copies what is
in the field, to the strIn variable
rst.Edit
rst.Fields(1) = strIn 'edits the field with
the strIn text
rst.Update
rst.MoveNext
Loop
 
Back
Top