Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282

    Limit form to each dropdown option appearing once, enable check box if other check box is true

    This is a continuation from thread https://www.accessforums.net/showthread.php?t=62079, I marked it as solved before realising there were a few more queries which I was unsure how to go about fixing.

    Form "AddLowRiskAssessment" displays my problem, and accessed by:
    Enter an ID into the "Liberi ID" Combo box. 70 is an example which will work.
    Press "Search"
    Press "Add New Assessment"
    Select a District, Team and Assessor: District4, Team14 displays 2 Assessor options, select either.
    Enter any two dates into the "Assessment Date" and "Review Date" Combo boxes
    Click "Next Page"



    This displays a form with the information entered in the previous screens.
    The behaviour dropdown displays a range of behaviours which the assessor may believe the child is at risk from, they should be able to select a behaviour, and each behaviour should only be able to be selected once. To do this, I used a dlookup, as suggested by aytee111 in my previous thread, however I encounter the following error whenever the dropdown loses focus. I have tried the dlookup in the before insert, after update, on lost focus and the same error message appears:
    Click image for larger version. 

Name:	Duplicate Behaviour Null.PNG 
Views:	29 
Size:	10.9 KB 
ID:	26582
    When I debug, this is the section of code highlighted:
    Click image for larger version. 

Name:	Duplicate Behaviour Null Debug.PNG 
Views:	29 
Size:	7.5 KB 
ID:	26583
    The only time this doesn't appear, is if I don't select anything in the dropdown, which makes this appear:
    Click image for larger version. 

Name:	Duplicate Behaviour Null Blank.PNG 
Views:	29 
Size:	19.2 KB 
ID:	26584
    (debugging highlights the same section as above)

    My second problem, is I want the "Assessment" and "3rd Party Intelligence" check boxes be not enabled until the user ticks the check box for "Present". Currently, ticking this box makes all behaviours' "Assessment" and "3rd Party Intelligence" boxes become enabled.

    I have attached my database for ease of use, your assistance is much appreciated!!!
    Attached Files Attached Files
    Last edited by Heathey94; 11-29-2016 at 09:42 AM. Reason: Forgot to attach Database

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cant DIM BehID as long because Dlookup can return null.
    just dim as variant and it will still be long if it DOES return a value.
    but you can still check for null without error.

    and
    chkbox2.enabled = chkBox1.value

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Add a line before the yellow: On Error Resume Next.

    Also, this code must also be in the BeforeUpdate event (edit: problem with this, ignore for now)

  4. #4
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Both of your suggestions for my first problem remove the error, but the user is still able to select the same option multiple times.

    and for my second problem ranman256, your suggestion also enables the check boxes on all rows, for reference, this is the code I had originally:

    If Me!RiskPresent = True Then
    Me!Assessment.Enabled = True
    Me!TPI.Enabled = True
    Else
    Me!Assessment.Enabled = False
    Me!Assessment = False
    Me!TPI.Enabled = False
    Me!TPI = False
    End If

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That is why I put it in the BeforeUpdate event - to prevent multiple entries. Still working on that.

    Is there a different form to edit this assessment where these checks need to be made as well?

  6. #6
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Yes, the LowRiskAssessment form, although they are currently unable to add/change behaviours - something I would like to change if possible. I was going to come to that when I had managed to get the Add version to work.

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is the code to go into the BeforeUpdate event of the subform:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim BehID As Long
     On Error Resume Next
     BehID = DLookup("BehaviourID", "TblAssessmentDetails", "BehaviourID=" & Me!BehaviourID & " AND AssessmentID=" & Forms!AddLowRiskAssessment!AssessmentID & " AND ResponseID<>" & Me!ResponseID)
     If BehID > 0 Then
     MsgBox "This behaviour has already been selected."
     Cancel = True
     End If
    End Sub

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The only way to be able to enable/disable individual controls on a continuous subform is to use conditional formatting. And unfortunately this does not apply to checkboxes.

    - Change the 3 checkboxes to comboboxes with format Yes/No, and Value List of Yes;No and Enabled=false (RiskPresent isn't required, just to make them look all the same)
    - on each of the two fields, go to Format and then Conditional Formatting
    - add new rule: Expression is : [RiskPresent]=True
    - under that line on the far right is a little block thing which says Enable - click that

    I would also add record selectors to your form so that they can delete entries that they made in error.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    A few observations:

    1) For the subform "LowRiskBehaviours":
    You have this code in the lost focus event of the combo box "BehaviourID""
    Code:
    Private Sub BehaviourID_LostFocus()
        Dim BehID As Long
        BehID = DLookup("BehaviourID", "QryCheckAddLowRiskBehaviours", "BehaviourID=" & Me!BehaviourID)
        If BehID > 0 Then
            MsgBox "This behaviour has already been selected."
            Cancel = True
        End If
    End Sub
    You cannot use the line
    Code:
    Cancel = True
    in the LostFocus event because it is NOT a cancel-able event.
    Here is the control before update event
    Code:
    Private Sub BehaviourID_BeforeUpdate(Cancel As Integer)
    
    End Sub
    See the difference??

    Even if you cancel the "BehaviourID" selection, a new record has been created and the "BehaviourID" is required..... thus the error...


    2) I would expect the DCount() function to be used instead of DLookup(). (To me it makes more sense...)
    In the query "QryCheckAddLowRiskBehaviours", the "BehaviourID" should not have a criteria.
    A) Which record is to be used in the query for the "BehaviourID" if you have 3 behaviors selected?
    B) The "BehaviourID" is provided in the DLookup (DCount) criteria argument.

    Or instead of DLookup/DCount, you could open a recordset in the Sub ..... wouldn't need the saved query "QryCheckAddLowRiskBehaviours" unless it is used in other forms/reports


    3) The query "QryCheckAddLowRiskBehaviours" has criteria for the two fields.
    AssessmentID = [Forms]![AddLowRiskAssessment]![AssessmentID]
    BehaviourID = [Forms]![AddLowRiskAssessment]![BehaviourID]

    But the combo box "BehaviourID" is on a SUB FORM, not the main form.
    The combo box "BehaviourID" cannot be found on the main form when the query executes, so the query always returns 0 records. Because the Function is DLookup, a record is not there, so NULL is returned. (You could wrap the DLookup() in the NZ function to convert the NULL to a zero.)

    I'm not sure this is correct, but maybe the criteria for the "BehaviourID" field should be
    [Forms]![AddLowRiskAssessment]!Present.Form![BehaviourID] ("Present" is the name of the sub form CONTROL)
    (see http://access.mvps.org/access/forms/frm0031.htm)




    --------------------------------------------------
    BTW, these two lines should be at the top of EVERY module:
    Code:
    Option Compare Database
    Option Explicit
    "Option Explicit" is missing in 6 modules.......

  10. #10
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    aytee111, that code worked, thank you! And Steve, since it is working, I don't think I'll change it to a DCount or enter any of your code - don't fix what's not broken as they say...

    Having said that, it would maybe be better if I could restrict the options in the combo box to just those which have not been selected, as this would mean there are less options for the user to find the one they are looking for - it doesn't matter if it would be too much hassle. I also think it would be beneficial for the delete button (see below)

    I think I must have accidentally named the subform CONTROL Present by accident when I was trying to name the Present Check Box, I have now changed it to 'LowRiskBehaviours'

    Quote Originally Posted by aytee111 View Post
    The only way to be able to enable/disable individual controls on a continuous subform is to use conditional formatting. And unfortunately this does not apply to checkboxes.

    - Change the 3 checkboxes to comboboxes with format Yes/No, and Value List of Yes;No and Enabled=false (RiskPresent isn't required, just to make them look all the same)
    - on each of the two fields, go to Format and then Conditional Formatting
    - add new rule: Expression is : [RiskPresent]=True
    - under that line on the far right is a little block thing which says Enable - click that
    I see, that's unfortunate. Is making 'Present' true if 'Assessment'/'3rd Party Intelligence' are true a possibility, or is that also impossible? (again restricting it so that just that row is affected.)

    Quote Originally Posted by aytee111 View Post
    I would also add record selectors to your form so that they can delete entries that they made in error.
    That's a good point, I hadn't thought of that. Would a button (like below) do the trick? If so, what code would I need?

    Click image for larger version. 

Name:	Delete Behaviour.PNG 
Views:	22 
Size:	3.1 KB 
ID:	26590

    Would it be able to delete rows which hadn't been officially inputted yet? for instance, in the below example:

    Click image for larger version. 

Name:	Delete Duplicate.PNG 
Views:	22 
Size:	8.7 KB 
ID:	26591
    'Regularly coming home late or going missing' has been "accidentally" selected twice. Changes can still be made in that row (as indicated by the tick in the first box) and the error message does not appear until the row has lost focus. I believe this would mean the 'Delete' button would not be able to delete the row, as it has not been placed into the table yet. I believe the option suggested above - to only allow behaviours which have not been selected - would remove this problem, but there may be another alternative, it depends what you believe would be easier.

    I will re-upload my database once I have gone through it to check "Option Explicit" is added to all of the code, I think the reason it's missing from so many is due to the fact it does not automatically appear in the code, as I thought it would.
    Attached Files Attached Files
    Last edited by Heathey94; 11-30-2016 at 05:48 AM. Reason: Added Database

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Deleting - it is better to add record selectors than to use a button, it is a property on the form. To "delete" a row that you are still entering you can hit 'Esc'.

    Repeat entries - you have removed the code from the LostFocus event of BehaviourID, not sure why you did this. You can copy the code from the BeforeUpdate event, then the error message will come up straight away.

    Is making 'Present' true if 'Assessment'/'3rd Party Intelligence' are true a possibility
    I don't think I understand your question - wasn't the point to only allow entry to those two fields if RiskPresent is true?

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    it would maybe be better if I could restrict the options in the combo box to just those which have not been selected
    You can change the row source of BehaviourID to show only those that have not been entered - but that changes it for the whole subform and all the descriptions disappear. I am not sure how to fix this, maybe someone else can come up with something. The only way I can think of it to separate out the description from the combobox but that becomes messy.

  13. #13
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Quote Originally Posted by aytee111 View Post
    Deleting - it is better to add record selectors than to use a button, it is a property on the form. To "delete" a row that you are still entering you can hit 'Esc'.
    Changing Record Selectors to "yes" just added this to the side (which doesn't look very nice):
    Click image for larger version. 

Name:	Record Selectors.PNG 
Views:	20 
Size:	654 Bytes 
ID:	26594I'm fully aware I probably needed to turn something else on as well/instead, but I'm not sure what it is.

    I thought the delete button would be simple for the user to understand, but I'm willing to change the layoutif you have better ideas.

    Quote Originally Posted by aytee111 View Post
    Repeat entries - you have removed the code from the LostFocus event of BehaviourID, not sure why you did this. You can copy the code from the BeforeUpdate event, then the error message will come up straight away.
    I removed it because I wasn't told to do it at any point, I was just playing around with it to trying to make it work, I've added it back in and it works to an acceptable standard - If for instance you tick one of the check boxes to make it lose focus, it will still tick it, but I think it's clear enough to get the user to change what they are doing.

    Quote Originally Posted by aytee111 View Post
    I don't think I understand your question - wasn't the point to only allow entry to those two fields if RiskPresent is true?
    There are two options. Either:
    The user must click "Present" to enable the other two boxes, or:
    The user selects Assessment/Third Party Intelligence, and Present is automatically selected.

    This is because in the old versions of the form (prior to version 3, as stated at the bottom), 'Present' was the only option. In version 3, this was replaced by two options - 'Assessment' or '3rd party intelligence'. 'Present', could have meant either 'Assessment' or '3rd party intelligence' (or both), but we obviously don't know which.
    In order to ensure we are able to report on the data accurately - how many children had the 'Truanting from School' behaviour recorded, for instance, we would need to use the 'Present' column to find these children. Anyone where the behaviour was highlighted in the 'Assessment' or through '3rd Party Intelligence', would also need to be included with the behaviour 'Present'.

    Hopefully I've made that clearer?

    Quote Originally Posted by aytee111 View Post
    You can change the row source of BehaviourID to show only those that have not been entered - but that changes it for the whole subform and all the descriptions disappear. I am not sure how to fix this, maybe someone else can come up with something. The only way I can think of it to separate out the description from the combobox but that becomes messy.
    OK, I'll just leave it as it is then (unless a better suggestion does come up)

  14. #14
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The record selectors should be next to the BehaviourID - I think you put them on the wrong form. It is your choice whether to use command buttons or record selectors, no real difference (the latter would include user training but would hold true for any and all forms and databases, once they know it they know it! and I am a lazy programmer). If you use command buttons, you could make them very small and just have the standard "X" delete picture on it for the icon.

    As for the options - as previously stated, this can only be done if you change them to text/combo-boxes and do it thru conditional formatting rules.

  15. #15
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Yeah, that makes sense. I think I'll use command buttons, they just look a bit nicer. Is it possible to make the Delete Button also act as hitting escape on a row that hasn't been entered? I'm just trying to make it as user friendly as possible.

    I thought that might be the case. It just seems so simple, you'd have thought it would be easy. I'll give making them combo boxes a go then.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-02-2016, 01:19 PM
  2. True/False Check Box Usage in Forms
    By avicknair in forum Access
    Replies: 3
    Last Post: 11-12-2015, 12:54 PM
  3. Replies: 13
    Last Post: 11-11-2014, 02:43 PM
  4. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  5. Enable a check box after printing
    By nukephysicist in forum Programming
    Replies: 1
    Last Post: 05-26-2011, 01:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums