Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Unanswered: MS Access, VB, button to clear 'ticks' will not work

    This should be easy but I just cannot get it to work, this forum has helpful earlier,

    This is a simple problem....
    I have a Form.
    In the Form, I have a 'tick' box i.e. Check box (SETHIGHLIGHT) that the user can check - It is used to set highlights in a record - that works fine... My problem Is, I made a button (Remove_All_Highlights) intending to simply clear all the SETHIGHLIGHT checkboxes

    What happens is, when I press the button, ONLY The last checkbox that was checked gets unchecked... all the others Records SETHIGHLIGHT stays checked. (Sounds simple huh?- its killing me, I tried several things, but it should be simple<I think>)

    Here is my code:

    Private Sub Remove_All_Highights_Click()
    SETHIGHLIGHT = False
    End Sub

    Any suggestions would be appreciated (totally-pbm was fantastic last time)
    Last edited by MesaFloyd; 08-17-06 at 14:49.

  2. #2
    Join Date
    Feb 2004
    Posts
    90
    I'm assuming it's a continuous form. If so, try this:

    Code:
    Private Sub Remove_All_Highights_Click() 
    DoCmd.GoToRecord , , acFirst
    Do While Me.CurrentRecord - 1 < Me.RecordsetClone.RecordCount
    SETHIGHLIGHT = False
    DoCmd.GoToRecord , , acNext
    Loop
    End Sub

  3. #3
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi Goldy
    Your the best! worked like a charm!

    This form can hold anwhere from 200-1000 records. This one im using has 260 records for develoment-your routine scrolls speedily through all the records and resets all the ticks in that column.

    Thank you so very very much.....
    While I built this entire program myself (newbie) for the most part I have augured through the problems but this is a bit out of my <programming> league(two days and 1/2 head of hair).
    I will now tredge on......!

    The best to you!

  4. #4
    Join Date
    Feb 2004
    Posts
    90
    No problem.

    But actually if there are that many records, the method I posted is a bit slow and inelegant.

    Have a look at the attached sample I made for a better solution. I've assumed your continuous form is a subform on another form.

  5. #5
    Join Date
    Feb 2004
    Posts
    90
    Oops forgot sample.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    That is very nice, nicer than the first one. Definetly more elegant.
    Since I am not a real prgrammer(more of a hardware guy that piddles in code when I must), I would not be able to incorprate that style unless it was setup like your first suggestion- that was easy to sew into my Form.
    - Heck I even added a message...."Are you sure you want to remove ALL highlights... this cannot be reversed" -yesno

    My form is single level. Very basic but functional for the application over the present Excel being used now...
    Appreciate your expertise Goldy, really.

  7. #7
    Join Date
    Feb 2004
    Posts
    90
    If your form is single level then try:

    Code:
    Private Sub Remove_All_Highights_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.RunSQL "UPDATE TABLENAME SET TABLENAME.SETHIGHLIGHT = 0;"
    Me.Requery
    End Sub
    All you have to do is change the two instances of the word "TABLENAME" to the name of the table of which SETHIGHLIGHT is a member.

  8. #8
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Does work with a bit of a bug.
    When the 'remove' button is pressed, a popup comes saying " You are about to update 260 Rows..... etc" this is OK but can I modify the verbage?

    If you press YES, it deletes the ticks just fine
    but
    If you press NO, a popup comes up saying...."Run-Time Error 2501. The Run SQL Action was cancelled .... and gives buttons to Debug...

    Here is the code:

    Private Sub Remove_All_HighLites_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.RunSQL "UPDATE tblPropertyLists SET tblPropertyLists.SETHIGHLIGHT = 0;"
    Me.Requery
    End Sub

    (Note I earlier mispelled ..... All_HighLites_Click() << is correct.

  9. #9
    Join Date
    Feb 2004
    Posts
    90
    Ah, yes. I always turn off the action query prompt in access options.

    Try this:
    Code:
    Private Sub Remove_All_Highights_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE TABLENAME SET TABLENAME.SETHIGHLIGHT = 0;"
    DoCmd.SetWarnings True
    Me.Requery
    End Sub

  10. #10
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Very nicely done and very appreciated Goldy.
    Works Perfectly!
    Your forever in my graditude.

    Thank you

Posting Permissions

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