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

    Access Structure Design (form process), open message box with multiple options on error

    Before I get started, some basic terminology I'll use:


    Assessor and User are not the same person. The user is inputting the information the assessor completed in the PDF/Word Document version of the CSE Assessment

    So, a bit of background to my database:
    I'm trying to collect the responses to many CSE Assessment forms in an Access database (example of said form is attached)

    Problem 1:
    The user is now able to add assessments to the database, but I'm not sure how to go about viewing (and then editing) these forms if there was an error during the input stage.

    Part a (probably two separate issues):
    I'm currently experiencing some problems opening the AssessmentRecord form (To get here, search for an ID in the Liberi ID text box on the Homepage (automatically opened)). If I search for ID 70, the Child's record displays fine, however when I then click the "View Assessments" button, the below error box appears, the ID changes to 71 and the subform is already populated with an assessor's name - despite the fact no assessors were selected from the dropdown.

    Click image for larger version. 

Name:	AssessmentRecordOpen.PNG 
Views:	104 
Size:	17.7 KB 
ID:	26653

    Part b:
    Ideally - once on the "AssessmentRecord" form - I'd like to go to the child's record, and select the View Assessments button. As some children could have many assessments, the user may wish to narrow it down by selecting a specific assessor, or they may wish to view all assessments, no matter who the assessor was. Ideally, this would be achieved by selecting the "View Assessments" button on the "ChildRecord" form.

    Problem 2:
    Also, if the user attempted to create a duplicate record for the child (Homepage, Add Child button) instead of just coming up with an error (example shown below, debugging section highlighted also shown), I'd like a message box to appear with two options - 1) Effectively cancel so they can correct the child ID, 2) take the user to the child's record with the ID they entered. this is because I believe there are two reasons a record may be entered twice - 1) mistyping when entering the ID 2) they did not check if the child was already in the database before attempting to add them, this would allow them to check the children are the same and there was not an error previously.
    Click image for larger version. 

Name:	DuplicateChildID.PNG 
Views:	103 
Size:	21.7 KB 
ID:	26654
    Click image for larger version. 

Name:	DuplicateChildIDDebug.PNG 
Views:	102 
Size:	10.5 KB 
ID:	26655

    If you have any solutions to any of my problems, I'm all ears

    I have attached the database for your use, if you have any questions, I'm happy to assist in any way I can.
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,381
    Changes AddChild form
    Don't need to save record. Already saved and code tries to save again causing duplicate error.
    Spelling of LiberiID (no space).

    Code:
    Private Sub AddChildsRecord_Click()
    If IsNull(LiberiID) Then
    MsgBox "Please enter information into the fields", , "Missing Information"
    Else
    '''''DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenForm "Homepage",,,"SearchLiberiID = " & LiberiID
    ''''Forms!Homepage.SearchLiberiID = Forms!AddChild.[LiberiID]
    DoCmd.OpenForm "ChildRecord"
    DoCmd.Close acForm, "AddChild"
    DoCmd.Close acForm, "Homepage"
    End If
    End Sub
    Last edited by davegri; 12-06-2016 at 05:33 PM. Reason: more bugs

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,381
    form ChildRecord, search criteria to find opening record wrong.
    This is also wrong in several other places.

    Code:
    Private Sub ViewAssessments_Click()
    DoCmd.OpenForm "AssessmentRecord", , , "LiberiID = " & Me.LiberiID
     '''Forms!AssessmentRecord.LiberiID = Me.LiberiID  'You can't set an autonumber primary key to ANYTHING. It's an autonumber!
     DoCmd.Close acForm, "ChildRecord"
    End Sub
    What is the purpose of LiberiID? It only exists in the tblChild records. It isn't a foreign key anywhere.
    Last edited by davegri; 12-07-2016 at 01:19 AM. Reason: question

  4. #4
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Quote Originally Posted by davegri View Post
    form ChildRecord, search criteria to find opening record wrong.
    This is also wrong in several other places.

    Code:
    Private Sub ViewAssessments_Click()
    DoCmd.OpenForm "AssessmentRecord", , , "LiberiID = " & Me.LiberiID
     '''Forms!AssessmentRecord.LiberiID = Me.LiberiID  'You can't set an autonumber primary key to ANYTHING. It's an autonumber!
      DoCmd.Close acForm, "ChildRecord"
     End Sub
    What is the purpose of LiberiID? It only exists in the tblChild records. It isn't a foreign key anywhere.
    Liberi ID is the ID for the child which is used on an external database. This was initially the primary key, however I then realised the user could potentially enter the wrong ID, which would cause problems everywhere if they needed to go back and change it. I can select the LiberiID - that's just a number, the ChildID is the autonumber here.

    I did have spaces in all of my variables, but I was later advised to change it on a thread on here - I must have forgotten to change it in this instance. If I change that reference to "LiberiID", it does run and inserts the data as a new record - unless the LiberiID already exists, where it just takes you to that child's record - so as far as I can see, that code is required (so the process described above can begin and prompt the user with a message box about duplicating the record).

    My method does work though, so although it may not be the most optimal way of doing it, I'd rather not go back and change everything - possibly creating more errors in the process - if what I have works.

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I have made some changes, see if this fixes your issue.
    Attached Files Attached Files

  6. #6
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I've noticed a few problems/errors, I'll list them here for future reference.

    I'm going to change the child and assessor combo box back to a text box - there are currently almost 300 children who would go into the database, while assessor's are at a similar number - so a dropdown wouldn't work. I could potentially add the District/Team/Assessor Dropdowns however (as shown on the AssessmentRecord form previously), if you feel that would be easier for the user?

    Also, I'm not sure how you would add a new child on that database? I can't see a route to anywhere with that as an option.

    The AssessorRecords form no longer populates anything based on what was entered in the Homepage - I'm guessing this was just a mistake when you changed the text box to a combo box and I should be able to fix. (I have just thought of a problem with what I had originally however, see * below).

    On the AssessmentRecords, is it possible to automatically populate the Liberi ID, forename and Surname fields? Since we know this already from the box on the homepage, it makes sense to me that it would automatically pull this through, rather than once the assessment has been selected. Also, a little side-thought process - could we put which number assessment it is for that child on the dropdown instead of the assessment ID? Alternatively, I could just hide the assessment ID, the count probably isn't that important.

    I do like what you've done with the behaviours form, I assume the Medium risk section being blank and no High Risk section was just to show me how it would work and allow me to do the rest? (you may need to talk me through that however - I haven't got a clue). Also a fan of your delete row buttons by the way.

    *I realised that as it is, if the assessor changes teams (happens frequently) the assessment would now show as having occurred under the new team, when in fact it should be the old team. Is there a way to take the team and district selected on the assessment record, and store this in the assessment details table? I think it should be quite easy to do, but since they are currently required to be unbound (as far as I'm aware) I'm unsure how it would work.

  7. #7
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, I've found where you add the child - you have to select a child then click the new button - would we not be able to just have a "new" button on the homepage, as selecting a child you definitely don't need seems a little pointless to me.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Please change whatever you want, these were purely suggestions and maybe showing you a few things you weren't aware of. All the things you mention above you have done before. I was a bit confused with the district and team as they didn't seem to be stored anywhere, they will need to be on the assessment table to accomplish the above.

    Remove the check on the homepage for entering a child, then they can just go to the form and add a new record.

    Using the "page" control: it is no different to what you have done before. In design view you will add the medium risk subform to the second page. Right-click on the tab to insert a new page.

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You might also want to think about changing the Child table, you have two fields with no duplicates, the ChildID should be removed as it is unnecessary and causing extra maintenance.

  10. #10
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Ok, this is what I have so far. For some reason, the Medium and High risk subforms are pulling data through again. Which I don't get as I just copied the LowRiskBehaviours form (and changed the RiskID) to create them.

    Also, I'm trying to create an assessment for one of the two children you created, but it doesn't seem to like it. I've probably just missed something, have you any idea?
    Attached Files Attached Files

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Where's the "New" button gone?

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh - at the bottom! When you go to the behaviours screen there are no key fields, all the fields at the top are blank so you can't add records.

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your medium and high risk subforms are linked to the main form so will show all records.

    Your high risk behaviour combobox is for risk id 1.

  14. #14
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I'm not sure what you mean? (about how they are linked to the main form)
    Last edited by Heathey94; 12-08-2016 at 12:54 PM. Reason: Clarification

  15. #15
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Find a tutorial on how to use subforms, that may help you.

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

Similar Threads

  1. Replies: 13
    Last Post: 04-21-2016, 03:33 AM
  2. Macro to Open Form Triggers Exclusive Access Message
    By snakatsu in forum Database Design
    Replies: 5
    Last Post: 11-10-2015, 10:46 PM
  3. Replies: 6
    Last Post: 09-30-2015, 03:14 PM
  4. Multiple options based on a tree structure...
    By blue22 in forum Database Design
    Replies: 3
    Last Post: 01-09-2014, 05:58 AM
  5. Replies: 1
    Last Post: 08-01-2011, 04:17 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