Database problem... need help

[Inhumanoid]

I'm a database newby and I need a little help

I have an access database with the following fields:

ID, ParentID, Level

Level and ParentID are filled with numbers Level is filled with zero's.

I want to have a loop that looks at every record an checks if the ID of that record matches a ParentID in any of te other records if this is the case the Level field of that record should be set to 1

Can anybody help me with this please ?

[Inhumanoid]

Did som studying :-)

Here's my solution in case you would hate me for not sharing...



Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim iValue As Integer
Dim iCounter As Integer

'open database
Set db = DBEngine.OpenDatabase(App.Path & "\4_new.mdb")
'open recordset using SQL
Set rs = db.OpenRecordset("Select * from Menu")
Set rs2 = db.OpenRecordset("Select * from Menu")


i = 1
Do Until rs.EOF = True
i = i + 1
'rs.Fields("Name").Valu
iValue = rs.Fields("ID").Value
Do Until rs2.EOF = True
If rs2.Fields("ParentID").Value = iValue Then
rs.Edit
rs.Fields("submenu").Value = 1
rs.Update
End If
rs2.MoveNext
DoEvents
Loop
rs2.MoveFirst
rs.MoveNext
Label1.Caption = i
DoEvents
Loop





'close db/rs
rs.Close
rs2.Close
db.Close
Set rs = Nothing
Set db = Nothing
Label1.Caption = "Done..."



[This message has been edited by Inhumanoid (edited 01-20-2000).]

[peteritv]

Why all the hard coding stuff?

Execute this SQL query:
"UPDATE Menu INNER JOIN Menu AS Menu_1 ON Menu.ID = Menu_1.ParentID SET Menu.[Level] = 1;"