locked
Use ADO or DAO to set all Fields in one row of a table to "" or Null RRS feed

  • Question

  • I have a table with one row that is a temporary place to store values that are archived to a permanent table when the user clicks on a button. 

    I want to set all fields in the temporary table to "" or Null at the click of another button without having to enumerate each field.

    After looking through a variety of forums, all I can find are references to updating a single field (or just a few) for a large number of records.

    How can I use VBA to loop through all of the fields in a 1 row table and set each to "" or Null?

    Thanks,

    Bob

    Wednesday, September 29, 2010 7:36 PM

All replies

  • Something like:

    Dim i As Long
    With ActiveDocument.Tables(1).Rows(1).Range
        For i = 1 To .Fields.Count
            .Fields(i).Result = ""
        Next i
    End With

    I am not sure what time of fields you are talking about and hence whether the .Result of the field can be set to ""

    Why not write the values to Document Variables and then insert the .Value of the relevant variable into the permanent table when required.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Bubbers66" wrote in message news:fbb7b0af-cc0e-4a8b-bedc-54d3a4ab082d@communitybridge.codeplex.com...

    I have a table with one row that is a temporary place to store values that are archived to a permanent table when the user clicks on a button.

    I want to set all fields in the temporary table to "" or Null at the click of another button without having to enumerate each field.

    After looking through a variety of forums, all I can find are references to updating a single field (or just a few) for a large number of records.

    How can I use VBA to loop through all of the fields in a 1 row table and set each to "" or Null?

    Thanks,

    Bob


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Thursday, September 30, 2010 8:21 AM
  • You could always delete the row and then insert it again with NULL values (or if the table has default NULL values it's even easier).

     

    Friday, October 1, 2010 2:20 AM
  • Thanks Doug,

    How would I set this up for an Access 2003 table? 

    The table is a temporary one linked to a form. 

    For context:

    The user selects XXX from a drop down link not associated with the temp table.  The fields on the form that ARE lined to the temp table are populated via DLookups.  The user can then edit any of the pre-populated fields.  When they are done, they click a button and the values are transferred to a permanent archive table. 

    Since I am still in development, Table fields are frequently being added or modified, so listing each one in VBA to set all values to "" is tedious to maintain.

    Maybe TMI, but this may give context why I'd like a way to look at each field in the temp table and set it to "" regardless of the changes I make.

    Thanks again,

    Bob

     

    Thursday, October 7, 2010 8:44 PM
  • If you are using Access, use a Delete query to delete the record.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Bubbers66" wrote in message news:2a9ee7ab-e701-4e6a-82f6-983d4d021d2a@communitybridge.codeplex.com...

    Thanks Doug,

    How would I set this up for an Access 2003 table?

    The table is a temporary one linked to a form.

    For context:

    The user selects XXX from a drop down link not associated with the temp table.  The fields on the form that ARE lined to the temp table are populated via DLookups.  The user can then edit any of the pre-populated fields.  When they are done, they click a button and the values are transferred to a permanent archive table.

    Since I am still in development, Table fields are frequently being added or modified, so listing each one in VBA to set all values to "" is tedious to maintain.

    Maybe TMI, but this may give context why I'd like a way to look at each field in the temp table and set it to "" regardless of the changes I make.

    Thanks again,

    Bob


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Thursday, October 7, 2010 10:45 PM
  • Thanks again Doug,

    I tried a delete query, but because I am not allowing the user to add records to the temporary table (this is designed specifically for one letter at a time - we don't want the user accidentally adding records to the table) a delete query causes numerous problems with record references.

    FYI:  When I copy the data from the temp table to the permanent archive, I am using a simple Append * query - which is why I chose a temporary table rather than simply storing the data on the form and copying each field to the archive.

    Being able to simply sweep through the fields on this one (and only) record in the temp table would make life a bit easier.

    Cheers!

    Friday, October 8, 2010 4:08 PM
  • In that case, use an Update Query.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Bubbers66" wrote in message news:0b32e395-70a0-4834-816c-b6b2dd169d57@communitybridge.codeplex.com...

    Thanks again Doug,

    I tried a delete query, but because I am not allowing the user to add records to the temporary table (this is designed specifically for one letter at a time - we don't want the user accidentally adding records to the table) a delete query causes numerous problems with record references.

    FYI:  When I copy the data from the temp table to the permanent archive, I am using a simple Append * query - which is why I chose a temporary table rather than simply storing the data on the form and copying each field to the archive.

    Being able to simply sweep through the fields on this one (and only) record in the temp table would make life a bit easier.

    Cheers!


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, October 8, 2010 9:32 PM