Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cmd button - clear selected field datasheet form (2000)

    I have a few "miscellaneous use" fields, for the main data processing clerks' use in my database. I have a form in datasheet view, and I would love to be able to let them select one of those "miscellaneous" fields (highlight the whole thing), click a button (toolbar perhaps?) and clear the whole field. I only want to allow this on the miscellaneous fields - NOT on the other data fields. Is this possible? I know I could run an update query, modifying the SQL through code, but I don't know how to identify the selected field(s), nor how to limit the ones they're allowed to select/clear. Any ideas/suggestions?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    In the on click of a command button you can use something like this :
    <pre>If Screen.PreviousControl.Name = "MyAllowedControl" Then
    Screen.PreviousControl = Null
    End If</pre>

    Replace MyAllowedControl with the name of your control
    Eventualy you can add :
    RunCommand acCmdSaveRecord
    to save the record.
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    Thanks Francois,
    How can I attach an action like that to a toolbar button? Is it possible?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    Create a button on a toolbar.
    Create a function to clear the active control in a module. (Not the previous control)
    Add to the function all condition that you want.
    Go to Tools / Customize and go to the previous created button.
    Right click on it and select Properties.
    In the textbox On Action enter the name of your function.
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    Ok, I'm ashamed to have to ask this - how do I define the currently selected control?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    The code I use to test (see attachment in my previous post) is nothing more then :
    Function clearfield()
    Screen.ActiveControl = Null
    End Function
    Of course, you'll have to add the code to check if the active field is a field that may be cleared.
    And nothing to be ashamed, that's why the lounge exist, to ask things we don't know.
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    Thanks Francois. TYVM. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    I think I messed up my explanation. I want to clear the entire column's entries, not just the first record.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    Now that you know how to determine the active column (with the screen.activecontrol function), you can get the name of the underlying field and write an sql update string and execute it.
    Francois

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    <P ID="edit" class=small>(Edited by Francois on 17-Aug-05 00:36. replaced Screen.ActiveControl by ctl in the sql string)</P>Something like this (without any check if it is allowed or not)
    Function clearfield()
    Dim ctl As Control
    Dim strSQL As String
    Set ctl = Screen.ActiveControl
    strSQL = "UPDATE MyTable SET MyTable." & ctl.ControlSource & " = Null;"
    DoCmd.RunSQL strSQL
    Forms("MyForm").Requery
    End Function
    Francois

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cmd button - clear selected field datasheet form (2000)

    Do you accept cash, check, or charge? <img src=/S/wink.gif border=0 alt=wink width=15 height=15> Thank you.

    Edit - AAARGH! I can't seem to refer to the control name....

    Function clearfield()
    Dim ctl As Control
    Dim strSQL As String
    Set ctl = Screen.ActiveControl
    If ctl = "MISC1" Then
    strSQL = "UPDATE tblPersonnel SET tblPersonnel." & Screen.ActiveControl.ControlSource & " = Null;"
    DoCmd.RunSQL strSQL
    Else
    End If

    End Function

    help?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Cmd button - clear selected field datasheet form (2000)

    Try

    strSQL = "UPDATE tblPersonnel SET [" & Screen.ActiveControl.ControlSource & "] = Null;"

Posting Permissions

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