Count Fields

[Thompyt]

HB is recordset
C = For Next count

HB!["Equip_" & C] = Null

Without count it would be:

HB.Edit
HB![Equip_1]

HB![Equip_2]

HB![Equip_3]

HB![Equip_4]
HB![Equip_5]
HB![Equip_N…..]
HB.Update


HB! "[Equip_" & C * "]" = Null gets Invalid use of property.

HB! ["Equip_" & C ] = Null gets Item not found in this collection

Col_Name = "HB![Equip_" & C & "]"
Col_Name = Null get invalid use of null

[orange]

In simple English what are you trying to do?
An example might help readers understand the issue.

[Thompyt]

Orange,
Right now I am trying to build a simple loop through many fields just to clear data. Then I'll go from there

I have 40 Fields in a table named Equip_1 through Equip_40, the column couny changes

I have determined the column count as: CurrentDb.TableDefs("Table2").Fields.Count - 6

C is an integer with a value starting at 1.

Now I want to assign "" to each field

Normally you would write ![Field_1] ="", ![Field_2] =""...….![Field_40] =""

I think this is a bit excessive and can be shortened to a few lines.

DO How would I insert the number value in?

MSGBOX "Equip_" & C gives you - Equip_1 through 40 where C-1 to 40 in a for next loop. (When you want to click OK 40 times.)

Now I am trying to insert string between the bookends to call the field I want to write to

!["Equip_" & C], But it is the incorrect syntax.

[Micron]

you could try HB.Fields("Equip_" & C)=??

No idea of how you're trying to set the value of a field by showing us HB![Equip_1]

[orange]

Are you trying to delete records?

[Thompyt]

Orange,
I was giving field names (Column Names) to set the trend of naming. I am trying to clear data from the columns.

So far I have this:

Dim db As DAO.Database
Dim HB As DAO.Recordset
Dim C As Integer, Col_Name As String
Set db = CurrentDb()
Set HB = CurrentDb.OpenRecordset("Table2")
    C = 1
Do While Not HB.EOF
    HB.Edit
        For C = C To (CurrentDb.TableDefs("Table2").Fields.Count - 6)
    HB.Fields("Equip_" & C) = ""
    HB.Update
        Next C
Loop

End Sub 

This does seem to loop since the error I get is "Update or CancelUpdate without AddNew or Edit. Even though I have .edit in there.

[Micron]

I can see some issues (such as not moving through the recordset) but why use complicated code when you could just use an update query?

[orange]

?? Still not sure what you're trying to do???

Are you trying to delete records? OR
Are you trying to remove values from the Equip_1 to 40 fields of ALL records in the table?

[Thompyt]

Its a simple start to a more complicated thing.

I know I could make a update query with 40 fields in it.

Moving through the fields?

Movnext?

[Thompyt]

?? Still not sure what you're trying to do???

Are you trying to delete records? OR
Are you trying to remove values from the Equip_1 to 40 fields of ALL records in the table?

remove values from the Equip_1 to 40 fields of ALL records in the table

[Gicu]

I think a much better option would be to use an update query, you can build that in a VBA loop if you wish:

Dim sSQL As String, sField As String, iCount As Integer


sField = ""
For iCount = 1 To 40
sField = sField & "," & "[Eguip_" & iCount & "] = Null"
Next iCount


sField = Mid(sField, 2)
sSQL = "UPDATE Table2 SET " & sField & ";"


CurrentDb.Execute sSQL, dbFailOnError

Cheers,
Vlad

[orange]

I'd like to hear the rationale for a table with 40 fields named Equip_1 thru Equip_40.
Vlad's solution looks good.

[Thompyt]

Its setting up a Visio export table. There may be 40 recorded pieces of equipment on a platform.

I think I understand how Vlads code works, but I need to think more on it, as I was expecting to do other things and didn't want to use the strSQL way.

Thanks Vlad

[Micron]

I think there was a question about my post. While not the way I'd do it, this is closer to what you had

Dim rs As DAO.Recordset
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("tblYourTable")
If Not (rs.EOF And rs.BOF) Then
  Do While Not rs.EOF
    With rs
      For i = 1 To rs.Fields.count - 1 'assumes the first fld to be excluded but all the rest included
        .Edit
        .Fields(i) = "" 'or Null or 0 or whatever
     Next
    .Update
    .MoveNext
    End With
  Loop
End If

It certainly will fail if there's any violation of data rules. The other code given isn't a query IMO, but is vba sql. I meant a stored query (query def). If you can bring yourself to create a table with fields named like that, an actual query shouldn't be out of scope either.
What I wrote should work regardless of the number of fields or their names.

[Micron]

I think I understand how Vlads code works,

It should if all you have is one record to be updated. I reviewed but don't see anything where you said if there's only one record to be updated or if there's more.