Page 3 of 16 FirstFirst 12345678910111213 ... LastLast
Results 31 to 45 of 233
  1. #31
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    SearchAssessor homepage:


    I was going to get it to work, and then think about sorting it.
    I had it as one name since they were originally going to search for the name, which I didn't think would work as separate fields.
    Oh, that must be what it is, I'll look at that tomorrow - had other priorities today.

    Adding a new assessor:
    as they won't be selecting anything from the Comboboxes (each team is in a set district) it makes sense for it to just be a text box - right? Is it impossible to make that work?

    Selecting an assessment:
    - there is currently no assessment number entered
    Yes, I know - I want it to automatically calculate which assessment number it is, based on how many assessments for that Child have a date of assessment before the current one - and then display that in a combo box so the user can select it based on this.

  2. #32
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the query for the select assessor combobox you can have a field like: AssessorName: Surname & ", " & FirstName - you can sort on that. Whenever you want to display their name you can concatenate the two fields however you want.

    The team will be a combobox with the district and the org ID's included in the row source. Once they select the team (AfterUpdate) populate those two fields from the combobox. They will both be Enabled=False and will be bound fields.

    You need a combobox to select assessments.
    For the ass-nbr, add this code to your add new button:

    Code:
        Dim NewNbr As Long
        On Error Resume Next
        NewNbr = DLookup("AssessmentNumber", "TblAssessment", "ChildID=" & Me!HoldChildID)
        If NewNbr > 1 Then
            Me!AssessmentNumber = NewNbr + 1
        Else
            Me!AssessmentNumber = 1
        End If
            'add the new number to the combobox
        Me!SelAssessment.Requery

  3. #33
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, so this is where I'm at:

    Team, District and Organisation are required in the Assessor table as it will enable the user to differentiate between two assessors with the same name.
    They are also required on the Assessment table as the Team at the time of the assessment is what will be used when reporting on the data.
    As mentioned previously, assessors may change teams, which is why we need it as separate fields on both tables.

    I can't get the District and Organisation to automatically fill out on the AssessorRecord form after Team has been entered (When I know how to do this, there are a few other areas it will need to be applied, but I should be able to work this out for myself when told how to do it)

    Can I get the AssessmentRecord form to automatically show the most recent (Highest AssessmentNumber) for the child selected on the ChildRecord form to be displayed, instead of automatically attempting to add a new record? Once this is done and can tell if the SelectAssessment Combo box is working.

    Also, I think I need a button to undo all changes made on the screen, so that if the user realises they have begun to add a child/assessor etc they shouldn't have (e.g. already in the database), they can press this button to wipe the form clean.
    Attached Files Attached Files

  4. #34
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Always call the name of the text box the same as the control source - there are loads of questions on this forum about why they can't find the name (saw Team on assessor form).

    Team - district and org are both numeric fields, you need to bring these into the row source for the team (ID). Then, in the AfterUpdate, Me.District=Me.Team.Column(x) - at the moment district is column (2) - starts from 0.

    When opening the AssessmentRecord form, use this: DoCmd.OpenForm "AssessmentRecord", , , "AssessmentNumber=" & DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!ChildID) & " AND ChildID=" & Me!ChildID

    In the record source for this form the fields are repeated twice, that causes naming problems on the form and is unnecessary.

    User can hit the 'esc' key to undo their changes. Also, "Me.Undo" will also do it.

  5. #35
    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
    Team - district and org are both numeric fields, you need to bring these into the row source for the team (ID). Then, in the AfterUpdate, Me.District=Me.Team.Column(x) - at the moment district is column (2) - starts from 0.
    I thought it would be something like that!

    Quote Originally Posted by aytee111 View Post
    When opening the AssessmentRecord form, use this: DoCmd.OpenForm "AssessmentRecord", , , "AssessmentNumber=" & DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!ChildID) & " AND ChildID=" & Me!ChildID.
    Ok, I'll give that a go.

    Quote Originally Posted by aytee111 View Post
    In the record source for this form the fields are repeated twice, that causes naming problems on the form and is unnecessary.
    I guess I could create duplicate tables - one for Assessor, one for Allocated?


    Quote Originally Posted by aytee111 View Post
    User can hit the 'esc' key to undo their changes. Also, "Me.Undo" will also do it.
    Yes, I did know that... sorry, not quite with it today..

  6. #36
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is allocated? NO duplicate tables are a very bad idea!

  7. #37
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Allocated is the Team/District at the time of the assessment (won't change if the assessor changes team/district) - sorry, I thought that was clear from my form, although if you didn't look at the form then it would be of course it would be of no help.

    By duplicated tables, I mean 1 would hold the Allocated Team, one the Assessor's Team and most of the rows would be the same, but they would be used for different things. It's the only way I can think of getting around your comment regarding the record source repeated twice.

  8. #38
    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
    When opening the AssessmentRecord form, use this: DoCmd.OpenForm "AssessmentRecord", , , "AssessmentNumber=" & DLookup("Max(AssessmentNumber)", "TblAssessment", "ChildID=" & Me!ChildID) & " AND ChildID=" & Me!ChildID
    The following error appears when I try to view the form in form mode:
    "Run time error '3079':
    The specified field 'AssessmentNumber' could refer to more than one table listed in the FROM clause of your SQL statement" and your code (quoted above) is highlighted as the problem

    But I've checked, and AssessmentNumber is only in the TblAssessment - any ideas?
    Attached Files Attached Files

  9. #39
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    As mentioned previously, everything is duplicated in your record source - this is going to cause you problems!

  10. #40
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    What's duplicated? I can't see anything there twice.

  11. #41
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I thought that was clear from my form, although if you didn't look at the form then it would be of course it would be of no help.
    Now now! Which form are you referring to?

    By duplicated tables, I mean 1 would hold the Allocated Team, one the Assessor's Team and most of the rows would be the same, but they would be used for different things. It's the only way I can think of getting around your comment regarding the record source repeated twice.
    Team and District are on TblAssessment - should be ID's by the way, not text fields, text can change but ID's don't. I have the (mis)understanding that these two belong to the assessment when it is entered so not sure where they are allocated to, unless by allocated you mean that they are stored with the assessment?

  12. #42
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The specified field 'AssessmentNumber' could refer to more than one table
    More than one means twice.

  13. #43
    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
    Now now! Which form are you referring to?
    The AssessmentRecord form, it says "Allocated Team" there.

    Quote Originally Posted by aytee111 View Post
    Team and District are on TblAssessment - should be ID's by the way, not text fields, text can change but ID's don't. I have the (mis)understanding that these two belong to the assessment when it is entered so not sure where they are allocated to, unless by allocated you mean that they are stored with the assessment?
    OK, I'll see if I can get this across right (it's my fault, I'm not that great at explaining myself:
    The assessor is part of a team, which is part of a district within an organisation.
    I need this data to be recorded in the assessor's table, as it will ensure the user is able to select the correct assessor later on.

    All of the children will have an allocated social worker, however that does not need to be recorded in this database.
    This social worker is part of a team, which is part of a district within the COUNCILs organisation.

    The assessment could be completed by anyone - a Police Officer, a Teacher, a Nurse etc. but in the majority of cases, it will be the Social Worker. Hence why the assessor is mainly, but not always, a social worker.
    We will report on the Team and District the allocated social worker is part of, at the time of the assessment, hence the need to store this seperately.
    e.g. District 5 had 10 CSE assessments completed in March etc.

    Hopefully that's clearer?

    Team, District and Organisation all have the ID with "visible - No" at the side of the form, but with the text displayed (and nothing happens with them, these text boxes are unbound) - the ID will mean nothing to the user, so I felt it would be pointless to include it.

    Quote Originally Posted by aytee111 View Post
    More than one means twice.
    Yes, I know but I've checked the relationships to view all the tables and AssessmentNumber is only there once, I've looked on the query in question and can't see it there twice either.

  14. #44
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Twice error: don't just look at the SQL, test the query (click the three dots and run it). That way you will see all the fields that are duplicated.

    Yes you're right, my screen is too small and I did not scroll down all the way!

    Still pondering on the other.

  15. #45
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Team, District and Organisation all have the ID with "visible - No" at the side of the form, but with the text displayed (and nothing happens with them, these text boxes are unbound) - the ID will mean nothing to the user, so I felt it would be pointless to include it.
    This may be what has been causing issues with all the comboboxes I have given you. You set the column widths, so the ID number, which is the bound column (e.g. TeamID), is the first field in the row source query. Then you set the first column width as zero so all the user sees is the team name. This applies to all - so assessor name, child name, etc, all will be the first column displayed with the control source set to the ID.

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