Results 1 to 14 of 14
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Limit ckbx selection (2002 SP-3)

    I think I just backed myself into a corner. . .again.

    I would like to limit the use (to once) of a ckbx in a record set based on whether it has been "checked" (True) in any other record. Also, rather than have it merely disabled, I would have to have some kind of msg display to alert the user that another record already has that value (True) and (upon close of the msg) would popup that particular record. I had tried to use "no dups" , but that won't work because all the remaining records have a value of "False" for that ckbx. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>. . .(I'll try anything) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Limit ckbx selection (2002 SP-3)

    This is quite a nasty challenge - it typically involves processing the entire recordset any time the form is opened in order to even allow the enabling/disabling of the check box to begin with. We do this kind of thing with SQL Server triggers do deal with situations such as a preferred address where we track multiple addresses for a person or company, but presumably that isn't an option for you. Thus I think you are stuck with using DAO or ADO to open a query that checks to see whether the recordset includes a record that has the checkbox already set (you may also need an audit of some sort to look for situations where 2 or more records have a flag set). That code would presumably go on the Current event procedure, unless you always unload and reload the form when you open a new recordset.

    It may also be possible to do this using a different record structure if you have some sort of header or master record associated with the recordset. In that case, you could move the indicator to the master record, and use a pointer to the sub-record which is the "preferred" on. Of course that approach also would require some code to set the pointer in the master record when a subrecord is designated as the preferred one .
    Wendell

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    Wendell,

    Thanks for taking the time to consider this Wendell. Yeah, it all looks a little too scary to me, and the end reult wouldn't be worth complicating the process. What I did manage to do was use the On Change event to call a continuous form sorted by the value inserted in a txtbx when the ckbx is ticked. If there's another record with the same selection, it will float to the top of the continuous form. A warning in the header advises that only one record should have that selection made. Pretty "clunky", but it seems to work.
    <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limit ckbx selection (2002 SP-3)

    In the BeforeUpdate event of that checkbox, you need to do a DLookup to find if another record has this field = True. If Dlookup returns Null, then no other record is "checked", so you can safely accept it in the current record. If you do find such a record, then you can handle it accordingly; that is, pop a message warning user, ask if they really want to do this (and if yes, then uncheck the other record), or whatever you want.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    Mark,

    Hey, that sounds interesting! Got any code that would get me started in the right direction? I don't think I've ever even used DLookup before.

    tblMaster
    frmTeamAssign
    ckTeamAssign

    frm TeamAssignReview
    ckTeamAssign2

    Sure appreciate the "donation" (even if you don't have code example) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Got church this morning, but I'll be back.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    This was fun. . .didn't accomplish anthing, but it was fun <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    Private Sub blnWarriorAssign_BeforeUpdate(Cancel As Integer)
    Dim varX As Variant
    varX = DLookup("[blnWarriorAssign]", "tblMaster", "[blnWarriorAssign] = True")

    If (varX = "True") Then
    Beep
    MsgBox "This selection has already been made on another record. Un-check this box and locate other record.", vbExclamation
    Me.blnWarriorAssign.SetFocus
    End If
    End Sub

    <img src=/S/bubbles.gif border=0 alt=bubbles width=31 height=17>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Limit ckbx selection (2002 SP-3)

    You have to check that the user has ticked the check box rather than cleared it.
    Moreover, the value of a boolean field is True or False, not the string "True" or "False".

    Private Sub blnWarriorAssign_BeforeUpdate(Cancel As Integer)
    Dim varX As Variant
    If Me.blnWarriorAssign = True Then
    varX = DLookup("[blnWarriorAssign]", "tblMaster", "[blnWarriorAssign] = True")
    If varX = True Then
    Beep
    MsgBox "This selection has already been made on another record. Un-check this box and locate other record.", vbExclamation
    Me.blnWarriorAssign.SetFocus
    End If
    End If
    End Sub

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Limit ckbx selection (2002 SP-3)

    I would take what Hans wrote, but add the line of code "Cancel = True". This will keep user from keeping the box checked:

    Private Sub blnWarriorAssign_BeforeUpdate(Cancel As Integer)
    Dim varX As Variant
    If Me.blnWarriorAssign = True Then
    varX = DLookup("[blnWarriorAssign]", "tblMaster", "[blnWarriorAssign] = True")
    If varX = True Then
    Beep
    MsgBox "This selection has already been made on another record. Un-check this box and locate other record.", vbExclamation
    Cancel=true
    End If
    End If
    End Sub
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    Thanks Hans and Mark

    It all works great (including Cancel = True)
    Also, I discovered I really didn't need "Me.blnWarriorAssign.SetFocus" from my original "code".
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    Hans,

    I just realized a consequence here.

    FrmPrayer_Detail has blnActive which, if unchecked, makes the record unviewable. If this record contains the blnWarriorAssign (True) then I'll never be able to find the record. Might there be some way to call the actual record in the code you generously supplied? I can only suppose I need to create another form "like" the one I have open to reveal the record (can't open a form that's already open?). <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    Hmm. . .
    Getting "Expected: End of statement" at "FormName:"
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Limit ckbx selection (2002 SP-3)

    Sorry, a typo. I wrote DoCmd,OpenForm, now corrected to DoCmd.OpenForm (point instead of comma).

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Limit ckbx selection (2002 SP-3)

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Beautiful!
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Limit ckbx selection (2002 SP-3)

    In fact, it is possible to open a new instance of the same form, but that's a bit technical, so it's easier to create a duplicate of the form (you can use Copy / Paste for this), and open the relevant record in it.

    Private Sub blnWarriorAssign_BeforeUpdate(Cancel As Integer)
    Dim varX As Variant
    If Me.blnWarriorAssign = True Then
    varX = DLookup("[blnWarriorAssign]", "tblMaster", "[blnWarriorAssign] = True")
    If varX = True Then
    Beep
    DoCmd.OpenForm FormName:="frmCopy", _
    WhereCondition:="[blnWarriorAssign] = True", WindowMode:=acDialog
    Cancel = True
    End If
    End If
    End Sub

    Replace frmCopy with the name of the copied form.

Posting Permissions

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