Do While

[Henley12]

Can you do a Do While IsNull(

![field])? Is there a way to make something like that work? I have a form that creates new work orders, but it doesn't populate the work order number field. I would like to loop through the table and insert a work order number if the field is null. Is this the correct way to do this?

 

[pbaldy]

You could run a loop like that on a recordset based on that table, or better yet a recordset based on an SQL statement that only pulled records where that field was null. Most efficient would be an UPDATE query.

 

[Henley12]

I think that gets me back to my original problem. I need something to update a work order number field sequentially beginning with the last work order number.

 

[RainLover]

You need to use the DMax Function.

Use DMax plus 1 on the before update event of the form.

Any problems and I will post back with an example.

 

[Henley12]

Here is the code I have on my form, but I have something wrong.

varNext = DMax("[WONum]", "tblMain") + 1
Do While IsNull([tblMain]![WONum])
With rstMain
.Edit
!WONum = varNext
End With
rstMain.Update
varNext = varNext + 1
Loop

 

[gemma-the-husky]

have a query that just finds the null records (as you arent bothered about the others)

then just have a code module to process them - heres a sub that will iterate the recordset

sub fixthem

dim dbs as database
dim rst as recordset

set dbs=currentdb
set rst = dbs.openrecordset(nullqueryname)

while not rst.eof
   rst.edit
   rst!nullfield = [B]whatevernextvalue[/B]
   rst.update
   rst.movenext
wend

rst.close
set rst=nothing
set dbs=nothing
end sub

right - this logic will do it, but you need ot decide how to get the approraite value to assign to the null field