Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    Ogunquit, Maine, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clearing a Field (OfficeXP)

    I have a table that contains a Boolean field that a user can set. I would like to build a procedure that would clear the field thourgh a Do While Loop on a button click. I can do it with a command button that runs an Update query but I really don't want to see the message screens that come up.

    Any ideas? I'm not to familiar with VBA.

    Jordan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing a Field (OfficeXP)

    Executing an update query is much more efficient than looping through the records. You can suppress the usual warnings ("You are about to update 37 records" etc.) by putting DoCmd.SetWarnings False before executing the update query and DoCmd.SetWarnings True after it.

    Alternatively, you can use CurrentDb.Execute to execute an SQL statement, for example:

    CurrentDb.Execute "UPDATE tblMyTable SET MyFlag = False"

    This goes directly to the Jet Engine and doesn't display the warnings generated by the Access interface.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Location
    Ogunquit, Maine, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing a Field (OfficeXP)

    Thanks for the speedy response, did the trick.

    But now I would still like to learn how to step thru a table. I can recall doing it in dBase and just for the exercise would like to do it in Access. I know that I have to define a table, go to the first record, do my stuff on that record, step to the next and repeat until EOF is true.

    I'm having problems with the construct and syntax. Any chance you can help or point me in the correct direction?

    Jordan Freedman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing a Field (OfficeXP)

    Hello Jordan,

    Here is an example using ADO, the default data object model in Access 2000 and higher. To run this code, you must have a reference to the Microsoft ActiveX Data Objects 2.n Library in Tools | References... (in the Visual Basic Editor.) By default, this reference is set, but it doesn't hurt to check.

    The procedure ClearField takes two arguments: tha name of a table and the name of a field in that table. The field will be cleared in all records in the table. Call it like this:

    ClearField "tblMyTable", "MyFlag"

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub ClearField(strTable As String, strField As String)
    ' Declare variables
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    On Error GoTo ErrHandler

    ' Set connection and open recordset
    Set cnn = CurrentProject.Connection
    rst.Open strTable, cnn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect

    ' Loop through records
    Do While Not rst.EOF
    rst.Fields(strField) = Null
    rst.Update
    rst.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user and then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Location
    Ogunquit, Maine, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing a Field (OfficeXP)

    Hans,

    I appreciate all of your help. Am I wrong in assuming that there should be another SET statement to define the Recordset (rst)?

    Jordan

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Clearing a Field (OfficeXP)

    As HansV would note, because the recordset variable was declared:

    Dim rst As New ADODB.Recordset

    the Set statement is not needed. As noted in VBA Help for Dim statement:

    <blockquote><hr>New Optional. Keyword that enables implicit creation of an object. If you use New when declaring the object variable, a new instance of the object is created on first reference to it, so you don't have to use the Set statement to assign the object reference. The New keyword can't be used to declare variables of any intrinsic data type, can't be used to declare instances of dependent objects, and can
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing a Field (OfficeXP)

    I was offline for a few hours; MarkD has already given a very clear and comprehensive explanation. (Thanks, Mark!)

  8. #8
    New Lounger
    Join Date
    Oct 2003
    Location
    Ogunquit, Maine, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing a Field (OfficeXP)

    I finally figured out how to Call the sub and also had to clean out memory so that everything finally worked as described.

    Really appreciate all of the help and also realize that I have another subject (ADO) that I must gain some skill at.

    Jordan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •