Need help with creating a small loop (1 Viewer)

[Bill Bisco]

Dear all,

I need a little assistance on creating a small loop to run code. This should be relatively simple, but I'm just not thinking of it properly. It should go something like this.

For each record from AREA with PLANTPROGRAMID = VARIABLE Then
(execute code here)
Next record

In this example, my table is called Area, my field is called plantprogramid, and my variable is defined earlier in code.

Any help would be greatly appreciated.

Thanks,

Bill

 

[pr2-eugin]

How about opening a Recordset and manipulating the data? I always go back to this site for information on Working with Recordsets.

 

[Bill Bisco]

Thanks for your reply. I looked up the link and created the following code, but I am still getting an error:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Area")
    With rs
        .FindFirst "PlantProgramID = " & iPlantProgram
    Do While rs.EOF = False
        CreateDefaultDepartments rs![AreaID]
        rs.MoveNext
    Loop
    End With
    
    rs.Close

The code fails at the bit

        .FindFirst "PlantProgramID = " & iPlantProgram

I get error 3251: Operation is not supported for this type of object.

I have tried changing DAO.recordset to just recordset but I still get the error. Is the code possibly failing because it cannot tell that "Area" is the name of a table? If so, how would I reference it properly?

Thanks,

Bill

 

[pr2-eugin]

Your code is almost correct.. I believe that CreateDefaultDepartments is an User Defined function/Sub?

You do not need to use FindFirst here. Use that in an If Check inside the Do While loop..

Dim rs As DAO.Recordset, dbObj As DAO.Database
Set dbObj = CurrentDb
Set rs = dbObj.OpenRecordset("Area")
Do While rs.EOF = False
   [COLOR=Blue][B] If rs!PlantProgramID = iPlantProgram [/B][/COLOR]Then CreateDefaultDepartments rs![AreaID]
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbObj = Nothing

 

[Bill Bisco]

Yes, CreateDefaultDepartments is the custom Sub that I am executing. Sorry for not mentioning that explicitly.

Your code works! Thank you! I knew this shouldn't be too difficult!

Sincerely,

Bill

 

[pr2-eugin]

Happy to have helped !

Glad you had a read through the article and came up with your own solution.

Good Luck !