Page 3 of 3 FirstFirst 123
Results 31 to 44 of 44
  1. #31
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Great thank you - that's one thing off my list.

    The next problem: when trying to add an assessor (or edit their details) I have the option to select district and team - teams are restricted to one district, with many teams in each district (similar to States/Counties in Countries). I want to limit the teams available to only their district and I have found one method of doing this - https://msdn.microsoft.com/en-us/lib.../ff196029.aspx, however if a district is accidentally selected/needs to be changed due to the assessor changing teams I have a problem since the old teams still apply (see images below)

    Click image for larger version. 

Name:	Assessor District1.PNG 
Views:	34 
Size:	7.7 KB 
ID:	23373Click image for larger version. 

Name:	Assessor District2.PNG 
Views:	34 
Size:	7.7 KB 
ID:	23374

    The SQL code I use for the 'team' row source is: SELECT distinct TeamDetails.Team, TeamDetails.TeamID FROM TeamDetails WHERE (((TeamDetails.District)=[forms]![Assessor Records Form]![District])) UNION select distinct null, null FROM TeamDetails ORDER BY TeamDetails.Team;

    while the row source for 'District' is a query which pulls in the district's from the district table.
    with the districts listed in the DistrictDetails table, while the Teams and districts they fall under are listed in the TeamDetails table.



    Have you got a way of getting around my problem? I don't mind trying a different method or just fixing this one, whichever you find easiest. Again, if you need any more information, just let me know.

  2. #32
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That method would require you to requery the team combo in the after update event of the district combo. Are you doing that? More here:

    Baldy-Cascading Combos

    I'll need to know the steps to test that medium query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #33
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    No, I thought that was probably the case but was unsure how to go about it, I'll have a look tomorrow.

    Do you want the database again or just the steps?

  4. #34
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Assessment Database.zipNo, I thought that was probably the case but was unsure how to go about it, I'll have a look tomorrow.

    I've attached the database again - I've made quite a few more pages, You can open the Low Risk Assessment Form to record 27 and then click Next Page - that should open the Medium Risk Assessment Form.

    Alternatively, going about it the long way, opening the homepage, searching 2 in the search for child field, view assessments, then next page twice.

  5. #35
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Offhand, the medium query has joins in it that are causing it to return zero records. Specifically, there is no assessor ID in that record.

    I won't pretend to understand your tables and such, but they don't appear to be normalized.

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #36
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I totally agree with Paul. your tables are not normalized. Several things jump out at me.

    You are using spaces in your object names. Shouldn't use spaces punctuation or special characters (exception is the underscore) in object names.

    You have 3 one-to-one relationships. It is rare to see a one-to-one relationships, let alone 3 in the same dB.
    The relationship between the table "Assessor Details" and "TeamDetails" is on a text field named "Team" in each table. Table "DistrictDetails" does not seem to be needed.

    Tables "Assessment Low Risk", "Assessment Medium Risk" and "Assessment High Risk" are designed like a spreadsheet. The field names are actually behaviors (data). What would happen if you had to add a new low risk assessment? You would have to modify the table. Then you would have to modify queries, forms and reports. Last you would have to modify VBA code. Basically a re-write of the dB.

    You might consider tables:
    tblBehaviors
    --------------
    Behavior_PK (Autonumber)
    RiskLevel_FK (Long) (FK to tblRisks)
    BehaviorDesc (Text)

    ChildDetails
    --------------------
    LiberiID_PK (Autonumber)
    Forename
    Surname
    DateOfBirth
    Address
    Town
    Area
    County
    PostCode


    AssessorDetails
    ----------------
    AssessmentID_PK (Autonumber)
    LiberiID_FK (Long) (FK to table ChildDetails)
    AssessorID_FK (Long) (FK to table AssessorDetails)
    BehaviorID_FK (Long) (FK to table tblBehaviors)
    AssessmentComments (Text)

    tblRisks
    ----------------------
    RiskID_PK (Autonumber)
    RiskDesc) (Text) (ex.: Low, Med, High)



    Edit: Forgot to add these links:
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

  7. #37
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Steve,

    I have now changed the database to your thinking (thank you for the helping with the organising of the tables, it really helped)... after much puzzling over how it would work I now agree with you - just. I have attached my new database so all further discussions make sense.

    I hadn't used Access for quite some time prior to this project, which is why I had forgotten to exclude spaces and thought it would do more harm than good to change it - but since I have now restructured it I have redone this (other than the forms, which I will get to as the queries that make them work are complete).

    I have removed my previous queries and recreated a further 2... which is as far as I got before I became unsure if the fields in my tables were correct (specifically the Assessor Table). If you could get back to me when possible on whether this is indeed correct, I would be most grateful.
    Attached Files Attached Files

  8. #38
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I have now progressed to beginning to modify my forms, however I have encountered a problem.

    I wish the forms to be displayed as they were (with each low risk behaviour below the other, however currently I can only get the option for one (and I'm not sure I'm going about it correctly)

    I have attached my updated database so you can understand what I am talking about - I want my new form (LowRiskBehaviour) to look like my old form (Low Risk Assessment Form).

    Note: I do still have the problem with the query mentioned earlier
    Attached Files Attached Files
    Last edited by Heathey94; 01-14-2016 at 03:21 AM.

  9. #39
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK... I've now had chance to properly read through Paul's link and believe the relationships in my current database are fully normalized (other than 1 which I come too later). I have renamed all of my old forms so they begin with an 'o' so they are grouped and I can make sure I have added them correctly to the new version (the exception is Add High Risk Assessment, for some reason it won't let me change it).

    With regards to Steve's comment about adding (and so editing) risks, I am unsure how the user should search for the question they want to add. I have temporarily added a "Question Number" column which is a number which resets once the risk level changes (so the user does not have to count every question in order to find the one which needs editing, just those under the same risk heading as theirs) as this is the only idea I have (other than searching for the entire question, which is just impractical). The forms will be completed and uploaded by assessors, and the user will then update the database. There will only be one user (barring a long term absence/change of duties) and so it will be simple to go through simple procedures such as this with them. I know this column goes against normalisation rules, which is why it is only temporary while I wait for a better idea from you. I have attached a screenshot of part of the form so you have an example to work from.

    On a similar note, the EditBehaviour form (which uses the QuestionNumber) I have 2 queries:
    1 - I would like the RiskLevel to display instead of the RiskID in the dropdown box
    2 - I would like the BehaviourDescription to update when the "Find Risk" button is clicked

    Also, it automatically opens this form in datasheet view, even though I have selected No in the "Allow Datasheet View" (and all others except form view) option, any ideas?
    Attached Thumbnails Attached Thumbnails Assessment Form Word.PNG  
    Attached Files Attached Files
    Last edited by Heathey94; 01-14-2016 at 11:08 AM. Reason: Made changes to the Database since posting

  10. #40
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, it automatically opens this form in datasheet view, even though I have selected No in the "Allow Datasheet View" (and all others except form view) option, any ideas?
    Change the "Default View" property to "Single Form" or "Continuous Forms" view.

    1 - I would like the RiskLevel to display instead of the RiskID in the dropdown box
    Set the :
    "Row Source" = "SELECT TblRisks.RiskID, TblRisks.RiskLevel FROM TblRisks ORDER BY TblRisks.RiskID; "
    "Column widths" to 0
    "Number of columns" = 2

    You want the form to look like a spreadsheet. I would use a Main Form/Sub form arraignment - the subform is where the behaviors would be selected.
    If you must have the form like the picture in Post #39, you will have to use an unbound form/unbound controls and write code to add records to the table. And write code to fill the unbound controls when viewing/reviewing records.

  11. #41
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Also, it automatically opens this form in datasheet view, even though I have selected No in the "Allow Datasheet View" (and all others except form view) option, any ideas?
    Change the "Default View" property to "Single Form" or "Continuous Forms" view.
    Thanks, there are so many options I guess I just didn't see it.

    1 - I would like the RiskLevel to display instead of the RiskID in the dropdown box
    Set the :
    "Row Source" = "SELECT TblRisks.RiskID, TblRisks.RiskLevel FROM TblRisks ORDER BY TblRisks.RiskID; "
    "Column widths" to 0
    "Number of columns" = 2
    Great, thank you - that solution worked brilliantly. I do still have the problem with the behaviour description not updating however.
    I also used this solution on the districts and teams, however it then found an error with the requery in the district "AfterUpdate", the latest version of the database is attached.

    You want the form to look like a spreadsheet. I would use a Main Form/Sub form arrangement - the subform is where the behaviours would be selected.
    I am not keen on the subform idea, but am going to give it a go. What is your suggestion to make the risks as easy to fill out as possible - I don't want the user to have to type each risk every time as

    1 - this is open for misspelling, and
    2 - I've shortened the title of some of the risks as they were very specific while, in the database, they did not need to be.

    Perhaps a question number dropdown which populates the description for the user (as suggested in my previous post with regards to editing questions)? If this is your suggestion, I also need to know how to update the description (this may use the same method as required for the edit behaviour form) - I may also need to know this if this is not your suggestion.

    Also is there a way to alter the width of the columns? everything I've done so far reverts back to normal after closing.

    I know that's a lot of questions to take in so break it down etc if you wish.
    Attached Files Attached Files
    Last edited by Heathey94; 01-20-2016 at 04:46 AM.

  12. #42
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I have attached the most recent version of my database for your use.
    Attached Files Attached Files

  13. #43
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What form are we talking about?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #44
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    The Low/Medium/High Risk forms (and the same with the 'add' versions) are the forms I would like displayed similar to the image above, in post 39, if possible.

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

Similar Threads

  1. Autonumber field not populating
    By Pete-RM in forum Forms
    Replies: 5
    Last Post: 09-24-2015, 08:05 AM
  2. Replies: 5
    Last Post: 10-20-2014, 10:32 AM
  3. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  4. Autonumber in multitable form
    By Fairlane in forum Forms
    Replies: 5
    Last Post: 08-29-2013, 06:15 PM
  5. Autonumber through a form, not a table
    By Kat-ness in forum Forms
    Replies: 2
    Last Post: 05-02-2013, 04:18 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