Page 5 of 6 FirstFirst 123456 LastLast
Results 61 to 75 of 84
  1. #61
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a duplicates query (use the wizard). In the subform BeforeInsert event - also can be done when they select the behaviour in both places (I would remove the top combobox, by the way, it serves no purpose at all, it does exactly the same as what the other one does, so why have it? Just adds unnecessary complexity). Do a dlookup on that query. If the behaviour is found then prevent them from adding it (in the BeforeInsert say Cancel=true)

  2. #62
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oops, didn't answer your second question.

    For checkboxes, use the OnClick event. Add the line to set fields if certain conditions are met.

    For enabled, you can do that in the form design so that the fields start out as dis-enabled (!) until certain conditions are met. OnClick you can set them to Enabled again. You will have to dis-enable them again in the subform's AfterInsert event so that they are ready for the next record to be added.

  3. #63
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    okay, I've not used a dlookup before, and I haven't used a query in VBA yet either, so there are probably quite a few errors on show here... anyway, my attempted code is: DLookup(Me!BehaviourDescription,Queries!QryCheckBe haviours,Cancel=True)
    QryCheckBehaviours has AssessmentID and BehaviourID, both have criteria of the AssessmentID and BehaviourID (respectively) from the LowRiskBehavioursForm, how many things am I doing wrong?

    I think we're going to go with the not enabled until present is true, option - would the code look something like "Me!Assessment = Enable"?

    I've attached the database again in case you need it to work out what I'm going on about - and sorry for going on so long after the thread is solved!

    I'm not sure why there is a gap in behaviour in this comment - there isn't when I view it in edit mode...
    Attached Files Attached Files
    Last edited by Heathey94; 09-28-2016 at 06:51 AM.

  4. #64
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You must learn to look things up for yourself! At least the simple things like syntax.

    -DLookup("fieldname","qry/tbl name","criteria(optional)")

    Dim BehID as long
    BehID=Dlookup("BehviourID","QryCheckBehaviours","B ehaviourID=" & me!behaviourID)
    If BehID>0 then
    MsgBox "Behviour already used"
    Cancel=True
    End If

    Here's another note - always have a control's name the same as its control source. In queries and in VBA when referring to a control, Access sometimes uses the name and sometimes uses the control source. So in this dlookup statement I refer to BehviourID but I am not sure if that is right or not, Access may be requiring the "Name" of the control. Far easier to always make them the same, then no errors will occur.

    You can put the dlookup in the AfterUpdate event of the Beh-combobox as well, both places. It doesn't have a cancel statement so remove that.

    You query doesn't work - didn't you test it???? It needs the name of the parent control, [Forms]!AddAssessmentRecord![LowRiskBehaviours]![AssessmentID]

  5. #65
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your other question, setting those check boxes. Write it out in a sentence(s) and I will help you translate it into an if statement. Such as, if the tick Assessment or Third Party, then this must happen, etc.

  6. #66
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I did try to search for it myself, but I couldn't find any sites which explained it properly, they all seemed to assume I knew the format for it.

    I didn't think the query would work until there was something in the com,bo box, which I couldn't do until I'd got this working...

    Thanks, I'll give it a crack

  7. #67
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, I copied your code and changed it where necessary (QryCheckBehaviours is now QryCheckLowRiskBehaviours as I'll have to repeat for Medium and High) and it displayed the following error message:

    Click image for larger version. 

Name:	PreventDuplicateBehavioursError.PNG 
Views:	6 
Size:	15.3 KB 
ID:	25961

    Debugging highlights the following section of code:
    BehID = DLookup("BehaviourID", "QryCheckLowRiskBehaviours", "BehaviourID=" & Me!BehaviourID)

    Hovering over it, BehID=0 and Me!BehaviourID=Null

    what have I missed?


    The check boxes:
    Assessment and third party intelligence begin disabled - I assume this is just the option in the property sheet?
    then, if Present is checked, Assessment and third party intelligence become enabled.

    Process repeats for each behaviour selected from the dropdown box.

  8. #68
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    That error only occurs if I try to select a behaviour already selected (in case it's not clear)

    oh, and for the if statements, if present is unticked, Assessment and 3rd Party Intelligence are also unticked, and then are disabled again

  9. #69
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Why is behaviourID null? That is the question! At what point exactly did the message show up? Trace it thru the steps that you are performing just prior to the error coming up.

  10. #70
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, I was wrong - it happens when I first try to select something.

    So, I followed the steps to open the page, then, when I attempt to select a behaviour from the combo box, I get the error, could it be because it's in the BeforeInsert part of the VBA - the record won't insert, so that a behaviour can be selected, and the behaviourID remains null?

  11. #71
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    sidenote, it also happens when I try to tick a box before selecting a behaviour - this may be obvious already, or it may not.

  12. #72
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    if Present is checked, Assessment and third party intelligence become enabled
    Code:
    If Me!Present=True then
     Me!Assessment.Enabled=True (or you can make it Locked=True or both)
     Me!TPI.Enabled=True
    else
     Me!Assessment.Enabled=False
     Me!Assessment=False
     Me!TPI.Enabled=False
     Me!TPI=False
    End If

  13. #73
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    where would that code go?

  14. #74
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    OnClick for present

  15. #75
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Yeah sorry - blank moment.

Page 5 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-01-2016, 04:06 PM
  2. Replies: 1
    Last Post: 12-20-2015, 01:09 PM
  3. Replies: 4
    Last Post: 07-14-2015, 06:49 PM
  4. Replies: 2
    Last Post: 05-28-2013, 04:00 PM
  5. Sub form based on table select specific records
    By ReluctantGeek in forum Forms
    Replies: 0
    Last Post: 01-21-2012, 11:24 AM

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