For Each Problem

[coolscan3]

Cannot see a problem with this Code


Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim n As Integer

Set db = CurrentDb

DoCmd.SetWarnings False

DoCmd.OpenQuery ("qBelowStandard") **** Make Table Query

Set tdf = db.TableDefs("tBelowStandard")

MsgBox (tdf.Fields.Count) ******* Displays 72

For Each fld In tdf.Fields

n = n + 1

'If Right(fld.Name, 4) <> "Con" Then *** commented ot for test purposes

MsgBox (fld.OrdinalPosition)
tdf.Fields.Delete fld.Name

'End If

Next fld


MsgBox (tdf.Fields.Count) ******* Displays 36

MsgBox ********* Displays 36


The OrdinalPosition is incrementing in twos

 

[PHV]

For i = tdf.Fields.Count - 1 To 0 Step -1
n = n + 1
MsgBox (tdf.Fields(i).OrdinalPosition)
tdf.Fields.Delete tdf.Fields(i).Name
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

I've always found it better to delete backwards when doing this kind of operation, something like:

For i = tdf.Fields.Count - 1 To 0 Step -1

    MsgBox (tdf.Fields(i).OrdinalPosition)
    tdf.Fields.Delete tdf.Fields(i).Name

Next i

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[HarleyQuinn]

Pays to refresh the thread before posting, sorry PHV

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[coolscan3]

Thanks for you help, yes this does work fine