Loop Help

[lakers8175]

I have a table that has Division number then about 3, 4 or 5 nulls under it until the next divsion number. I want to take the division number and put it where the nulls are until the next division number. I want to continue this untill the last record of the table. Here is an example..
100 Div
null
null
null
200 Div
null
null
null
null
I would like it to look like
100 Div
100 Div
100 Div
100 Div
200 Div
200 Div
200 Div
200 Div
200 Div

Any help would be appreciated...
Sean

 

[Junior1544]

hmm... let's see if i can do this...

This code is off the top of my head so debug it befor using it...



Public Function ChangeDept() As Boolean
On Error GoTo err_ChangeDept
ChangeDept = False
dim rs as dao.recordset
dim strDept as string


Set rs = CurrentDb.OpenRecordset("SELECT table.* FROM table;&quot

rs.MoveFirst
rs.edit
strDept = rs!div
rs.movenext

Do While Not rs.EOF
if isnull(rs!div) then
rs!div = strdept
else
strDept = rs!div
end if
rs.update
rs.MoveNext
Loop

ChangeDept = True
msgbox ("The full change was done.&quot
Err_RunQuery_Click:
if ChangeDept = false
MsgBox err
end if

End Function

Ok, Now i was just thinking... I hope this is a one time thing, because with a normalized database, it doesn't matter what order or where in a table the records are... if this is a regularly done thing, I would suggest re-thinking your normalization setup...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[lakers8175]

James, I know a little bit of VB here's what I'm tyring to run

Public Function ChangeDept() As Boolean

ChangeDept = False
Dim rs As DAO.Recordset
Dim strDept As String


Set rs = CurrentDb.OpenRecordset("SELECT Div FROM tblImport;&quot

rs.MoveFirst
rs.Edit
strDept = rs!Div
rs.MoveNext

Do While Not rs.EOF
If IsNull(rs!Div) Then
rs!Div = strDept
Else
strDept = rs!Div
End If
rs.Update
rs.MoveNext
Loop

ChangeDept = True
MsgBox ("The full change was done.&quot
Err_RunQuery_Click:



End Function


My table name is tblImport and the field name is Div. I'm getting an "Invalid use of Null" on the "strDept = rs!Div"
step.

 

[Junior1544]

Make sure that the first record has some thing in the field name Div.
The select statment needs to stay with the star there... here's how i think you should have it...

You also took out the on error... I alwas suggest keeping that in there because it will just stop when it finds an error...

It looks like to me that you were not defining the recordset correctly... Try it now...

--James



Public Function ChangeDept() As Boolean

On Error GoTo err_ChangeDept

ChangeDept = False
Dim rs As DAO.Recordset
Dim strDept As String


Set rs = CurrentDb.OpenRecordset("SELECT tblImport.* FROM tblImport;&quot

rs.MoveFirst
rs.Edit
strDept = rs!Div
rs.MoveNext

Do While Not rs.EOF
If IsNull(rs!Div) Then
rs!Div = strDept
Else
strDept = rs!Div
End If
rs.Update
rs.MoveNext
Loop

ChangeDept = True
MsgBox ("The full change was done.&quot
Err_RunQuery_Click:

msgbox ("There was an error. The error number is: " & err)


End Function
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[lakers8175]

I got a little further, now I'm getting a Run Time Error.
"Update or CancelUpdate without addnew or edit" It's on the "if IsNull(rs!Div) Then
rs!Div = strDept"
Step

 

[Junior1544]

hmm... try this inside of that if/end if

if IsNull(rs!Div) Then
rs.edit
rs!Div = strDept
rs.update
end if

and you can take out that rs.edit higher in the code...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[lakers8175]

Thanks for your patience, it worked perfect....

Sean